Getting Table and Field Data out of Dexterity Dictionaries

Posted on Posted in Developing For Dynamics GP Tagged with , , 18 Comments

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

Today, 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 (now GP Power Tools) to write the code (shown below) which can be modified as desired.

Code to execute in context of Microsoft Dynamics GP Product Dictionary

local integer l_File_ID1, l_File_ID2;

local boolean result;
local integer i, j, k, count, l_dict, l_resid, l_groupid, d;
local string l_table_name, l_table_techname, l_table_physname;
local string l_group_name, l_group_techname, l_dictname;

local string l_Pathname1, l_Pathname2;
local integer fieldDataType;
local text l_Text1, l_Text2;
local string l_tab, l_crlf, l_ddl_options;

local integer x, y, z, field_count, array_count, composite_count, l_sequence;
local integer l_field, l_datatype, l_composite, l_length, l_keyable, l_decimals, l_controltype, l_composite_datatype, l_ddl_count;
local string l_field_name, l_field_physname, l_datatype_name, l_composite_name, l_composite_datatype_name, l_controltypename, l_ddl_option;

local long l_table_count, l_field_count;

local text l_code;

{ Formatting Constants }
l_tab = char(9);
l_crlf = char(13) + char(10);

{ Create File Paths }
l_Pathname1 = Path_GetForApp(PATH_DATAFOLDER) + "TABLES.TXT";
l_Pathname2 = Path_GetForApp(PATH_DATAFOLDER) + "FIELDS.TXT";

if ask("About to create " + Path_MakeNative(l_Pathname1) + " and " + Path_MakeNative(l_Pathname2) + " continue?", getmsg(YES), getmsg(NO), "") <> ASKBUTTON1 then
    abort script;
end if;

{ Open Files }
l_File_ID1 = TextFile_Open(l_Pathname1, 0, 0);
if empty(l_File_ID1) then
    warning "Unable to open " + l_Pathname1;
    abort script;
end if;
l_File_ID2 = TextFile_Open(l_Pathname2, 0, 0);
if empty(l_File_ID2) then
    TextFile_Close(l_File_ID1);
    warning "Unable to open " + l_Pathname2;
    abort script;
end if;

{ Create Header Rows }
l_Text1 = "DictID"  + l_tab + "DictName" + l_tab + "ResType" + l_tab + "ResID" + l_tab 
        + "TechnicalName" + l_tab + "PhysicalName" + l_tab + "DisplayName" + l_tab 
        + "GroupID" + l_tab + "GroupName" + l_tab + "GroupDisplay" + l_tab 
        + "Series" + l_tab + "Dictionary" + l_crlf;
TextFile_WriteText(l_File_ID1, l_Text1);
l_Text2 = "DictID"  + l_tab + "DictName" + l_tab + "ResType" + l_tab + "ResID" + l_tab 
        + "TechnicalName" + l_tab + "PhysicalName" + l_tab + "Sequence" + l_tab
        + "FieldID" + l_tab + "FieldName" + l_tab + "PhysicalName" + l_tab
        + "DataType" + l_tab + "ControlType" + l_tab + "Length" + l_tab + "Keyable" + l_tab + "Options" + l_crlf;
TextFile_WriteText(l_File_ID2, l_Text2);

