Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Identifying where SmartList Column Data comes from

Identifying where SmartList Column Data comes from

Rate This
  • Comments 2

David Meego - Click for blog homepageRecently, I had a support case which asked the following: "When I go to Add the Sales Account Number to the Sales Line Items SmartList, there are three Sales Account Number Columns available. What is the difference between them?"

So, I wanted to work out a method to make it fairly easy for customers and partners to understand where the SmartList Column Data comes from.

Below are the steps to identify the where the data comes from, this method will work for any SmartList, but is based on this example:

  • Open SmartList window and expand Sales and then Sales List Items, click on the default * favorite.
     
  • Create a temporary saved Favorite: Click Favorites, enter a Name "Test" and click Add >> Favorite.
     
  • Go to SQL Server and run the following SQL Query against the ASI_MSTR_Explorer_Favorites (ASIEXP81) table in system database (eg. DYNAMICS) to display the newly added favorite:
select top 1 ASI_Favorite_Dict_ID, ASI_Favorite_Type, ASI_Favorite_Name
from ASIEXP81
order by DEX_ROW_ID DESC
  • Make a note of the ASI_Favorite_Dict_ID and ASI_Favorite_Type values for the most recently saved Favorite (should be the one created in the step above). In our case Dictionary = 0 and Type = 20.
     
  • Select the newly added Favorite, Click Favourites and click Remove.
     
  • Run the following SQL Query against the ASI_Field_Definition (ASITAB20) table in system database (eg. DYNAMICS) using the Dictionary, Type and Sequence Numbers from the Columns window (see screenshot above):
select ASI_Field_Sequence, ASI_Field_Number, RTRIM(ASI_Field_Name) AS ASI_Field_Name,
       RTRIM(ASI_Field_Display_Name) AS ASI_Field_Display_Name, RTRIM(ASI_Table_Technical_Name) AS ASI_Table_Technical_Name
from ASITAB20 -- ASI_Field_Definition table
where ASI_Favorite_Dict_ID = 0 -- Dictionary from temporarily saved Favorite
and ASI_Favorite_Type = 20 -- Type from temporarily saved Favorite
and ASI_Field_Sequence in (83, 114,394) -- Sequence Numbers listed in Add Columns window

  • View the results to see the ASI_Field_Name and ASI_Table_Technical_Name to see where the data is coming from:

ASI_Field_Sequence      ASI_Field_Number  ASI_Field_Name          ASI_Field_Display_Name       ASI_Table_Technical_Name
83                      24073             Sales Index             28277                        ASI_SOP_LINE_Items_Explorer
114                     23118             IV Sales Index          28229                        IV_Item_MSTR
394                     22959             RM Sales Account Index  28213                        RM_Customer_MSTR

Note: The ASI_SOP_LINE_Items_Explorer table combines the data from the SOP_LINE_WORK (SOP10200) and SOP_LINE_HIST(SOP30300) tables.

The ASI_Field_Number and ASI_Field_Display_Name (when shown as a number) are messages in the SmartList dictionary which contain the technical name of the field and the display name of the field respectively. In our case the technical names are the same as the ASI_Field_Name and Display Name for all three fields in the same "Sales Account Number".

While it is possible to use Modifier to change the message IDs from the ASI_Field_Display_Name column so they are different, it is probably easiest to relabel the columns once they have been added to the SmartList Favorite on the Change Column Display window.

Or, you might find that you only need the field from one table and can leave the other fields off the SmartList.

Hope you find this useful.

David

  • Hi David -- Great SQL Solution!  When I get this question from an end user, we walk through sorting the column list by Sequence.  Since SmartList columns tend to be added table by table, I can usually identify the table source by looking at the other columns in sequence.  In your example, I come up with the following data source:  (1) Sales Line Item, (2) Item Master, (3) Customer Master.  This method requires understanding of the tables and isn't perfect, but it is a quick way to help a user who doesn't have access to SQL.   Your solution would be the exact 100% correct answer every time; mine just gets to an educated guess.

  • Posting from Mark Polino at DynamicAccounting.net

    mpolino.com/.../identifying-smartlist-column-data-comes-developing-dynamics-gp

Page 1 of 1 (2 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post