How to modify all table fields (record to record)

Hello everyone

I am writing a script that is supposed to copy Item data from one company to another.

I have a problem with Items that already exist - I want to modify them but I don't want to write all fields in the table. I decided to create loops and use RecordRef and FieldRef. Unfortunately, this approach takes a long time (over 24 hours).
sourceTable.OPEN(objectId, FALSE, fromCompany);
sourceFieldFilter := sourceTable.FIELD(54);
sourceFieldFilter.SETFILTER('FALSE');
IF sourceTable.FINDSET THEN BEGIN
  targetTable.OPEN(objectId, FALSE, toCompany);
  REPEAT
    targetTable.INIT;
    FOR intCounter := 1 TO sourceTable.FIELDCOUNT() DO BEGIN
      sourceField := sourceTable.FIELDINDEX(intCounter);
      targetField := targetTable.FIELD(sourceField.NUMBER);
      targetField.VALUE(sourceField.VALUE);
      IF FORMAT(sourceField.CLASS) <> 'FlowField' THEN BEGIN
        targetTable.MODIFY(FALSE);
        COMMIT;
      END;
    END;
  UNTIL sourceTable.NEXT = 0;
END;
targetTable.CLOSE();
sourceTable.CLOSE();


I am looking for another solution. I wrote something like that (but it doesn't work):
fromItem.INIT;
fromItem.CHANGECOMPANY(companyName);
fromItem.SETFILTER(fromItem.Blocked, 'FALSE');
IF fromItem.FINDSET THEN BEGIN
  REPEAT
    IF toItem.GET(fromItem."No.") THEN BEGIN
      toItem := fromItem
      toItem.MODIFY;
    END;
    COMMIT;
  UNTIL fromItem.NEXT = 0;
END;



There are over 200 fields in my table and I don't want to write each field in the style:
toItem.Fields1: = fromItem.Fields1;
toItem.Fields2: = fromItem.Fields2;
toItem.Fields3: = fromItem.Fields3;
....
....
toItem.Fields200: = fromItem.Fields200;
toItem.MODIFY;

it will work but I am looking for another solution.

Do you have any suggestions?

Best Answers

  • AlexDenAlexDen Member Posts: 72
    Accepted Answer
    Then try to use
    toItem.TRANSFERFIELDS(fromItem, FALSE);
    
    instead of
    toItem := fromItem;
    
  • Wisa123Wisa123 Member Posts: 291
    Accepted Answer
    As @AlexDen said, you should absolutely use the transferfields function for this kind of task.
    If performance is still an issue (which it should'nt be with the transferfields approach) try committing the changes every 1000 records (assuming you sync that many).

    /Wisa
    Austrian NAV/BC Dev

Answers

  • AlexDenAlexDen Member Posts: 72
    Hi,

    Your "another" solution looks correct and should work.
    Do you execute this code in the correct company?
    Maybe, you have to add:
    toItem.ChangeCompany(toCompany);
    
  • kamilolszewski_kamilolszewski_ Member Posts: 3
    AlexDen wrote: »
    Hi,

    Your "another" solution looks correct and should work.
    Do you execute this code in the correct company?
    Maybe, you have to add:
    toItem.ChangeCompany(toCompany);
    

    Thanks for the tip.
    I am sure he execute in the correct company. System has a problem with MODIFY function, if the record already exists - as if it would also like to modify the key (??).
    I get the following message:
    rhnglwnkgfb6.png

  • AlexDenAlexDen Member Posts: 72
    Accepted Answer
    Then try to use
    toItem.TRANSFERFIELDS(fromItem, FALSE);
    
    instead of
    toItem := fromItem;
    
  • Wisa123Wisa123 Member Posts: 291
    Accepted Answer
    As @AlexDen said, you should absolutely use the transferfields function for this kind of task.
    If performance is still an issue (which it should'nt be with the transferfields approach) try committing the changes every 1000 records (assuming you sync that many).

    /Wisa
    Austrian NAV/BC Dev
  • kamilolszewski_kamilolszewski_ Member Posts: 3
    AlexDen wrote: »
    Then try to use
    toItem.TRANSFERFIELDS(fromItem, FALSE);
    
    instead of
    toItem := fromItem;
    

    Thanks, it works :)
Sign In or Register to comment.