When creating Extender Windows, typically the Key Fields for the Extender Window should match the Key field for the Dynamic GP window.

So for example, when creating an Extender Window that attaches to the Dynamics GP Item Maintenance window – we would choose the “Item Number” field.

pat

Figure 1: This is the GP Key field for this window

So this is obviously much easier for windows that use a single key field. But it is more complex when we would like to attach to a scrolling window in Dynamics GP.

From a design perspective, almost every scrolling window in Dynamics GP scrolling window will have a unique key. While not universal, most windows will use a scrolling window key field that is a combination of the main window key field and a sequence number. Typically this sequence field will use the word “Sequence” in the field name. Common ones in Dynamics GP are Sequence Number, Line Item Sequence, and Distribution Sequence Number.

So if we look to a typical distribution entry window that contains a scrolling window such as the Payables Transaction Distribution Entry.

I wanted to call out this particular window for a couple of reasons.

  1. The Distribution Sequence Number was just added to the GP UI in GP 2015. I’m not sure why it was added in this release when it would have been useful (for eOne and Extender users) in all the previous versions.
  2. This window is also problematic in that it does something I don’t think any other window does. It actually has the ability to re-sequence the distributions which is problematic for Extender.

When creating my Extender Window definition, we can see that the Extender Window will be attached to the distribution lines.

Figure 2: Found the "sequence" for this scrolling window

Figure 2: Found the “sequence” for this scrolling window

We can see that I’ve chosen good primary key values of Voucher Number (from the main window) and Distribution Sequence Number from the Distribution_Scroll that makes the specific line unique.

Again, the field Distribution Sequence Number is new with GP 2015. Because it isn’t on the GP UI in previous versions, we would have to pick other fields to make our Key Fields to try to make the line unique.

Unfortunately there aren’t really many good options in previous versions since I can enter in essentially “duplicate” data such as Account Number and Distribution Type fields. Sometimes users will pick the Account Number and the Debit & Credit Amounts as the best they can do. So those aren’t good – but again the best you can do without the Distribution Sequence Number field available.

Creating a payables invoice and opening the Payables Transaction Entry Distribution window, I’ve added a few more PURCH distributions to the default distributions.

Figure 3: Extra distributions added

Figure 3: Extra distributions added

Choosing the PAY distribution, I then open my Extender Window.

Figure 4: Extender data added to the selected line

Figure 4: Extender data added to the selected line

The key fields – Voucher Number & Distribution Sequence Number look correct. As you may (or may not) know, typically “Sequence” numbers in Dynamics GP are in increments of 16384.

So knowing that, our Distribution Sequence Number for the 2nd line (the PAY distribution as you can see from the screenshot) would be correct.

So the second point I referenced above – this window has the ability to re-sequence the GP data.

In the Payables Transaction Entry Distribution window, I press the “Redisplay” button.

Figure 5: Let's see what this does

Figure 5: Let’s see what this does

We notice immediately that my distributions have been re-ordered in the scrolling window. Now, is the window just “refreshed” and ordered differently in the UI?

No – GP physically changes the underlying data by changing the Distribution Sequence Number of my data.

If we look in SQL at the underlying data:

Figure 6: TSQL to show the underlying data has changed

Figure 6: TSQL to show the underlying data has changed

We can see that the sequence value for the PAY distribution (account 000-2100-00) is now 65536 and we can see that in the UI and the data it is now the fourth distribution.

So how does that affect our Extender data?

Not well unfortunately.

If I select the second row (the same position I picked previously):

Figure 7: Extender data is unchanged in the UI

Figure 7: Extender data is unchanged in the UI

We can see that my Key fields are “wrong” because before this record was linked to the PAY distribution. But because GP re-ordered the underlying data and the Extender data stayed the same now my Extender data is attached to the wrong PM distribution.

This happens because Extender only knows about the data on the UI. It doesn’t (and cannot) know where it comes from in the database and only knows what is on a window.

So in this example, our underlying data changed by a GP process and our Extender data is now out of sync with the data it should belong to.

So is there a solution to this?

Well, the easiest solution is to tell your users to NOT press that button or use Extender Enterprise code to hide it (or another tool like Modifier/VBA).

But being talented programmers, I think we would like to let our users use the application and all of the features and not break our Extender data.

To resolve this, the only “good” solution that I can think of is to use a SQL database update trigger. In the trigger, we need to find and update the Extender data to the new value of the Distribution Sequence Number.

To test this out, I re-defaulted my Payables Distributions and then re-entered the last 2 PURCH distributions so that the window looks like I started initially.

We can also see that the GP and Extender data looks OK as expected as the 000-2100-00 PAY distribution is once again second on the list and the DSTSQNUM matches the Extender_Key_Values_2 field as expected.

Figure 8: TSQL showing the GP & Extender data that is synchronized

Figure 8: TSQL showing the GP & Extender data that is synchronized

Now let’s press the Redisplay button and hold our breath.

The window is re-ordered again the same as before – but how does the Extender data look?

Let’s take a look at the SQL data.

Figure 9: TSQL showing the modified records by GP and our trigger

Figure 9: TSQL showing the modified records by GP and our trigger

Success! We can see that the 000-2100-00 distribution has been re-sequenced as before. But this time, we can see that the Extender_Key_Values_2 record has also been changed to the same value.

I wouldn’t need to pull this up in the GP interface to know that it has truly been synchronized correctly – but for those skeptics out there.

Figure 10: We can see the newly sequenced data as well as our Extender data attached to the correct record

Figure 10: We can see the newly sequenced data as well as our Extender data attached to the correct record

We can see that my Extender data is now synchronized to the newly modified GP PM10100 data and displays on the record I expected it to.

I’ve attached my Extender 2015 window and SQL update trigger HERE to use as a test and starting point.

Best Regards,

Patrick
Senior Support Engineer
eOne Solutions