Identifying where SmartList Column Data comes from

  • Article

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