Changing Field Values - Field in Primary Key

SHardy

Hi,

I have the need to create a function that works through a list of tables/fields and change those field values where necessary based upon a conversion table.

I have come across a bit of a problem where the field that needs to be changed is in the primary key. I cannot use rename, as suggested, as I am working with RecordRefs, not Records.

I came across the SETPOSITION function which seems to indicate that this should change the values in the key fields.

I have put together some code that compiles OK and does not return any errors, but it is not retaining the new values.

Could someone please advise on how this function should be used?

Many Thanks,
Simon


strTemp := tblTableRef.GETPOSITION;
MESSAGE(strTemp);
strNo := STRPOS(strTemp,OldID);
strTemp := DELSTR(strTemp,strNo,STRLEN(OldID));
strTemp := INSSTR(strTemp,NewID,strNo);
MESSAGE(strTemp);
tblTableRef.SETPOSITION(strTemp);
COMMIT;
ERROR('OK!');

Note: I have set messages/ error to let it run once and show me what it is doing. These will be removed.

kriki

GETPOSITION and SETPOSITION is not usefull for you, it just gives you a string with the primary keyfields or uses that string to fill up the primary keyfields. But for renaming it is not usefull.
Better is to try MODIFY anyway. If it gives problems, you can try to DELETE the old record and INSERT the new version (RENAME = DELETE OLD + INSERT NEW).
Problem is that with DELETE-INSERT the records that point to this record will not be changed (e.g. if you RENAME a Payment Terms-code, everywhere you use the old code, it will be replaced with the new code).
Another thing you can put in your code is some code for that table to convert the recordreference to the record-variable of that table and do a rename of the record.

SHardy

Aah! I think this is another case of a confusing and misleading help topic.

So, is the SETPOSITION used with the FIND() function to "go to" a record rather than using filters to find it? The help topic appears to suggest that SETPOSITION sets the values of the key fields.

OK, a little bit of background. We are migrating from NT domain to Active Directory. As part of this ALL user names are also changing to follow a group wide format. I therefore need to update ALL instances of user IDs in ALL tables.

I have almost finalised the code to do this, but this was my last little stumbling block - changing values of fields within the primary key while using RecordRef.

The MODIFY method does not work. I will try your suggestion of passing the RecordRef variable to a Record variable and then doing a RENAME, if it is possible. Might have difficulty. The reason that I used RecordRefs is because I am working through so many tables and they are all structured differently. This was the only way for me to proceed.

Would it be useful if I posted the full code?

Hopefully the issue you raised will not be a problem, as I am changing these codes everywhere.

I will update topic once I have tried to implement this.

Many Thanks,
Simon

kine

If all fields which are filled with user ID have correct table relation, you only need to rename the user ID in table user and all related fields will be corrected. It means, set the table relation on all fields and rename the users without using recref because you do not need it...

SHardy

OK, I don't think I can pass the RecordRef to a Record variable and keep the code dynamic.

What I have done is an INSERT (of the amended record) followed by a DELETE (of the original record). This seems to work OK, but is this likely to have any detrimental effects?

Any comments would be greatly appreciated.

FYI, full code:


