Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Tuesday, June 9, 2015

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

1 comment:

  1. Hi,

    In GP 2016 R2, one of the company does not showing AA open transaction smart list records. Please guide for the same.

    Thanks,
    Tanuja

    ReplyDelete