[SOLVED] Changing Field Values In Primary Key

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.

Simon - I am not quite sure what you are trying to do here. - But then again - FieldRef / RecRef aren’t my strong side. As mentioned in annother post, RecRef / FieldRef should basicly only be used when it is really needed, as the readability are REALLY REALLY low. So would you like to enlighten me on what the REQUIREMENT is? But when “renaming” a record, you basicly have to delete the old record, and insert the new one, and I don’t see you doing either in your code sample.

Hi, Thanks for your response. I would’ve got back earlier, but was having problems accessing this forum again. I think I was getting confused by the very unhelpful Navision help. The help topic for SETPOSITION read like it would set the values for fields in the primary key of a recordref. Clearly, now, this is not the case. I have now re-written the code to do an INSERT & DELETE. FYI, a bit of background: As part of our imminent migration from NT domain to Active Directory, ALL user names are to change to a groupwide format. As such I have written this code to use two tables (1 - Tables & Field Names, 2- User ID Conversion) to go through all the required tables and update all the User IDs as necessary. The user ID fields are NOT relational to an overall user table. The user setup table only holds current users, not all historic users. This is why this is necessary. Having run this code on a test database, it would appear that it is skipping records. I have pasted my code below. Any help would be greatly appreciated. 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;

Oops! My mistake, this code works perfectly. My table/field list was incomplete. [:I] Many thanks for your help, Simon

Hehe, you are not the first one with problems like that [:D]

If any one is interested ([:D]), my problems with this aren’t over: http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=16665