VBA – Adding Extender Windows to GL Transaction Entry Lines


David Meego - Click for blog homepageLast week I had an interesting support case that I thought I would share with you.

The customer had created an Extender window to capture additional information for distribution lines on the General Ledger Journal Entry (GL_Transaction_Entry) window. The unique identifier for a distribution line is the Journal Entry field from the main window and the Sequence Line field from the scrolling window. These are the fields used when creating the Extender settings for the additional information to be stored.

See the example Extender Window setup below:

 

The issue we were seeing is that the value of the Sequence Line field for new lines remains as zero (0) until the user tabs off the line and the line is saved.  If information was added for the new line before it was saved, that information would be saved with a value of zero for the Sequence Line field and would not be correctly linked to the line once the actual Sequence Line value was assigned.  The work around solution without customization is to make sure the line is saved and then come back to it to add the additional information.... not a very user friendly solution.

Many windows in the application with scrolling windows (aka grids) do not populate the Sequence Line (or equivalent) field until the line is saved.  Often this is one way that the code knows that the line is a new line and not an already saved "existing" line. Windows built using this method will not work well for Extender information linked to the lines until the line is saved.

The partner asked why the Sales Transaction Entry (SOP_Entry) window works with Extender as it populates the Line Item Sequence field as the line is created, before it is saved.  The reason that this window behaves differently is that there is additional data linked to a line for a SOP transaction (such as taxes, serial/lot numbers, etc.). To allow the table relationships to be created for the additional child data, the Line Item Sequence value must be assigned earlier.  In the case of the Journal Entry window, there is no additional child data and so no need to populate the Sequence Line field in advance.


The customer was licensed for the Customization Site License, so it meant that Visual Basic for Applications (VBA) code created by the support team or the partner could be installed and used on the customer's system.  So I set to work finding a solution using Modifier & VBA to create a customization to allow the Extender window to work.

My initial thoughts were to take the focus to a field on the header after the Account Number field had been entered, using the AfterUserChanged() event.  This would force the line change script to run to populate the Sequence Line field and save the line.  Then I could return the focus to Debit Amount field. This sounded great in theory, but failed in practice due to the following error You may not enter a transaction without an amount. dialog.

I did think about entering some dummy value into the Debit Amount field to allow the save to work, but that was just getting too messy.  A different approach was needed.

My next idea was to populate the Sequence Line field earlier, when the line is created. This is the same approach that the Sales Transaction Entry window uses.  Looking at the source code for the window I could see where the Sequence Line field is populated (in the Scrolling window Line change script). I could also see that there was not any other code that was looking to see if the Sequence Line field was empty() or equal to 0.  It was important to check for any code looking for the zero value as I did not want to break any existing functionality by populating the Sequence Line field early.

So using VBA code, I tried to replicate the Dexterity code with the following script:

Private Sub AccountNumber_AfterUserChanged()
    If SequenceLine = 0 Then
        SequenceLine = TransactionEntry.NextSequenceDLR
        TransactionEntry.NextSequenceDLR = TransactionEntry.NextSequenceDLR + 500
    End If
End Sub

Both the 'Sequence Line' and '(L) Next Sequence DLR' fields on the windows are hidden fields. This means you cannot add them to VBA easily.  Well, you could modify the window to make the fields visible and place them in the visible area of the window, change the security to show the modified window, change the VBA Eventmode property to the modified window, add the fields to VBA, change the security back to the original, delete the modified window and change the VBA Eventmode property back to the original..... or you can do something completely unsupported, but much quicker and easier.  So, I added "place holder" fields that I could see from the main window and the grid, exported the package using Customization Maintenance, edited the package in Notepad to change the fields to the hidden fields and imported the package back. 

See the package excerpt below.  WindowField is the Dexterity Field name (including "(L) " for local fields).  DisplayName is the VBA Name without spaces (without "(L) " for local fields), this can be the prompt text for fields linked to a prompt.  ProgrammerName is the name with spaces (but without "(L) " for local fields).

    WindowField "(L) Next Sequence DLR"    
    {
     ArrayIndex "0"
     DisplayName "NextSequenceDLR"
     Local "true"
     ProgrammerName "Next Sequence DLR"
    }

While editing the package file works really well if you are careful, it is really easy to corrupt the file.  Make sure that you have a backup file and if you mess it up, don't ask for help from Microsoft Support.

Anyhow, after all that work, the code fails because the fields are not editable. VBA generates a Run-time error '1008': Unsafe Operation. An attempt was made to set a value into an application field which is disabled. This operation could compromise the integrity of the application. error. Doh.

 

