Insert line in Dexterity scrolling window

I'm still warming up to Dexterity scrolling windows.  Combined with table buffers the two have given me fits for a while.  While filling and deploying scrolling windows seems to work okay, I've had to resort to using RANGE WHERE to load the scrolling window because of problems I've had with this feature.

In attempting to develop a replacement SOP Order Entry window I ran into the requirement for numbering and ordering the data in the scrolling window.  This turns out to be a lot harder than it looks!

I always rely on the sample code provided in the manuals.  In this case the Programmer's Guide Volume 2 explains the use of scrolling windows and provides sample scripts for reference.  However I simply could not get the sample scripts to work.

Back against the wall I had to roll my own.  Turns out it's not so hard if you know what you're doing (and I'm getting there.)

Here is the code from the PG v2 p104:

local currency temp;
if not empty('Sequence Number' of table Invoice_Data) then
{Store the sequence number of the current record.}
temp = 'Sequence Number' of table Invoice_Data;
{Read the sequence number of the previous record.}
get prev table Invoice_Data;
if err() <> EOF thentemp = temp + 'Sequence Number' of table Invoice_Data;
end if;
temp = temp / 2;
clear table Invoice_Data;
{Save the new record in the table.}
'Invoice Number' of table Invoice_Data = 'Invoice Number';
'Sequence Number' of table Invoice_Data = temp;
save table Invoice_Data;
end if;

The odd statement to me is the line that reads get prev.  I know what this statement does in the sample code - backs up to the previous line to get that line's number, then adds it to the line we are to insert before and divides the sum by 2.  Should put us right in the middle of the two numbers for the new line, right?

Except it does not.  Seriously?!  I certainly could be missing something but this flat-out does not work.  I get error 16, end-of-file.  WTF?!

Again scrolling windows and table buffers are not my forte', so I cooked up a Plan B for this that seems to work well.

Somewhere in that user guide it says that form-level procedures use their own table buffers.  This is key as it avoids conflicts with the scrolling window table buffer.  So I can do things in form scripts that avoid messing with my scrolling window table, which can earn you funky results in the window (and I HAVE seen FUNKY results.)

I found that if I use a form-level function and pass it the key of the current scrolling line (which includes the sequence number I'm trying to split) I can return the split sequence number to the Insert script so the new line gets placed appropriately.

In pseudo code here's how the form function will work:

• Set the appropriate range on the scrolling window (for the key provided.)
• Read the sequence numbers in order and save each number until the next row.
• When you reach the sequence number provided stop and average the two.

The only trick is if the first row is passed as the sequence.  In that case simply halve it and pass that back.  Here is my version of this code (works so far:)

function returns currency nextsequence;
in string ordernumber;
in currency currentsequence;

local currency calcseq;

range clear table SOPLineTemp;
clear table SOPLineTemp;
'SOP Number' of table SOPLineTemp = ordernumber;
'User ID' of table SOPLineTemp = 'User ID' of globals;
range start table SOPLineTemp by SOPLineTemp_Sequence;
fill table SOPLineTemp;
'SOP Number' of table SOPLineTemp = ordernumber;
'User ID' of table SOPLineTemp = 'User ID' of globals;
range end table SOPLineTemp by SOPLineTemp_Sequence;

get first table SOPLineTemp by SOPLineTemp_Sequence;
calcseq = 'Line Item Sequence' of table SOPLineTemp;
if calcseq = currentsequence then
  calcseq = calcseq / 2;
end if;

while err() = OKAY and 'Line Item Sequence' of table SOPLineTemp <= currentsequence do
if 'Line Item Sequence' of table SOPLineTemp = currentsequence then
  calcseq = ('Line Item Sequence' of table SOPLineTemp + calcseq) / 2;
 
else
  calcseq = 'Line Item Sequence' of table SOPLineTemp;
 
end if;

get next table SOPLineTemp by SOPLineTemp_Sequence;
end while;

nextsequence = calcseq;

Notice I'm not using the GP SOP Detail Item table.  I don't want to be subject to orphan detail lines from this window so I do the work to bring the existing detail in then write it back and update SOP Detail.  Quite a bit of extra work but it provides true abstraction and allows the user to clear their changes without the dreaded "Save or Delete" do-or-die option (that SOP users know all too well.)

I would also like to take a minute to point out SOME of the MAJOR flaws in SOP entry that my window avoids:

• Zero master numbers and note ID's.
• Disparate tax, salesperson and ship-to codes (none of which differ EVER in our orders.)
• Data scattered across 5 different windows (I have placed all our fields on a single window.)

Also I make good use of eConnect to handle ALL my SOP inserts and updates.  And we have a number of custom fields that reside in a side-car table (and rely on the master number so avoiding the zero master number is a BIG PLUS!)

One last thing.  In the Line Insert script I had to use a local variable to contain the result of this function.  I then assign the local variable to the row being inserted.  For whatever reason this had to be done or it simply would not work.

I hope this helps your coding efforts.  If you poke any holes in this please let me know in the comments.  This is going to user testing tomorrow, but I think I've beat on it pretty hard so far.