VBA - Displaying Extender Data for GL Transaction Lines on the GL Inquiry window
You might remember last month when we had a support case where the customer wanted to use Extender additional information against the General Ledger Journal Entry (GL_Transaction_Entry) distribution lines.
We discovered that the Sequence Line field needed for the unique identifier for the line record was not populated until the line was saved. This meant that Extender could not be used with a line until after it had been saved. Not exactly an optimal situation. In the VBA - Adding Extender Windows to GL Transaction Entry Lines post we used a VBA & Dexterity hybrid customization to populate the Sequence Line earlier so that it can be used with Extender during the initial data entry.
Well, now we have a continuation of the support case. This time the customer wanted to be able use the same Extender additional information on the Journal Entry Inquiry (GL_Journal_Entry_Inquiry) window. A fairly simple ask you would think. Well not according to my friend Murphy. Using the Extender settings shown below you would expected everything to work.
We can even use an Alternate Key to make sure that the fields are mapped correctly.
While this setup is correct ... it does not work. The problem again relates to the Sequence Line field. It turns out that the Sequence Line field in the scrolling window is never populated and always remains a zero value. You can see this in the following screenshot.
Looking further into what is happening it turns out the the Sequence Line field does not actually exist in the GL_YTD_TRX_OPEN (GL20000) table. The field used in the table is called Sequence Number, so when the data is copied from the table to the window the Sequence Line field is never populated.
So how can we fix this one using Visual Basic for Applications (VBA) ...
For a start we need to get the value of the Sequence Number field from the table. VBA is unable to access Dexterity table buffers, so we cannot get directly to the table, however there is a simple method that will work for us. Using the Modifier we can drag a field from the linked table onto the scrolling window and as long as the AutoCopy property is true (which is the default) it will be populated. So I dragged out the Sequence Number field onto the window.
My plan now is to use VBA to copy the value from the Sequence Number field to the Sequence Line field. This is necessary as Extender can only see fields that exist on the original window and so cannot "see" the modifier added Sequence Number field. To avoid getting the Unsafe Operation errors discussed in the previous post, I changed the Sequence Line field from non-editable and hidden to editable and visible. This really makes no difference as the scrolling window is read only and the field is not in the visible area of the window. But it will keep VBA from throwing an exception.
I changed security to use the now modified window and then added the window to VBA. As I could not see the Sequence Line and Sequence Number fields, I added the Debit Amount and Credit Amount fields to VBA instead. Then I exported the package with Customization Maintenance and used good ol' Notepad.exe to change the fields to the sequence fields. See the previous post for information on this useful but unsupported technique.
Finally, I added one line of VBA code the JournalEntryInquiryDetail module to populate the Sequence Line field.
VBA code to populate Sequence Line field
Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)
SequenceLine = SequenceNumber
End Sub
Now the Extender window can get a valid Sequence Line value and works as it was meant to.
Note: The VBA Package and the Extender Settings file are attached as an archive to the bottom of this post.
You must change security settings to use the modified window for this example to work.
Hope this example helps you.
David
// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)