In a post last week I linked to Frank Hamelly’s post on using all four User Defined Fields on the Account Maintenance window ().
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;
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
What are the steps to perform this action in Microsoft Dynamics Great Plains 2010? I am not finding the same path to ‘Security’ as stated in this document. Under my ‘Tools’ I do not have ‘SmartListBuilder’.
Hi Cleavon,
SmartList Builder is not a standard feature of Microsoft Dynamics GP but is available as an à la carte module.
When you purchase the module you get SmartList Builder, Excel Report Builder and Navigation List Builder.
Ian
Hello Ian,
Thanks for the reply.
I am told SmartList Builder has been purchased. The customer is using it. I am not able to allow the user to see a user defined column using the instructions in the document. I was wondering if the document is version dependant.
I am user Microsoft Dynamics 2010. I see the following options:
(Microsoft Dynamics GP >> Tools >> Setup >> System >> SmartList Options) .
Hi Cleavon,
If you’ve bought the module three things;
1) Have you input the new licence key which includes SmartList Builder?
2) Have you run GP Setup.exe and installed the new module on each client and run GP Utilities on one of them?
3) Have you added the SmartList Builder security tasks to a role (this last won’t matter if you’re logged in as sa, dynsa or a user with the Poweruser role.
Ian