Want to duplicate an GP Excel Report plus a few fields?

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

The programmers for GP Builders added these views as “Data Connection” in the table section for the Builders.  So you can use this data in anything you build. 

They have their own security (similar to the SQL tables/views) under MS Dynamics GP > Tools > SmartList Builders > Security > Data Connection Security.  

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.

Happy Building,
Devon

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)

Making a new Navigation List in Builder for GP 2010

People have been searching for and asking how to use the new Navigation List builder in GP2010. So here are some basic steps for building a Navigation List:

1. You will find the Navigation List Builder under SmartList Builder (Microsoft Dynamics GP >> Tools >>SmartList Builder >> Navigation List Builder)
2. Add your tables, restrictions & calculated fields as normal.  Set the Field Display options and field names.  (Remember you can use the Copy/Duplicate option from an existing SmartList for this.  See my posts: Making a copy of a Builder SmartList and Copy a basic SmartList from GP into SmartList Builder? )
3. On the upper right, select what Navigation Pane you want this list to appear on.
4. Check Display fields - the columns on the main list (be sparing with these – but you can add more than will show on the screen – remember that users can unselect fields so they don’t show.)
5. Select Preview fields – the bottom section on the List.  Press the Preview icon on top to organize these fields on the Preview section.
   
6. If you want a Status image     for your records – press Options icon on the top to select the source field (only fields with data types of Dropdown List or Yes/No) and image for each value.
   
7. Now you want to add the items to appear on the top dark blue bar of your list:
    
   You do this via the Actions icon on the top
   • Actions are organized into the following Groups:  Modify, New, Reports, Go To, & Actions (similar to the groups in the screen-shot above)
   • Select Large or Small Icons (the image shown will be the stock image for the Group selected)
   • Select Button priority (Primary, Secondary, Force Overflow) – what to show when displaying GP with small screen sizes
   • Several types of Actions are available:
     • Defined Action – typically items found on stock Lists for Modify and special Groups.  These will be the subject of a follow-up post. Posted on 11/5/10:  Navigation List Builder – Let’s add some Actions!
     • Execute Procedure – under Product of Microsoft Dynamics GP there are 5 to open GP windows – SOP Inquiry, RM Sales Inquiry, RM Cash Inquiry, PM Transaction Entry & PM Manual Payments 
     • Items you are used to from SmartList Builder Goto’s : Open Form, Open File & Open Web Page
8. Save your List.  You will find it at the after the rest of the Lists on the selected Navigation Pane

Try building a new Navigation List today, I think you will find these Lists very useful!

Happy Building,
Devon

Changing SmartList Column order, number of records, etc.

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.

Happy Building,
Devon

Copy a basic SmartList from GP into SmartList Builder?

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.

Happy Building,
Devon

**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

Security for SmartList Builder and the other Builders

I always forget how to add a task to give a user security to access the SmartList Builder module in GP 10, so to help us all – bookmark the following post: Creating a SmartList Builder Security Task by Ralph W. on GP Users Edge.

In the first part he does a great job showing what you need to do to add this task. 

Then he goes on to:  

• “Assignment of Security Task to Security Role”  – replace their Role IDs with yours
• “Granting Access to the New SmartList Object” – generally you will want to add each new SmartList to the appropriate Task IDs.   He makes one Task with access to all SmartLists, but this may work for you, too!

For GP 2010, You will find additional items to check (not shown on these screenshots)  but the steps are the same.

To add access to a new list from Navigation List Builder you will find your new lists under the following:

Happy Building!
Devon

Import / Export in the Builders

The XML export and import in SmartList Builder has existed since at least GP8 but there were lots of pieces that didn’t export (and so were missing in the imported version). In a discussion with MS support they confirm that there are various documented issues in older GP versions. In addition, Outlook didn’t like to email the XML files. So, I just got into the habit of building SmartLists on the client’s server.

Since it has been over 5 years, I figured it was time to look at the Import & Export features again. (Especially since Outlook will now email XML files without issue.)

Testing on GP 10 SP 4+  (10.00.1411), I created a SmartList with all of the various features including items that didn’t work in earlier versions (field formatting, negative values, Go To’s etc.). I exported, deleted the original and imported the XML file.

I was pleasantly surprised. I found that everything came thru EXCEPT Advanced Go To’s – which are easy to re-add after importing.  (Let me know if you are aware of any other issues!)  I will certainly be using the Import/Export in the future.  

One note – make sure you are NOT importing into a GP install that has the same List ID (for that type) it will try to overwrite the existing List without warning. (And I have found that this overwriting has unpredictable results). My trick is to save only one List in each file and name the file the List ID.

Here is how to export:
Microsoft Dynamics GP >> Tools >> SmartList Builder >> Export

You can check more than one list and various types.
Type or use the Yellow folder to navigate to your desired path.
Make sure your file has a .xml extension.
Press the Export button on the top Left.

To import:
Microsoft Dynamics GP >> Tools >> SmartList Builder >> Import

Type your file name or use the Yellow folder to navigate to your desired file.
Press the Import button on the top Left.

Be patient, it will take a little time to import your List (generally about 1 minute each or more). You will not be able to use GP until it is done importing. (If you try – GP will show as “Not Responding” and the import screen will stop updating. Don’t panic – it is still importing – just wait.)

Happy Building,
Devon

PS – Thanks for all the congratulations… my son is doing great.