Quick Tip: Dexterity Ranges and how to update Fields that are part of the Key

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 Scenario

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.

 

Original Code Excerpt

range clear table SOP_Serial_Lot_WORK_HIST;

clear table SOP_Serial_Lot_WORK_HIST;
'SOP Type' of table SOP_Serial_Lot_WORK_HIST = 'SOP Type' of table SOP_LINE_WORK;
'SOP Number' of table SOP_Serial_Lot_WORK_HIST = 'SOP Number' of table SOP_LINE_WORK;
'Line Item Sequence' of table SOP_Serial_Lot_WORK_HIST = 'Line Item Sequence' of table SOP_LINE_WORK;
range start table SOP_Serial_Lot_WORK_HIST by number 1;

fill table SOP_Serial_Lot_WORK_HIST;
'SOP Type' of table SOP_Serial_Lot_WORK_HIST = 'SOP Type' of table SOP_LINE_WORK;
'SOP Number' of table SOP_Serial_Lot_WORK_HIST = 'SOP Number' of table SOP_LINE_WORK;
'Line Item Sequence' of table SOP_Serial_Lot_WORK_HIST = 'Line Item Sequence' of table SOP_LINE_WORK;
range end table SOP_Serial_Lot_WORK_HIST by number 1;

change first table SOP_Serial_Lot_WORK_HIST by number 1;
while err() <> EOF do
'SOP Number' of table SOP_Serial_Lot_WORK_HIST = '(L) New Number';
'SOP Type' of table SOP_Serial_Lot_WORK_HIST = '(L) New Type';
save table SOP_Serial_Lot_WORK_HIST;
check error;    

    change next table SOP_Serial_Lot_WORK_HIST by number 1;
end while;
range clear table SOP_Serial_Lot_WORK_HIST;

 

 

The Problem

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.

 

The Solution

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:

 

Updated Code Excerpt

local 'SOP Number' l_number;
local 'SOP Type' l_type;

range clear table SOP_Serial_Lot_WORK_HIST;

clear table SOP_Serial_Lot_WORK_HIST;
'SOP Type' of table SOP_Serial_Lot_WORK_HIST = 'SOP Type' of table SOP_LINE_WORK;
'SOP Number' of table SOP_Serial_Lot_WORK_HIST = 'SOP Number' of table SOP_LINE_WORK;
'Line Item Sequence' of table SOP_Serial_Lot_WORK_HIST = 'Line Item Sequence' of table SOP_LINE_WORK;
range start table SOP_Serial_Lot_WORK_HIST by number 1;

fill table SOP_Serial_Lot_WORK_HIST;
'SOP Type' of table SOP_Serial_Lot_WORK_HIST = 'SOP Type' of table SOP_LINE_WORK;
'SOP Number' of table SOP_Serial_Lot_WORK_HIST = 'SOP Number' of table SOP_LINE_WORK;
'Line Item Sequence' of table SOP_Serial_Lot_WORK_HIST = 'Line Item Sequence' of table SOP_LINE_WORK;
range end table SOP_Serial_Lot_WORK_HIST by number 1;

change first table SOP_Serial_Lot_WORK_HIST by number 1;
while err() <> EOF do
{ Store Key Fields }
l_number = 'SOP Number' of table DTM_Serial_Lot_WORK_HIST;
l_type = 'SOP Type' of table DTM_Serial_Lot_WORK_HIST;

    'SOP Number' of table SOP_Serial_Lot_WORK_HIST = '(L) New Number';
'SOP Type' of table SOP_Serial_Lot_WORK_HIST = '(L) New Type';
save table SOP_Serial_Lot_WORK_HIST;
check error;    

    { Restore Key Fields }
'SOP Number' of table DTM_Serial_Lot_WORK_HIST = l_number;
'SOP Type' of table DTM_Serial_Lot_WORK_HIST = l_type;
change next table SOP_Serial_Lot_WORK_HIST by number 1;
end while;
range clear table SOP_Serial_Lot_WORK_HIST;

 

 

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.

 

More Information

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.

David