SmartList Designer Search Columns Error - "TableError. GetFromFieldDefinition() of form aaSmartlist "

An infrequent issue has occurred when deploying analytical accounting report using the smart list designer. It occurs in both GP 2013 R2 and GP 2015. It is resolved in GP 2015 R2 which brings good news to the ones who are planning to upgrade. To reproduce the error follow the steps below:

• On the smart list designer, give the view an appropriate name
• Choose specifically the "Analytical Accounting" product 
• Check the associated database view  

 

Smartlist Designer

• As the view is created. Go to Microsoft Dynamics GP > Smart List > Analytical Accounting Folder. Click on the view created in the previous step

Smart List Window - Search Button

• On the smart list window, click on the "Search" button with the ribbon, to open the search window.
• Click on the Lookup icon next to the "column name" field, and an error message will pop up stating that "TableError. GetFromFieldDefinition() of form aaSmartlist .."

Error Message - On Smart List > Search > Column Name

The error message keeps on showing for each field within the smart list, so if you have a 40 field the error will pop up 40 times. Apparently, the error message is related specifically to the Analytical Accounting Smart list, because as I  change the Product field on the smart list designer from "Analytical Accounting" to any other product, the error is gone. 

Error Cause
The cause of the problem is simply creating a view using the Smartlist designer under the "Analytical Accounting" product. 

>>>>>   Important Note !
          This error is associated with GP 2013 R2 and GP 2015. It is resolved with the release of GP  2015 R2.

Solution 
If you are planning to upgrade to GP 2015 R2, then the error will be resolved after the upgrade. Otherwise, you will have to consider one of the following resolutions; one on the application level and the other one on the database level. 

Application Level Solution

This is simply represented with changing the product associated with the smartlist from "Analytical Accounting" to any other one from the predefined list. To do so, follow the steps below:

1. Go to Microsoft Dynamics GP > Smartlist > Click on the Smartlist causing the error.
2. Click on "Modify" to open the Smartlist designer window. 
3. On the product field, choose any other product such as "Microsoft Dynamics GP", then the "Financial" series.
4. Click Ok

Considering this solution will result with changing the folder of the smartlist, so if you choose "Financial" from the product, the customized smart list will be available under the "Financial" folder in the smartlist.

Database Level Solution

Technically speaking, the root-cause of the problem is having no records for the customized smartlist in DYNAMICS.[dbo].[ASITAB20] table. This was clear on the SQL profiler as the system is trying to retrieve records for the default column sequence and type from this table specifically when a smart list is associated with the AA module. 

To get this resolved, run the following query on your DYNAMICS db, to populate the associated records for this smart list as derived from DYNAMICS.[dbo].[ADH00101]

/*--------------------------------------------------------------------------------

Creation Date: 9, June, 2015

Created by: Mahmoud M. AlSaadi

The main purpose of the script is to resolve the smartlist designer error (TableError. GetFromFieldDefinition() of form aaSmartlist ). It inserts records in DYNAMICS.[dbo].[ASITAB20] as derived from DYNAMICS.[dbo].[ADH00101]

The script has been tested on a very limited sample data.

Revision History:

Revision No.            RevisionDate    Description

1                       09/06/2015      Original Version

------------------------------------------------------------------------------- */

----- Fill in the Smart list name below. In my case, the customized Smart list name is "GL WITH AA Details", replace it with the correct name.

DECLARE @SmartListName AS NVARCHAR(MAX)

SET @SmartListName = 'GL with AA Details'

IF NOT EXISTS ( SELECT  ASI_Favorite_Dict_ID ,

                        ASI_Favorite_Type ,

                        ASI_Field_Sequence ,

                        ASI_Field_Number ,

                        ASI_Field_Number_Dict_ID ,

                        ASI_Field_Name ,

                        ASI_Field_Display_Name ,

                        ASI_Field_Physical_Name ,

                        ASI_Field_Use_UsrDef_Prm ,

                        ASI_Field_DDL_Type ,

                        ASI_Field_Datatype ,

                        ASI_Field_Format_String ,

                        ASI_Field_In_Lookup_Tabl ,

                        ASI_Include_Column ,

                        ASI_Display_Column ,

                        ASI_Field_Description ,

                        ASI_Field_Custom_Query ,

                        ASI_Table_Technical_Name ,

                        DEX_ROW_ID

                FROM    DYNAMICS.[dbo].[ASITAB20]

                WHERE   ASI_Favorite_Dict_ID = ( SELECT ASI_Favorite_Dict_ID

                                                 FROM   ASIEXP81

                                                 WHERE  
                                                 ASI_Favorite_Name =  
                                                 @SmartListName

                                               )

                        AND ASI_Favorite_Type = ( SELECT    ASI_Favorite_Type

                                                  FROM      ASIEXP81

                                                  WHERE 
                                                  ASI_Favorite_Name =  
                                                  @SmartListName

                                                ) )

    INSERT  INTO ASITAB20

            SELECT  ASI_Favorite_Dict_ID ,

                    ASI_Favorite_Type ,

                    ASI_Sequence AS ASI_Field_Sequence ,

                    ASI_Sequence + 1 ASI_Field_Number ,

                    ASI_Favorite_Dict_ID AS ASI_Field_Number_Dict_ID ,

                    ADHFieldDisplayName AS ASI_Field_Name ,

                    ADHFieldDisplayName AS ASI_Field_Display_Name ,

                    ADHFieldPhysicalName ASI_Field_Physical_Name ,

                    0 AS ASI_Field_Use_UsrDef_Prm ,

                    1 AS ASI_Field_DDL_Type ,

                    ASI_Field_Datatype AS ASI_Field_Datatype ,

                    1 ASI_Field_Format_String ,

                    0 ASI_Field_In_Lookup_Tabl ,

                    1 AS ASI_Include_Column ,

                    1 AS ASI_Display_Column ,

                    '' AS ASI_Field_Description ,

                    '' AS ASI_Field_Custom_Query ,

                    '' AS ASI_Table_Technical_Name

            FROM    DYNAMICS.[dbo].[ADH00101]

            WHERE   ASI_Favorite_Dict_ID = ( SELECT ASI_Favorite_Dict_ID

                                             FROM   ASIEXP81

                                             WHERE  

                                             ASI_Favorite_Name =  

                                             @SmartListName

                                           )

                    AND ASI_Favorite_Type = ( SELECT    ASI_Favorite_Type

                                              FROM      ASIEXP81

                                              WHERE      

                                              ASI_Favorite_Name =  

                                              @SmartListName

                                            )

The script can be download from here >>> Download Link 

Best Regards,
Mahmoud M. AlSaadi