OnRun()
tblTableName := ' ';
tblFieldName := ' ';
tblRcdCount :=0;
Window.OPEN(Text001,tblTableName,tblFieldName,tblRcdCount);
rTableList.INIT;
IF rTableList.FIND('-') THEN REPEAT
tblTableName := rTableList.TableName;
tblFieldName := rTableList.FieldName;
tblRenameKey := FALSE;
CLEAR(tblRcdCount);
Window.UPDATE;
rFieldList.RESET;
rFieldList.SETRANGE(TableName,tblTableName);
rFieldList.SETRANGE(FieldName,tblFieldName);
IF rFieldList.FIND('-') THEN;
tblTableID := rFieldList.TableNo;
tblFieldID := rFieldList."No.";
tblTableRef.OPEN(tblTableID,FALSE);
tblKeyFields := tblTableRef.CURRENTKEY;
IF STRPOS(tblKeyFields,tblFieldName) > 0 THEN BEGIN
tblRenameKey := TRUE;
END;
IF tblTableRef.FIND('-') THEN REPEAT
tblRcdCount := tblRcdCount + 1;
IF (tblRcdCount/500) = ROUND(tblRcdCount/500,1) THEN
Window.UPDATE;
tblFieldRef := tblTableRef.FIELD(tblFieldID);
OldID := tblFieldRef.VALUE;
rIDList.INIT;
rIDList.SETCURRENTKEY("OLD User ID");
rIDList.SETRANGE("OLD User ID",OldID);
IF rIDList.FIND('-') THEN REPEAT
NewID := rIDList."New User ID";
IF OldID <> NewID THEN
IF tblRenameKey = FALSE THEN BEGIN
tblFieldRef.VALUE := NewID;
tblTableRef.MODIFY(FALSE);
COMMIT;
END ELSE BEGIN
tblFieldRef.VALUE := NewID;
tblTableRef.INSERT(FALSE);
tblFieldRef.VALUE := OldID;
tblTableRef.DELETE(FALSE);
COMMIT;
END;
UNTIL rIDList.NEXT = 0;
UNTIL tblTableRef.NEXT = 0;
UNTIL rTableList.NEXT = 0;
Window.CLOSE;

kriki

I checked your code and made some changes. I commented all, so you can see what and why I changed it.

OnRun()
tblTableName := ' ';
tblFieldName := ' ';
tblRcdCount :=0;
Window.OPEN(Text001,tblTableName,tblFieldName,tblRcdCount);
rTableList.RESET;  // MUST BE RESET AND NOT INIT
  // INIT initialises ONLY the non-key fields
  // RESET removes all filters on the record and resets the CURRENTKEY to the primary key
  // CLEAR clears ALL fields and does also do a RESET
IF rTableList.FIND('-') THEN 
  REPEAT
// no need of extra variable    tblTableName := rTableList.TableName;
// no need of extra variable    tblFieldName := rTableList.FieldName;
    tblRenameKey := FALSE;
    CLEAR(tblRcdCount);
    Window.UPDATE;
    CLEAR(rFieldList); // CLEAR because if you don't find a record, it retains the last record found
    rFieldList.RESET;  // not really necessary because of "CLEAR(rFieldList);"
    rFieldList.SETRANGE(TableName,rTableList.TableName);
    rFieldList.SETRANGE(FieldName,rTableList.FieldName);
    IF rFieldList.FIND('-') THEN;
    
// no need of extra variable    tblTableID := rFieldList.TableNo;
// no need of extra variable    tblFieldID := rFieldList."No.";
    tblTableRef.OPEN(rFieldList.TableNo,FALSE);
    tblKeyFields := tblTableRef.CURRENTKEY;
    IF STRPOS(tblKeyFields,rTableList.FieldName) > 0 THEN BEGIN
      tblRenameKey := TRUE;
    END;
    
    IF tblTableRef.FIND('-') THEN 
      REPEAT
        tblRcdCount := tblRcdCount + 1;
        IF (tblRcdCount/500) = ROUND(tblRcdCount/500,1) THEN
          Window.UPDATE;
        tblFieldRef := tblTableRef.FIELD(rFieldList."No.");
        OldID := tblFieldRef.VALUE;
        rIDList.RESET;  //RESET and not INIT
        rIDList.SETCURRENTKEY("OLD User ID");
        rIDList.SETRANGE("OLD User ID",OldID);
        IF rIDList.FIND('-') THEN 
          REPEAT
 // no need of extra variable           NewID := rIDList."New User ID";
            IF OldID <> rIDList."New User ID" THEN
// OLD CODE              IF tblRenameKey = FALSE THEN BEGIN
              IF NOT tblRenameKey THEN BEGIN // this is more readable, because you already have a boolean, so why confronting it with a boolean?
                tblFieldRef.VALUE := rIDList."New User ID";
                tblTableRef.MODIFY(FALSE);
