Quick Tip: Working with VBA, Extender and Sales Transaction Entry

  • Article

David Meego - Click for blog homepageI have completed the development project I was working last week and have another Quick Tip that came out of the project. For the previous Quick Tips related to this project have a look at Quick Tip: Passing Variables to SQL Server and Quick Tip: Passing Date and Time Variables to SQL Server.

The project involved Visual Basic for Applications (VBA) code as well as an Extender window linked to the Sales Transaction Entry and Sales Item Detail Entry windows. Both the VBA code and the Extender window object needed to reference the primary key fields for the Sales Order Processing (SOP) transaction. 

The primary key for the SOP_HDR_WORK (SOP10100) table contains the 'SOP Type' (SOPTYPE) and the 'SOP Number' (SOPNUMBE) fields. So you would think that it alright to use these two fields when defining your Extender Window and add these two fields to Visual Basic for use with your VBA code. Until a few versions ago, you would have been fine with that assumption.

When the Advanced Distribution module was introduced and Fulfillment Order document type was added, a potential issue was created.

Without Advanced Distribution registered, the Fulfillment Order document type is not included and the value (by position) of the 'SOP Type' field on the window matches the value stored in the database tables.

Document Type Window Value   Database Value  
Quote 1 1
Order 2 2
Fulfillment Order N/A N/A
Invoice 3 3
Return 4 4
Back Order 5 5

With Advanced Distribution registered, the Fulfillment Order document type is included after the Order document type and the value (by position) of the 'SOP Type' field on the window no longer matches the value stored in the database tables.

Document Type   Window Value   Database Value  
Quote 1 1
Order 2 2
Fulfillment Order 3 6
Invoice 4 3
Return 5 4
Back Order 6 5

In Dexterity, there is a global function SOPTypeToInt() in the DYNAMICS.DIC to handle the mapping of Window Value to Database Value for us, but how do we handle this for Extender or VBA?

So this is where my Quick Tip comes in...

Instead of using the 'SOP Type' field in your Extender objects or VBA code which will not always match the true value stored in the table, use the 'SOP Type Database' field.  This field will match the value stored in the table and will avoid any confusion about whether an Invoice document is SOP Type 3 or 4.

For Extender, this change is simple, when defining the object, just select 'SOP Type Database' instead of 'SOP Type' on the windows where the 'SOP Type Database' field exists.

For VBA, selecting the 'SOP Type Database' field is a little harder as the field is a hidden field that can't be easily added to Visual Basic. It is possible to modify the window to make it visible so it can be added to VBA and then hiding it again. However there is a quicker (but unsupported) method.

The quicker method involves adding the SOP Type field (or any other field) to Visual Basic instead. Then saving the window as a Package file using Tools >> Customize >> Customization Maintenance. Make a backup of the Package file. Then open the Package file with Notepad.exe and find the field you added, for example:

    WindowField "SOP Type"    
    {
     ArrayIndex "0"
     DisplayName "SOPType"
     Local "false"
     ProgrammerName "SOP Type"
    }

Edit the package to change the Field information.  WindowField is the name that Dexterity uses with spaces and including the "(L) " for local fields. DisplayName is the Name to be used in VBA and is usually the name of the field (or prompt) without spaces (or the "(L) " for local fields). Local needs to be set to true or false depending on if the field is a local field, ie. does it have the "(L) " prefix for WindowField. Finally the ProgrammerName is the name of the field with spaces, but not including the "(L) " prefix for local fields.

    WindowField "SOP Type Database"    
    {
     ArrayIndex "0"
     DisplayName "SOPTypeDatabase"
     Local "false"
     ProgrammerName "SOP Type Database"
    } 

Save the edited package file and import it back into Dynamics GP. Now when you write your VBA code, use SOPTypeDatabase instead of SOPType.

Hope you find this tip helpful.

David