How To Use All Account User Defined Fields On SmartLists – Step By Step

Microsoft Dynamics GPIn a post last week I linked to Frank Hamelly’s post on using all four User Defined Fields on the Account Maintenance window (Cards ¯ Financial ¯ Account).

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 (Microsoft Dynamics GP ¯ Tools ¯ SmartList Builder ¯ Security ¯ SQL Table Security);

SmartList Builder - Create Historical Transactions SmartList

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 (Microsoft Dynamics GP menu ¯ Tools ¯ SmartList Builder ¯ SmartList Builder) and create the new SmartList, Historical Transactions;

SmartList Builder - Create Using View

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;

SmartList Builder - Add SQL Table

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.

SmartList Builder - Configure 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;

SmartList Builder has detected changes to be made. Do you want to make these changes now?

Once this has been done the SmartList can be viewed with all four of the User Defined Fields populated;

SmartList - Historical Transactions

What should we write about next?

Your Name (required) -
Your Email (required) -

(Visited 1,165 times, 1 visits today)

6 thoughts on “How To Use All Account User Defined Fields On SmartLists – Step By Step

  1. Cleavon says:

    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’.

  2. Ian Grieve Ian Grieve says:

    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

  3. Cleavon says:

    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) .

  4. Ian Grieve Ian Grieve says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *