Options

Filtering and Updating the same Field Issue.

vivek4121vivek4121 Member Posts: 165
Hi All,

I need to update the Salesperson code in the Contact table, I have used the below code :
Contact.RESET;
Contact.SETRANGE("Salesperson Code",'3803');
Contact.SETCURRENTKEY("Salesperson Code");
IF Contact.FINDSET(TRUE,TRUE) THEN BEGIN 
  REPEAT
    Contact.VALIDATE("Salesperson Code",'5189');
    Contact.MODIFY;
  UNTIL Contact.NEXT = 0;
END;

There are 4 records within the filter, which i need to modify. But my concern is it only updates only the first record.
And When I Removed the "Contact.SETCURRENTKEY("Salesperson Code");" Line of code, then it updates all the records.

I did't understand the logic behind that.
Can anyone help?

Thanks in advance.

Comments

  • Options
    lvanvugtlvanvugt Member Posts: 774
    edited 2014-06-23
    The issue that you are updating facing is that you are modifying the field you also are sorting on using the same variable. This way SQL Server qoes astray. You should use a second variable based on the same table, e.g. Contact2 and write your code as following:
    Contact.RESET;
    Contact.SETRANGE("Salesperson Code",'3803');
    Contact.SETCURRENTKEY("Salesperson Code");
    IF Contact.FINDSET(TRUE,TRUE) THEN BEGIN 
      REPEAT
        Contact2 := Contact;
        Contact2.VALIDATE("Salesperson Code",'5189');
        Contact2.MODIFY;
      UNTIL Contact.NEXT = 0;
    END;
    
    Luc van Vugt, fluxxus.nl
    Never stop learning
    Van Vugt's dynamiXs
    Dutch Dynamics Community
  • Options
    krikikriki Member, Moderator Posts: 9,090
    If you looping a table and you change on the fly that can , best solution is to put the records in a temptable and then read the temptable and change the records in your real table.

    But in this case, it is even easier:
    Contact.RESET;
    Contact.SETCURRENTKEY("Salesperson Code");
    Contact.SETRANGE("Salesperson Code",'3803');
    Contact.MODIFYALL("Salesperson Code",'5189');
    
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    MBergerMBerger Member Posts: 413
    kriki wrote:
    If you looping a table and you change on the fly that can , best solution is to put the records in a temptable and then read the temptable and change the records in your real table.

    But in this case, it is even easier:
    Contact.RESET;
    Contact.SETCURRENTKEY("Salesperson Code");
    Contact.SETRANGE("Salesperson Code",'3803');
    Contact.MODIFYALL("Salesperson Code",'5189');
    
    Don't forget to add a TRUE to the end of the MODIFYALL, though.
  • Options
    krikikriki Member, Moderator Posts: 9,090
    Depends if you need to trigger the code or not.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    KishormKishorm Member Posts: 921
    Also don't forget that the RunTrigger parameter of MODIFYALL relates to calling the OnModify trigger of the record and not the OnValidate trigger of the field.
  • Options
    vivek4121vivek4121 Member Posts: 165
    Thanks for the Replies.

    Can you guys please elaborate this term a bit more "SQL Server goes astray"?
  • Options
    lvanvugtlvanvugt Member Posts: 774
    vivek4121 wrote:
    Can you guys please elaborate this term a bit more "SQL Server goes astray"?
    As you are changing a field that's part of the active key (index) and the filter you applied to it the original context of the record is lost so SQL Server isn't able to continue with the next logical record in relation to this index.
    Hope this makes sense.
    Luc van Vugt, fluxxus.nl
    Never stop learning
    Van Vugt's dynamiXs
    Dutch Dynamics Community
  • Options
    vivek4121vivek4121 Member Posts: 165
    =D> Thanks a lot.
Sign In or Register to comment.