Again, while this idea was good in theory, it failed in practice. Yet another approach was needed.

To bypass the VBA "protection" logic that prevents non editable fields from being changed by VBA, we will have to use Dexterity to make the change for us instead. 

So, using another unsupported method, I created a script to execute Dexterity sanScript code via the Continuum Integration Library. The following VBA code uses Dexterity sanScript to achieve the same results:

Hybrid VBA and Dexterity sanScript code

I based the Dexterity code on the actual script from the source code, just adding the window and form qualification to make it work. This is because pass through Dexterity sanScript executes as though it is a global procedure and so resources need to be fully qualified. 

The user needs to tab off the Account Number field for this code to run.  So they must press the Tab key before pressing opening the Extender window, using menus or the Ctrl-J shortcut key.

I changed the Extender Settings on my test window to Auto Open on Field Exit from the Account Number field and to Auto Close.  This avoids the use of Ctrl-J entirely and makes sure that we have tabbed out of the field before opening the Extender window. See settings below:

 

Note: The VBA Package and the Extender Settings file are attached as an archive to the bottom of this post.

I hope you find this technique useful and can see that it often takes more than one attempt to get to a working solution.

David

20-Aug-2010: Make sure you look at the follow up post: VBA - Displaying Extender Data for GL Transaction Lines on the GL Inquiry window.

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

GL_Transaction_Entry.zip

Comments (13)

  1. Martin Olsen says:

    Thanks David for this post – and the effort in finding a solution. I think it will help many people who have struggled with the same concept.  I have seen customers try alternate workarounds such as using the journal number, account number and amount as keys – but there is always a chance of a duplicate in this scenario.

    Do you think we could come up with a feature request that in a future builds of GP a journal does populate the sequence number earlier?  This would enable a plethora of opportunities for Journal  line level eXtender  solutions .

  2. Naren says:

    In one of the clients I tried to include the Journal Number + Open Year + Line Sequence. Since Extender uses the fields in the alphabetic order, So the Account number comes first then the journal and then the line sequence. For example if the Journal is 100 and Sequence is 1500 results in 1001500.00000. You might run into a duplicate key if the Journal is 1001 and Sequence is 500 which results in 1001500.00000. So we found that it is better to use the Open Year in the middle. Ex. 1002010500.0000. It is working good so far.

  3. David Musgrave says:

    Hi Naren

    Thanks for your comment.  I have also seen people include the account number or account index in the key fields.

    David

  4. David Musgrave says:

    Post from Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com/…/vba-adding-extender-windows-to-gl.html

  5. Frank Jin says:

    Great technique to see how to call sanscript in VBA !!!

    I was thinking adding a Dex trigger for AccountNumber Change Post to assign the line sequence number would achieve the same result?

    Thanks

  6. David Musgrave says:

    Hi Frank

    What we have created in VBA is almost identical to using Dexterity to add an After Account Number Change trigger to assign the Line Sequence.

    While this technique is very useful. Don't forget that it is not supported.

    David

  7. David Musgrave says:

    Post from Jivtesh Singh at About Dynamics, Development and Life

    http://www.jivtesh.com/…/dynamics-gp-customizations-best.html

  8. Prakash says:

    Thank you David for sharing this article.

    I have a question: I tried to create a similar Extender for "Payables Transaction Entry Distribution Scroll" window, but in that distribution scroll window, there is no unique field available like "Sequence line" or "Sequence number".

    Because of this issue, I am not able to create an unique key field in Extender for that scroll window. Please let me know your valuable suggestion to solve this issue.

    Thank you..

    Prakash

  9. David Musgrave says:

    Hi Prakash

    The Distribution Sequence Number is set in the table buffer only, there is no window field.

    You could try adding the Distribution Sequence Number field to the scrolling window.  This will be populated for already saved lines, but will not be populated for new lines.  

    You could populate it yourself (when it is empty) as the number that will be used when it is saved is the '(L) Next Seq Number' field on the main window.

    That should then allow you to work with Extender.

    David

  10. Prakash says:

    Thank you David for your response..

    I have tried this method, also I have created a local field in the scroll window and assign the sequence number using VBA. But, how to add these fields in Extender key fields section?

    In the Extender "Key fields lookup" window, I am not able to see this "Distribution Sequence Number" or any new local field created in the modified window.

    Is there any work around available for adding fields from the modified window as a key field in Extender? Please help.

    Thank you..

    Prakash

  11. David Musgrave says:

    Hi Prakash

    That's a good point. You will not be able to see modified added fields in Extender.  

    I think it is time to use VBA or Dexterity instead.

    David

  12. Prakash says:

    Thanks for your suggestion David…

Skip to main content