Dexterity: Setting a Range on a Composite Field in Dynamics GP

This week, I faced a Dexterity customization for Dynamics GP in which I needed to set a range on the GL_Account_MSTR table where I was filtering only on the third segment of the Account Number field. I had never done a range on an individual component of a Composite field, and my first attempt failed to yield the desired results. Let’s take a look at that attempt and then I’ll share the method I found successful.

My requirements stated the user can select from a drop-list containing the values of the third segment from the chart of accounts. The value selected is then used to filter the Account Master table by the third segment and do additional work on only those accounts. This is where I need to set a range on a single component of the Account Number composite field.

The GL_Account_MSTR table’s sixth index contains only the Account Number field. “Perfect” I think, I’ll use that. I proceeded to write the following code:

in string in_SeedSegment;

range clear table GL_Account_MSTR;
clear table GL_Account_MSTR;

component(3) of field ‘Account Number’ of table GL_Account_MSTR = in_SeedSegment;
range start table GL_Account_MSTR by number 6;

fill table GL_Account_MSTR;
component(3) of field ‘Account Number’ of table GL_Account_MSTR = in_SeedSegment;
range end table GL_Account_MSTR by number 6;

get first table GL_Account_MSTR by number 6;
while err() = OKAY do
                 // typical loop logic and other things

end while;

That was a failure. My while loop simply iterated through all records in the table.

My next attempt worked, luckily, when I realized the range where statement in Dexterity takes a string which is a SQL Where clause. I have always used the physicalname() function with this statement, and for a moment I paused to consider this avenue was not going to get me anywhere. That is when thinking about it from a SQL perspective gave me the solution. I simply used the name of the column as I would in T-SQL and skipped the use of physicalname(). This is my final solution:

in string in_SeedSegment;

range clear table GL_Account_MSTR;
clear table GL_Account_MSTR;

range table GL_Account_MSTR where “ ACTNUMBR_3 = ‘” + in_SeedSegment + “’”;

get first table GL_Account_MSTR;
while err() = OKAY do
                  // typical loop logic and other things

end while;

This range where statement worked perfectly. If you need to set a range on a single piece of a Composite field or you find yourself better suited to think about how to filter a table from a T-SQL perspective, remember you can feed your range where statement a T-SQL syntax.

You can find details on the range where statement, and the physicalname() and component() functions in the Microsoft Dexterity Help document in your software package.

To learn more about how you can take advantage of this and other Dynamics GP features, visit RSM’s Microsoft Dynamics GP resource. To make sure you stay up to date with the Microsoft Dynamics Community, subscribe to our Microsoft Dynamics Community Newsletter.

For more information on Microsoft Dynamics 365, contact us.

By: Chad Bruels

Chad Bruels