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
0
Answers
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.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
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
Server - 3.70.B
Client - 3.60
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.
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;
Server - 3.70.B
Client - 3.60
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
Microsoft Dynamics NAV Developer since 1997
MSDynamics.de - German Microsoft Dynamics Community - member of [clip]
at the start of the program:
and for updating the dialog box or commiting:
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
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
Server - 3.70.B
Client - 3.60
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
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.
Server - 3.70.B
Client - 3.60
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
Server - 3.70.B
Client - 3.60
Many thanks for your help.
Simon
Server - 3.70.B
Client - 3.60
http://www.mibuso.com/forum/viewtopic.php?p=43101#43101
Server - 3.70.B
Client - 3.60