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)
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
This is so easy – you are going to say “DUH” but people seem to constantly be searching for how to copy one SmartList in Builder to another. This is exactly the same in Excel Report Builder. So here it is:
Pull up the one you want to copy-
in GP2010 you will see Navigation List too: