Last week, I had an interesting case where a Dexterity Developer was having trouble getting some code to update a range of records selected in a table. The code would only update the first record in the range.
The developer was trying to move the contents of the SOP_Serial_Lot_WORK_HIST table for a line in the SOP_LINE_WORK table to the same line number in another document.
So that we know the key fields to be used in the range, the primary key for the SOP_Serial_Lot_WORK_HIST table is shown below:
The code below looks like it should work, the range is defined with the first three fields set to a value and the remaining fields are cleared/filled by virtue of the clear table and fill table commands. The while loop through the range is correctly written with change first and change next and looping while err() <> EOF do. Everything looks right except that when the code is run, only the first record in the SOP_Serial_Lot_WORK_HIST table is updated.
Have you worked out what the problem is?
Well, it all comes down to understanding exactly how the change next table command works. It does not look at the last record accessed in the physical table and then move to the next record. No, what it does is look at the current values in the key fields in the table buffer and then finds the next record in the physical table with values greater than those key fields.
Now have you worked it out?
The code changes the values of two of the key fields to values which are outside of the defined range and then saves the record. The table buffer still has those two new values in it when we request the next record. So that next record probably does not exist and even if it did it would be outside of the defined range and so returns err() = EOF. The code drops out of the while loop and only the first record has been updated.
There are a couple of solutions and I will mention both, but I will give my preferred method first. This first method is easier to understand, but a little more work to code.
The important point is to have the correct values in the key fields of the table buffer before you execute the change next table command. The easiest way to achieve this is with a couple of local variable to store the original values and then restore them after completing the updates.
The following code shows this technique:
The second technique is to change the change next table to change first table. As each record is removed from the range as it is saved, using change first table will keep picking up the next record until the range is empty. While this method only changes one word in the code, it is less readable. Another developer might even look at the code later and think it is in error as you have change first table where a change next table command should normally be.
For more information on ranges, check out the following Knowledge Base (KB) article I wrote:
- Rules to maintain alternate windows when you make customizations in Dexterity in Microsoft Dynamics GP (KB 922057)
Hope you found this useful.