Recently, 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:
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):
- View the results to see the ASI_Field_Name and ASI_Table_Technical_Name to see where the data is coming from:
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.