// a commit every record generates a lot of overhead and slows down the process                COMMIT;
              END 
              ELSE BEGIN
{NOW HERE YOU HAVE A BIG PROBLEM:this code can go into an endless loop or can skip records, because
  you change the fields of the key you are using, the NEXT-statement uses the current values in the
  record to find the next record, so you have to make a copy of the record and change THE COPY of
  the record and continue searching with the old record (even if it doesn't exist anymore)
                tblFieldRef.VALUE := rIDList."New User ID";
                tblTableRef.INSERT(FALSE);
                tblFieldRef.VALUE := OldID;
                tblTableRef.DELETE(FALSE);
}
                tblTableRef2 := tblTableRef.DUPLICATE;
                tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
                tblTableRef2.DELETE(FALSE);
                tblFieldRef2.VALUE := rIDList."New User ID";
                tblTableRef2.INSERT(FALSE);
// a commit every record generates a lot of overhead and slows down the process               COMMIT;
              END;
            IF (tblRcdCount/500) = ROUND(tblRcdCount/500,1) THEN // new:a commit every 500 records
              COMMIT

          UNTIL rIDList.NEXT = 0;
      UNTIL tblTableRef.NEXT = 0;
  UNTIL rTableList.NEXT = 0;
Window.CLOSE;

Timo_Lässer

kriki wrote:

IF (tblRcdCount/500) = ROUND(tblRcdCount/500,1) THEN // new:a commit every 500 records 
  COMMIT

You can also write

IF tblRcdCount MOD 500 = 0 THEN // new:a commit every 500 records 
  COMMIT;

kriki

Timo Lässer wrote:

kriki wrote:

IF (tblRcdCount/500) = ROUND(tblRcdCount/500,1) THEN // new:a commit every 500 records 
  COMMIT

You can also write

IF tblRcdCount MOD 500 = 0 THEN // new:a commit every 500 records 
  COMMIT;

And now we're busy:

at the start of the program:

timProgress := TIME;

and for updating the dialog box or commiting:

IF timProgress < TIME - 3000 THEN BEGIN // every 3 seconds
  timProgress := TIME;
  COMMIT;
END;

SHardy

Wow! Thanks for the invaluable advice.

As you can most likely tell, I am VERY new to Navision. I haven't even attended any training yet. Does it tell.

I will try your suggestions.

However, regarding the tblTableName & tblFieldName variables...

are these not still required for my WINDOW?

Thanks,
Simon

kriki

SHardy wrote:

However, regarding the tblTableName & tblFieldName variables...

are these not still required for my WINDOW?

You don't need to define values at the opening of the dialogbox, in this case you do

UPDATE(1,"the value you want to show");

Or you can also just use rTableList.TableName rTableList.FieldName in it.

SHardy

OK, below is the final code:

OnRun()
timProgress := TIME;
Window.OPEN(Text001,rTableList.TableName,rTableList.FieldName,tblRcdCount);
rTableList.RESET;
IF rTableList.FIND('-') THEN REPEAT
  tblRenameKey := FALSE;
  CLEAR(tblRcdCount);
  Window.UPDATE;
  CLEAR(rFieldList);
  rFieldList.SETRANGE(TableName,rTableList.TableName);
  rFieldList.SETRANGE(FieldName,rTableList.FieldName);
  IF rFieldList.FIND('-') THEN;
  tblTableRef.OPEN(rFieldList.TableNo,FALSE);
  tblKeyFields := tblTableRef.CURRENTKEY;
  IF STRPOS(tblKeyFields,rTableList.FieldName) > 0 THEN BEGIN
    tblRenameKey := TRUE;
  END;
  IF tblTableRef.FIND('-') THEN REPEAT
    tblFieldRef := tblTableRef.FIELD(rFieldList."No.");
    OldID := tblFieldRef.VALUE;
    rIDList.RESET;
    rIDList.SETCURRENTKEY("OLD User ID");
    rIDList.SETRANGE("OLD User ID",OldID);
    IF rIDList.FIND('-') THEN REPEAT
      IF OldID <> rIDList."New User ID" THEN
        IF NOT tblRenameKey THEN BEGIN
          tblFieldRef.VALUE := rIDList."New User ID";
          tblTableRef.MODIFY(FALSE);
        END ELSE BEGIN
          tblTableRef2 := tblTableRef.DUPLICATE;
          tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
          tblTableRef2.DELETE(FALSE);
          tblFieldRef2.VALUE := rIDList."New User ID";
          tblTableRef2.INSERT(FALSE);
        END;
        IF timProgress < TIME - 3000 THEN BEGIN
          timProgress := TIME;
          Window.UPDATE;
          COMMIT;
        END;
    UNTIL rIDList.NEXT = 0;
  UNTIL tblTableRef.NEXT = 0;
UNTIL rTableList.NEXT = 0;
Window.CLOSE;

I shall now test this. I will update this topic to say if it was successful or if there are any problems.

Once again, thanks ever so much.

SHardy

Ooops! I took out the tblRcdCount. Corrected it and ran against test database.

Have just checked the data and it would appear that it is skipping records. Not on those tables with the fields in primary key, but on the others. I'm a bit confused now.

I am now about to go through the code again to try and uncover the hole, but in the meantime any further help would be hugely appreciated.

Amended code is below.

Many thanks,
Simon

OnRun()
timProgress := TIME;
tblRcdCount := 0;
Window.OPEN(Text001,rTableList.TableName,rTableList.FieldName,tblRcdCount);
rTableList.RESET;
IF rTableList.FIND('-') THEN REPEAT
  tblRenameKey := FALSE;
  CLEAR(tblRcdCount);
  Window.UPDATE;
  CLEAR(rFieldList);
  rFieldList.SETRANGE(TableName,rTableList.TableName);
  rFieldList.SETRANGE(FieldName,rTableList.FieldName);
  IF rFieldList.FIND('-') THEN;
  tblTableRef.OPEN(rFieldList.TableNo,FALSE);
  tblKeyFields := tblTableRef.CURRENTKEY;
  IF STRPOS(tblKeyFields,rTableList.FieldName) > 0 THEN BEGIN
    tblRenameKey := TRUE;
  END;
  IF tblTableRef.FIND('-') THEN REPEAT
    tblRcdCount := tblRcdCount + 1;
    tblFieldRef := tblTableRef.FIELD(rFieldList."No.");
    OldID := tblFieldRef.VALUE;
    rIDList.RESET;
    rIDList.SETCURRENTKEY("OLD User ID");
    rIDList.SETRANGE("OLD User ID",OldID);
    IF rIDList.FIND('-') THEN REPEAT
      IF OldID <> rIDList."New User ID" THEN
        IF NOT tblRenameKey THEN BEGIN
          tblFieldRef.VALUE := rIDList."New User ID";
          tblTableRef.MODIFY(FALSE);
        END ELSE BEGIN
          tblTableRef2 := tblTableRef.DUPLICATE;
          tblFieldRef2 := tblTableRef2.FIELD(rFieldList."No.");
          tblTableRef2.DELETE(FALSE);
          tblFieldRef2.VALUE := rIDList."New User ID";
          tblTableRef2.INSERT(FALSE);
        END;
      IF timProgress < TIME - 3000 THEN BEGIN
        timProgress := TIME;
        Window.UPDATE;
        COMMIT;
      END;
    UNTIL rIDList.NEXT = 0;
  UNTIL tblTableRef.NEXT = 0;
UNTIL rTableList.NEXT = 0;
Window.CLOSE;

SHardy

No! My mistake, this code works perfectly. My table/field list was incomplete. :oops:


Many thanks for your help.

Simon

SHardy

Just in case anyone is interested ( ), my problems with this are not yet over:

http://www.mibuso.com/forum/viewtopic.php?p=43101#43101