Filtering and Updating the same Field Issue.

vivek4121

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.

lvanvugt

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;

kriki

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');

MBerger

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.

kriki

Depends if you need to trigger the code or not.

Kishorm

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.

vivek4121

Thanks for the Replies.

Can you guys please elaborate this term a bit more "SQL Server goes astray"?

lvanvugt

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.

vivek4121

=D> Thanks a lot.