Want to duplicate an GP Excel Report plus a few fields? Or wondering what these “Data connections” are in SmartList Builder? Or puzzled on where the “Data connection” went in Builders for GP 2010? Wonder no more…
The GP Excel Reports are based off a series of SQL views added to the GP database. The “Default” ones are all the data straight from the view, others are the view with some restrictions added. (Same as the SmartList favorites they are modeled after.)
In GP2010, the “Data Connection” option disappears in the table section in Builders unless there are Data Connections enabled in security. (Same with the SQL table option!)
Next time you want to quickly add the data from an Excel Report (which you know are a lot like the SmartLists)… Try the Data Connection. It might work for you.
PS – for those of you familiar with SQL – check out the design of these views (names are the same as the name for the Data connection) you will learn all sorts of interesting things about the GP data and tables. (All these neat SQL Functions “dbo.DYN_FUNC_XX” like DYN_FUNC_Account_Category_Number)
Belinda, the GP CSI beat me to the press with this post Change the Defaults? about how to change the default columns, column/field order, maximum number of records, etc for basic GP SmartLists. (Microsoft Dynamics GP >> Tools >> Setup >> System >> SmartList Options or Navigation List – Administration >> Setup >> System >> SmartList Options )
Did you know that you can use this same screen for your SmartLists from Builder too? Yes, you CAN have the fields appear in a logical order for your users. (YEAH!!!!)
But, and this is a very big but, you will lose all of your changes on this screen if you make any changes in Builder to this SmartList afterwards. So, document your column order & max records settings (other setting should be done in builder) and re-apply these after making changes in SmartList Builder (and applying/updating them in SmartList).
This should allow you to make much cleaner and more user-friendly SmartLists.
I hate it when I am wrong! I have been posting that there is no way to copy a basic SmartList (one that comes with GP without Builder) into Builder. While that is true…
Someone at Microsoft has been recreating these SmartLists with ALL the tables in Builder. (The templates have been around a while for download but earlier versions weren’t very good.)
SmartList Templates for Microsoft Dynamics GP 10.0
(These require a CustomerSource Logon to download.)
I haven’t looked at all of them, but most seem very good.** Check out all of the tables used on the Receivable Transactions SmartList:
Remember that Advanced GoTo’s don’t import so you will need to add GoTo’s to:
- Payables Transaction Inquiry
- Purchase Order Inquiry
- Receivable Transaction Inquiry
- Sales Inquiry (Sales Order Processing)
Also remember that not all SmartList functionality is reproducible in Builder. For example, on Sales Transactions the default GoTo opens Entry if the transaction is open and Inquiry if the transaction is in History. This is typical for many transaction SmartLists. We can’t reproduce this in Builder (yet?… hint, hint…). We have to build two GoTo’s, one to the entry screen and one to the inquiry screen and pick which to make the default GoTo.
One more thing… The SmartLists from GP change the field names for user defined fields, you will need to do this yourself in Builder.
**Here is a list of issues I see (to be revised as more are identified):
- CUST_ADD – Customer Addresses & VEND_ADD – Vendor Addresses: link to Internet Information table, see my post on this
- ITEM – Items: link to Multiple Manufacture Items Master – this table can contain more than one record per item, so could cause Items to appear multiple times, if the table is used this way