I thought it might be useful to follow up with a step-by-step explanation of creating a SmartList for historical GL transactions with all four user defined fields from the account.
The first step is to create the SQL view we’ll use to get the data; click here for SQL View.
As mentioned by Frank, User Defined Field 3 and 4 don’t have logical names; they’re named USRDEFS1 and USRDEFS2 and are highlighted in the SQL View above.
This particular view selects data from the Account Transaction History (GL30000) table and links to Account Master (GL00100) and Account Index Master (GL00105) to get the relevant information from the account.
Next we need to grant access to the SQL view via SQL Table Security ();
Select the required database, change the display to Views and select the required views; in this case the SmartList_Journal_History view we created above.
Once done we can launch SmartList Builder () and create the new SmartList, Historical Transactions;
As you can see in the screenshot, above, when we click the Add Table button there are two options; Microsoft Dynamics GP Table and SQL Server Table. Selecting the second option launches thr Add SQL table window;
Change the display to Views and all available SQL Views are displayed in the list. Highlight the required View, click Use Company Database and then select the key fields.
Click Save, and then select the Visible and Default fields. As this example uses a custom SQL view I have selected all fields as both Visible and Default.
Save the SmartList away and open SmartList itself so we can check the report output. After changes have been made in SmartList Builder, launching SmartList will prompt for these changes from SmartList Builder to be incorporated into SmartList;
Once this has been done the SmartList can be viewed with all four of the User Defined Fields populated;