{ Scan Installed Products }
for j equals 1 to Launch_CountProds() do
    l_dict = Launch_GetProdID(j); 
    l_dictname = Launch_GetProdName(l_dict);
    
    { For Each Series }
    for k equals 1 to 16 do
        count = Resource_StartSeriesFill(l_dict, 43{Tables}, k);
        if count &gt; 0 then
            { For the Tables in Series }
            for i = 1 to count do
                { Table Resource ID }
                l_resid = Resource_GetInfo(l_dict, 43{Tables}, i, l_table_name);
                
                if l_resid <> 0 and not empty(l_table_name) then
                    { Get Table Resource Info }
                    l_table_techname = Resource_GetResourceName(l_dict, TABLETYPE, l_resid);
                    l_table_physname = getPhysicalName(l_table_techname, l_dict) of form XTableInformation;
                    
                    { Get Logical Table Group Info }
                    l_groupid = Table_GetGroup(l_dict, l_table_techname);
                    if l_groupid &gt; 0 then
                        l_group_name = Resource_GetDisplayName(l_dict, GROUPTYPE, l_groupid);
                        l_group_techname = Resource_GetResourceName(l_dict, GROUPTYPE, l_groupid);
                    else
                        clear l_group_name, l_group_techname;
                    end if;
                    
                    { Write Table Info to File }
                    increment l_table_count;
                    l_Text1 = str(l_dict) + l_tab + l_dictname + l_tab + str(TABLETYPE) + l_tab + str(l_resid) + l_tab
                            + l_table_techname + l_tab + l_table_physname + l_tab + l_table_name + l_tab 
                            + str(l_groupid) + l_tab + l_group_techname + l_tab + l_group_name + l_tab
                            + str(k) + l_tab + l_dictname + l_crlf;
                    TextFile_WriteText(l_File_ID1, l_Text1);

                    { Scan Fields for Table }
                    clear l_sequence;
                    field_count = getTableFieldCount(l_table_techname, l_dict) of form XTableInformation;
                    { For each Field in Table }
                    for x = 1 to field_count do
                        { Get Field Info }
                        l_field = getTableFieldAtPosition(l_table_techname, l_dict, x, l_field_name, l_field_physname) of form XTableInformation;
                        l_datatype = getFieldDatatype(l_field_name, l_dict, l_datatype_name) of form XFieldInformation;
                        array_count = getFieldArraySize(l_field_name, l_dict) of form XFieldInformation;
                        composite_count = getDatatypeCountComponents(l_datatype_name, l_dict) of form XDatatypeInformation;
						l_ddl_options = "";
						
                        if array_count &gt; 0 then
                            if composite_count &gt; 0 then
                                { Array of Composite }
                                for y = 1 to array_count do
                                    for z = 1 to composite_count do
                                        l_composite = getDatatypeStaticValueAtPosition(l_datatype_name, l_dict, z, l_composite_name) of form XDatatypeInformation;
                                        l_composite_datatype = getFieldDatatype(l_composite_name, l_dict, l_composite_datatype_name) of form XFieldInformation;
                                        l_controltype = getDatatypeControlType(l_composite_datatype_name, l_dict) of form XDatatypeInformation;
                                        system SYS_OLFD_SET_DICT, l_dict;
                                        system SYS_OLFD_FIELD_STORAGE_INFO, getResID(l_composite_datatype_name, l_dict) of form XDatatypeInformation, l_controltypename, l_length, l_keyable, l_decimals;

                                        { Write Field Info to File }
                                        increment l_sequence;
                                        increment l_field_count;
                                        l_Text2 = str(l_dict) + l_tab + l_dictname + l_tab + str(TABLETYPE) + l_tab + str(l_resid) + l_tab 
                                                + l_table_techname + l_tab + l_table_physname + l_tab + str(l_sequence) + l_tab
                                                + str(l_field) + l_tab + l_field_name+CH_BRACKET_LEFT+str(y)+CH_BRACKET_RIGHT+CH_COLON+l_composite_name + l_tab + l_field_physname+CH_UNDERSCORE+str(y)+CH_UNDERSCORE+str(z) + l_tab
                                                + l_composite_datatype_name + l_tab + l_controltypename + l_tab + str(l_length) + l_tab + str(l_keyable) + l_crlf;
                                        TextFile_WriteText(l_File_ID2, l_Text2);
                                    end for;
                                end for;
                            else
                                { Array }
                                l_controltype = getDatatypeControlType(l_datatype_name, l_dict) of form XDatatypeInformation;
                                system SYS_OLFD_SET_DICT, l_dict;
                                system SYS_OLFD_FIELD_STORAGE_INFO, getResID(l_datatype_name, l_dict) of form XDatatypeInformation, l_controltypename, l_length, l_keyable, l_decimals;

								if l_controltype = DATATYPE_DROP_DOWN_LIST then
									{get ddl options here}
									try						
										l_ddl_count = getDatatypeCountStaticValues(l_datatype_name, l_dict) of form XDatatypeInformation;
										l_ddl_options = "";
										for d = 1 to l_ddl_count do
											getDatatypeStaticValueAtPosition(l_datatype_name, l_dict, d, l_ddl_option) of form XDatatypeInformation;
											
											l_ddl_options = l_ddl_options + str(d) + "-" + l_ddl_option + ", ";
										end for;
										
										l_ddl_options = substring(l_ddl_options, 1, length(l_ddl_options) - 2);
									else
									{}
									end try;
								end if;

                                for y = 1 to array_count do
                                    { Write Field Info to File }
                                    increment l_sequence;
                                    increment l_field_count;
                                    l_Text2 = str(l_dict) + l_tab + l_dictname + l_tab + str(TABLETYPE) + l_tab + str(l_resid) + l_tab 
                                            + l_table_techname + l_tab + l_table_physname + l_tab + str(l_sequence) + l_tab
                                            + str(l_field) + l_tab + l_field_name+CH_BRACKET_LEFT+str(y)+CH_BRACKET_RIGHT + l_tab + l_field_physname+CH_UNDERSCORE+str(y) + l_tab
                                            + l_datatype_name + l_tab + l_controltypename + l_tab + str(l_length) + l_tab + str(l_keyable);
									
									l_Text2 = l_Text2 + l_tab + l_ddl_options + l_crlf;
									
                                    TextFile_WriteText(l_File_ID2, l_Text2);
                                end for;
                            end if;
                        else
                            if composite_count &gt; 0 then
                                { Composite }
                                for z = 1 to composite_count do
                                    l_composite = getDatatypeStaticValueAtPosition(l_datatype_name, l_dict, z, l_composite_name) of form XDatatypeInformation;
                                    l_composite_datatype = getFieldDatatype(l_composite_name, l_dict, l_composite_datatype_name) of form XFieldInformation;
                                    l_controltype = getDatatypeControlType(l_composite_datatype_name, l_dict) of form XDatatypeInformation;
                                    system SYS_OLFD_SET_DICT, l_dict;
                                    system SYS_OLFD_FIELD_STORAGE_INFO, getResID(l_composite_datatype_name, l_dict) of form XDatatypeInformation, l_controltypename, l_length, l_keyable, l_decimals;

                                    { Write Field Info to File }
                                    increment l_sequence;
                                    increment l_field_count;
                                    l_Text2 = str(l_dict) + l_tab + l_dictname + l_tab + str(TABLETYPE) + l_tab + str(l_resid) + l_tab 
                                            + l_table_techname + l_tab + l_table_physname + l_tab + str(l_sequence) + l_tab
                                            + str(l_field) + l_tab + l_field_name+CH_COLON+l_composite_name + l_tab + l_field_physname+CH_UNDERSCORE+str(z) + l_tab
                                            + l_composite_datatype_name + l_tab + l_controltypename + l_tab + str(l_length) + l_tab + str(l_keyable) + l_crlf;
                                    TextFile_WriteText(l_File_ID2, l_Text2);
                                end for;
                            else
                                { Single Field }
                                l_controltype = getDatatypeControlType(l_datatype_name, l_dict) of form XDatatypeInformation;
                                system SYS_OLFD_SET_DICT, l_dict;
                                system SYS_OLFD_FIELD_STORAGE_INFO, getResID(l_datatype_name, l_dict) of form XDatatypeInformation, l_controltypename, l_length, l_keyable, l_decimals;

								if l_controltype = DATATYPE_DROP_DOWN_LIST then
									{get ddl options here}
									try						
										l_ddl_count = getDatatypeCountStaticValues(l_datatype_name, l_dict) of form XDatatypeInformation;
										l_ddl_options = "";
										for d = 1 to l_ddl_count do
											getDatatypeStaticValueAtPosition(l_datatype_name, l_dict, d, l_ddl_option) of form XDatatypeInformation;
											
											l_ddl_options = l_ddl_options + str(d) + "-" + l_ddl_option + ", ";
										end for;
										
										l_ddl_options = substring(l_ddl_options, 1, length(l_ddl_options) - 2);
									else
									{}
									end try;
								end if;

                                { Write Field Info to File }
                                increment l_sequence;
                                increment l_field_count;
                                l_Text2 = str(l_dict) + l_tab + l_dictname + l_tab + str(TABLETYPE) + l_tab + str(l_resid) + l_tab 
                                        + l_table_techname + l_tab + l_table_physname + l_tab + str(l_sequence) + l_tab
                                        + str(l_field) + l_tab + l_field_name + l_tab + l_field_physname + l_tab
                                        + l_datatype_name + l_tab + l_controltypename + l_tab + str(l_length) + l_tab + str(l_keyable);
										
								l_Text2 = l_Text2 + l_tab + l_ddl_options + l_crlf;
                                TextFile_WriteText(l_File_ID2, l_Text2);
                            end if;
                        end if;
                    end for;

                end if;
            end for;
        end if;
        result = Resource_EndSeriesFill();
    end for;

