Getting Table and Field Data out of Dexterity Dictionaries


David Meego - Click for blog homepageToday, I have something special. Also, something that I had not planned to do…

Over the years since Mark Rockwell and I published the Pushing the Limits with Dexterity, I have been asked a number of times about accessing dictionary resources such as tables and fields. This is information that is not stored anywhere in SQL tables and can only be accessed using Dexterity sanScript code to “read” the resources definitions.

Usually, I would point the developer to the Resource_ function library and the conference materials. However, in today’s world where developers are not always familiar with Dexterity, sending them off to learn Dexterity and write their own code is probably not the best solution.

The reason that this topic has come up today is because of the .Net application that reads table & field display names post on the Community Forum.  The .Net developer on this post has an external .Net application that needs to work with the Microsoft Dynamics GP tables, but would like to use the user friendly display names rather than the cryptic physical names (as per the SQL table and column names).

The developer knows how to call Dexterity from a .Net application, but does not have the Dexterity knowledge to write the code to extract the data. Getting to table data is not too hard, but pulling the field data out of the dictionary is complex due to the handling required for array and composite fields.  I decided that it would be worth writing and publishing some code that could be called as passthrough Dexterity sanScript which would output the resources as tab delimited text files.  These files can be read into Excel or any other application.

I used the Runtime Execute window in the Support Debugging Tool to write the code (shown below) which can be modified as desired.

Code to execute in context of Microsoft Dynamics GP Product Dictionary

Note: When executing this code via passthrough sanScript from VBA or Visual Studio, make sure the dictionary context is set to 0 for Dynamics using the .CurrentProductID property.

The Support Debugging Tool settings file with this code and example TABLES.TXT and FIELDS.TXT files are attached to the bottom of this post.

Let me know if you find this useful.

David

08-Jul-2010: Have a look at Mark Polino’s article about how he has used this code: Getting Table and Field Data out of Dexterity Dictionaries in Dynamics GP.

Resources.zip

Comments (15)

  1. David Musgrave says:

    Posting from Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com/…/getting-table-and-field-data-out-of.html

  2. Hendy says:

    Dear Mr. David,

    thanks for your sharing. this topic is what I'm looking for. I need all of the table technicalname because I'm building a Dexterity Tools module.

  3. David Musgrave says:

    Posting from Vaidy Mohan at Dynamics GP – Learn & Discuss

    http://www.vaidy-dyngp.com/…/new-resource-to-find-tables-fields.html

  4. David Musgrave says:

    Posting from Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com.au/…/excel-based-table-and-field-reference.html

  5. Great stuff, one possible enhancement; for drop down lists it shows the control type as Integer (as it is saved in the database etc.), would it be possible to show them as 'Drop Down List'? And then possibly extracting the available options of the drop down list?

  6. David Musgrave says:

    Hi Adriaan

    Obtaining the static text from a drop down list might be possible, but you do need to remember that many drop down lists in the product don't have static text entries, instead they are dynamically populated by code.

    David

  7. Hi David,

    Thanks for the response. I am busy with master data analysis for a customer and knowing the static values will be of great help, and in the case where it is indeed a DDL with no items I will flag those for further analysis. I'm trying to decrease a large volume of work, any help in this regard will be greatly appreciated.

  8. Hi David,

    Using the SDK I think I got it but can't post it here due to length restrictions, will be happy to share.

  9. Ramesh Bhat says:

    Hi David,

    Is there any tool or script avialable that can pull all the windows names and their fields names and export them into excel.

    Thanks for your help?

    Ramesh Bhat

  10. David Musgrave says:

    Hi Ramesh

    It would be possible but it requires some special techniques as mentioned in the Push the Limits with Dexterity conference materials (see link below).

    blogs.msdn.com/…/cross-dictionary-dexterity-development.aspx

    David

  11. Ramesh says:

    Thanks for your reply and help

  12. clpa says:

    are there any functions or procedures similar to getTableFieldCount and getTableFieldAtPositio i can use to get all windows for a specific form and all fields (global and local) for a specific window?

    thanks

    zhang

  13. clpa says:

    I got it from Push the Limits with Dexterity conference materials

  14. Hi Zhang

    Good job, that is where I would have sent you.

    David