Hybrid - Dynamically Populating a Modifier Added List Example


When using the Modifier to add a list field (drop down list, button drop down list, listbox, multi-select list box) to a window as a local field, the only option available to populate the list was to add static values.  This is because there is no functionality available in VBA to add items to a Dexterity list field.


This example shows a method which calls Dexterity sanScript from VBA to get Dexterity to add items to the list field for us.  This means that the list field can be dynamically populated based on business rules or data in a table.


Normally, when you call Dexterity sanScript using the Continuum Integration Library, the script is executed in the context of the original form (as it appears in the application dictionary).  This means that even though you can get the code to run, it would not be able to see the new local field which only exists in the modified form (as it appears in the custom forms dictionary).


The trick here is to specify that the code should run in the context of modified form.  To change the dictionary context and or change to use the modified form, you can use the commands below.


To change the Dictionary context, use the following command to specific the product ID.



CompilerApp.CurrentProductID = 0 ' Optional line to change Dictionary Context


To change the Dictionary context to use the modified version of a window, use the following command.



CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified" 


This example changes the User Setup window to add a drop down list at the bottom of the window.  This list is dynamically populated with the names of the companies a user has access to when a user is displayed on the window. The pass through sanScript code loops through a range on the SY_User_Company_Access_REL (SY60100) table to read the Company ID and then looks up the SY_Company_MSTR (SY01500) table to get the Company Name.


NOTE: This customisation uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.


Example code for v8.0, v9.0 & v10.0 is attached at the bottom of the article.


Please see the "Installation Instructions.txt" file in each version's archive for more information.

User Setup Populate Drop Down List.zip

Comments (12)

  1. Thanks, this saved my rear end.  I loaded the dropdown values by looping through an ado recordset and adding the values as is shown in the windows activate method.  Problem was that the dropdown value property gets/sets the index of the selected item, and not the value.  The trick here was to create a dynamic array that mirrors the dropdown with the values that can be referenced using the same index as the dropdown when when saving or retrieving from the db.

    Thanks a bunch for this blog,

    Laszlo

  2. David Musgrave says:

    Hi Laszlo

    When you use the add item command in Dexterity you do have the option of adding a long integer data value to the text value to added to the drop down list.

    You would then need more pass through Dexterity to read this value with the itemdata() function when a selection is made.

    Another option is to add a second hidden list field and add your "index" values to that at the same time you add the visible data to the first list field.

    Thanks for the feedback.

    David

  3. Danny says:

    I am trying to populate a drop down list in the line item grid of the SOP_Entry window.

    How do i refer to my field in the line item grid of the sop window?

  4. David Musgrave says:

    Hi Danny

    The LineScroll scrolling window on the SOP_Entry window is available as the SalesTransactionEntryDetail module in VBA.  Each scrolling window is treated as a separate Grid module in VBA.

    Just prefix the field with SalesTransactionEntryDetail. when referencing a grid field from outside of the grid module.

    Hope this helps

    David

  5. David Musgrave says:

    Post from Jivtesh Singh at About Dynamics, Development and Life

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

  6. David says:

    This post is great and works on GP2010 as well with no modification. I am actually using your example to change a color field by calling Dexterity sanScript from VBA . But I can't figure out how to pass the name of my field over Dexterity. IS it possible?

    Commands = "local boolean result;" & vbCrLf

    Commands = Commands & "result = Field_SetFontColor('CompanyID', COLOR_RED);" & vbCrLf

  7. David Musgrave says:

    Hi David

    Because the pass through Dexterity script runs as though it is a global procedure, any references to fields must be fully qualified with the of window <WindowName> of form <FormName> syntax.

    David

  8. David Musgrave says:

    Posting by Steve Gray at DynDeveloper.com

    dyndeveloper.com/article.aspx

  9. Steve Gray says:

    David:

    I'm trying to use this technique in GP 2013 and I cant get a reference to Dynamics.Application, I'm getting the 'ActiveX can't create' error. Will this work in 2013?

Skip to main content