Changing Field Values - Field in Primary Key

SHardySHardy Member Posts: 38
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.
Navision Version(s):
Server - 3.70.B
Client - 3.60

Answers

  • krikikriki Member, Moderator Posts: 9,116
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • SHardySHardy Member Posts: 38
    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
    Navision Version(s):
    Server - 3.70.B
    Client - 3.60
  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • SHardySHardy Member Posts: 38
    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;
    Navision Version(s):
    Server - 3.70.B
    Client - 3.60
  • krikikriki Member, Moderator Posts: 9,116
    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;
    
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Timo_LässerTimo_Lässer Member Posts: 481
    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;
    
    Timo Lässer
    Microsoft Dynamics NAV Developer since 1997
    MSDynamics.de - German Microsoft Dynamics Community - member of [clip]
  • krikikriki Member, Moderator Posts: 9,116
    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;
    
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • SHardySHardy Member Posts: 38
    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. :D

    I will try your suggestions.

    However, regarding the tblTableName & tblFieldName variables...

    are these not still required for my WINDOW?

    Thanks,
    Simon
    Navision Version(s):
    Server - 3.70.B
    Client - 3.60
  • krikikriki Member, Moderator Posts: 9,116
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • SHardySHardy Member Posts: 38
    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.
    Navision Version(s):
    Server - 3.70.B
    Client - 3.60
  • SHardySHardy Member Posts: 38
    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;
    
    Navision Version(s):
    Server - 3.70.B
    Client - 3.60
  • SHardySHardy Member Posts: 38
    No! My mistake, this code works perfectly. My table/field list was incomplete. :oops:


    Many thanks for your help.

    Simon
    Navision Version(s):
    Server - 3.70.B
    Client - 3.60
  • SHardySHardy Member Posts: 38
    Just in case anyone is interested ( :wink: ), my problems with this are not yet over:

    http://www.mibuso.com/forum/viewtopic.php?p=43101#43101
    Navision Version(s):
    Server - 3.70.B
    Client - 3.60
Sign In or Register to comment.