end for;

{ Close Files }
TextFile_Close(l_File_ID1);
TextFile_Close(l_File_ID2);

warning "Exported " + str(l_table_count) + " Tables and " + str(l_field_count) + " Fields.";
run application "", Path_MakeNative(l_Pathname1);
run application "", Path_MakeNative(l_Pathname2);

Note: The WordPress editor often converts the Greater Than symbol (>) to &gt; and the Less Than symbol (<) to &lt; if this happens the above script will not compile and you will need to perform a find and replace to undo the HTML encoding.

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 (or GP Power Tools) settings file with this code and example TABLES.TXT and FIELDS.TXT files are attached below.

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.
18-Apr-2025: Updated with suggested changes from Adriaan Davel from Mekorma to Add Drop Down List Static values and open the exported text files when complete.
05-Feb-2026: Added note about HTML encoding of Greater Than and Less Than symbols breaking the code.

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on https://www.winthropdc.com/blog.

18 Comments on “Getting Table and Field Data out of Dexterity Dictionaries”

  2. 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.

    Reply

  6. 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?

    Reply

  7. 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

    Reply

  8. 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.

    Reply

  10. 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

    Reply

  13. 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

    Reply

  16. When I try to copy/paste, then save the script, I get an ‘unknonw identifier ‘it’ I did a find for ‘it’ but don’t see what is causing the issue. Any sugguestions?

    Reply

  17. Figured it out

    &lt; should be replaced wtih > and &gt; should be replaced with >.  I don’t see any other symbols that are odd, saved it, no errors

    Reply

    • I have to continuously fix the greater than (>) and less than (<) symbols as the wordpress editor keeps converting them to their html encoded equivalents of &gt; and &lt;. I have fixed it again.

      Reply

Leave a Reply