Using eConnect from Dexterity - Part 3

I forgot I promised a sequel for this series regarding transactions.

I've done many of these and most include quite a bit of logic.  Some have intense logic sequences, depending on the data source.  I'll explain.

Transactions usually involve some sort of "Document Number".  This could be a JE #, a PO #, a SO #, or some other similar thing.  What's important is this is usually a unique identifier and cannot be repeated.

I mentioned in Part 2 that there are "next number" connection points in eConnect, and these might not be documented in the SDK (but can be found as "ta" stored procedures in a GP company database.)  You can use those to obtain a next number, which is typically the first step required in adding a transaction (after the batch, that is.)

The next steps will depend on the fact that most transactions in GP involve detail lines and related headers.  Sometimes, as certain modules require, there may be multiple types of details required.  The obvious example here is if you want to supply your own GL distribution instead of the defaults from GP.

This points out another nice feature of eConnect.  It will default supporting details for your transaction just like GP would if a user was doing the entry.  So if you want GP to default the GL distribution you typically don't need to take any extra steps.  Contrast that with going direct to tables and eConnect wins every time!

But if you do want to override the defaults and provide your own data you typically need to flag the header to NOT default the details you want (and may have already) provided.  I have found these clearly indicated in the SDK for the header, if applicable.

The sequence following getting the next number will involve creating all the supporting detail records first, then creating the header.  This typically involves WHILE loops and keys that support the ordering of transactions so you can insert the details, and when some key field changes, insert the header and get the next number.

I will admit sometimes the logic required to get the steps right in Dex can be involved.  And it's important to step into it and proceed with suitable test data.

And furthermore I would suggest testing your inserts from Query Analyzer in SQL Server.  (You are using a test server, right?)  This will tell you that your handling of the eConnect calls work before you start with Dexterity.

There's another reason I like using Dexterity for this: Because I don't like to use cursors in SQL Server.  Actually I despise them, so doing the work programmatically in Dex is better for my soul!  I have also found that this process happens FAST so user wait times are negligible.

And there is no way you can start on this if you don't understand what the UI requires, and what the back-end involves.  So you will need to obtain plenty of sample transactions to process, run these through the GP UI, and observe the results in the tables when saved before you even start coding.  Then you will be able to approach your integration with the understanding of where you are heading.

I hope I've laid the ground-work for a simple transaction example, which I'll do as the next installment in this series.

And again your comments will encourage me to keep up with this so let's hear it!

Dexterity custom windows not showing up in Quick Links

I have been rolling out new Dex windows to simplify Dynamics GP for our users.  In doing so I would like to allow users to add my custom windows to Quick Links on their Home Page.

I tried this with a user and could not find a recent custom window I added.  This is done through the "Modify" (pencil) icon at the top of the Quick Links window on the Home Page.  That lists your current Links and has an Add button/drop list that you can use to add new windows to the Quick Links list via the Add Command window.

Looking deeper into this I could only find several of my custom windows in the Add Command list.  In addition some of these were located from places that were not correct (for example an  AP Transaction window under GL Utilities.)

This made no sense at all.  Time to pop the hood!

Digging even deeper I found, via the Tools > Table Finder window that the likely culprit involved is the Dynamics SY07110 (syMenuMstr) table.  Doing Script debugging and DexSQL logging around the Quick Links Add function showed this table being hit extensively.

 Looking at SY07110 it's clear this table has pointers to dictionaries and forms.  I found the ID of my dictionary and noticed only 11 entries in this table.  Believe me I've got a few more than 11 windows in my enhancement!

Googling this table I found nothing definitive but several references to removing records from that table and allowing GP to add them again.  WTH!

I did this in my Dev box, removed all the entries in SY07110 corresponding to my dictionary ID, and VOILA!  Problem solved!

Now all my windows are listed in the proper place when I try to add to Quick Links.

YAY!

Hoping this helps someone else down the line.

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.