Archive

Archive for the ‘General – Builders’ Category

Make a GREAT list – checklist for Field Formating (common things to check)

November 30, 2010 1 comment

I started this post 4 months ago, but never finished it.  In doing some Builder research for another post I stumbled across a blog from another SmartList Lover, Sara Corbett.  Her post SmartList Builder Etiquette is in the same vein as this post, but, interestingly, she lists different items than I do.  Compare the two posts and comment on what you think is important for a Great list…

What is the difference between a good SmartList and a great SmartList?  In my opinion, it is often in the details – mostly the Formating details. Here is a handy checklist of common things to check:

  • Field formating — press the blue arrow in the header left of Display Name to launch the field settings:
    • Note Index fields – check the box to pull the Note text
    • All Account Index fields – check the box to pull the Account Number
    • Phone & Fax numbers – select the dropdown for the Phone number formating
    • Social Security numbers – select the dropdown for Social Security formating
    • Date fields – check the box to display 1/1/1900 as a blank  (also can change the date formating, too)
    • Fields from SQL Views – make sure you turn off the $ on quantity fields and add % for percentage fields
    • Set Decimal places – especially for fields stored in GP as integers but displayed with decimals in GP (like the Shipping Weight on the Item master is stored as 100 where it is really 1.00, HR module stores most hour totals this way.)
  • Field Display Names – Add field names from the setup for user defined fields. On most Maintenance (“Card”) tables (yes, you have to type these EVERY time you add these tables, sorry!)   Look for “User Defined”
  • Dropdown Fields – make sure you enter values – See Drop Down Lists in SmartList Builder
  • Look for fields that will appear more than one on the final list, especially fields that will have different data from the various tables.  Consider turning off the Display checkbox, or at least, changing the Display name for some fields.

Also don’t forget to add useful Go To’s!

Happy Building!
Devon

How to get data in separate columns rather than rows in SmartList Builders (Aging, Sales by Months, etc)

November 1, 2010 2 comments

This has been on my list of things to blog about – but when I saw this post in the Forums, I knew its time had come:

I have a client who is attempting to create a Sales report which shows sales by Customer.  They want the report to include a separate column for each month – based on Document Date.  I have attempted to create a calculation for each month (If/Then) but either don’t have the syntax correct or it is truly not going to work.

CASE
WHEN {{Sales Transaction History:Document Date} >1/1/2017…
 
Any ideas?????

This is something that seems to have become a very popular request in the last few years and I have always coded it in SQL. Since it requires a ‘group by’ in SQL code, I can’t imagine being able to do this in SmartList Builder. I would recommend coding this in a SQL view and basing the SmartList on that view.

I would differ :-) … This is easily accomplished in SmartList Builder:

First – Add a calculated field for each column of data 

  • In this scenario, one for each month – use the Month function to test:
    for January:
    CASE WHEN MONTH({Sales Transaction History:Document Date})=1 then {Sales Transaction History:Originating Document Amount} ELSE 0 END
    and so on…
  • For a list with aging in columns (inventory, AR or AP) 
    for example – the calculation for the “31-60 days” bucket for AR by Document Date:
    CASE WHEN
    DATEADD ( d , -30, GETDATE() ) > {RM Open File:Document Date}  AND
    DATEADD ( d , -60, GETDATE() ) <= {RM Open File:Document Date}
    THEN
    (CASE WHEN {RM Open File:RM Document Type-All} > 6
    THEN -{RM Open File:Current Trx Amount}
    ELSE
    {RM Open File:Current Trx Amount}
    END)
    ELSE 0 END
  • For other types of data – like quantities on hand – with Sites in columns, here is the calculation for the North site column:
    CASE WHEN {Item Quantity Master:Location Code}=’NORTH’ then {Item Quantity Master:QTY On Hand}
    ELSE 0 END

Next, unselect as Display any fields you don’t want to group by (or sum).  

In the scenario above we would want Year to be a group by field (or we will get all sales in January in history!).  So add a calculated field for year (Users can do a search filter on this field when running the list):

YEAR({Sales Transaction History:Document Date})

Now use the Options icon on the top to make this into a Summary SmartList – the way to “group by” in Builders.   Select all of your column calculated fields as Summary type “Sum” , your other fields as “Group by” and, in the words of my daughter, “There you go!”  

Learn more about summary SmartLists in my post: Summary SmartLists in Builder (Sales by Item for a date range, etc)

Happy Building!
Devon

PS – My apologies and thanks to the ladies on the original forum post!

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

October 29, 2010 1 comment

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)

Changing SmartList Column order, number of records, etc.

October 18, 2010 Leave a comment

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?

October 15, 2010 1 comment

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

October 7, 2010 1 comment

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

October 6, 2010 2 comments

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.

Matched Tables – Viewing Open & History Documents on the same SmartList in Builder (just like the default Lists!)

August 9, 2010 1 comment

One of the features of the default Lists in SmartList, Excel Reports & Navigation Lists is that you can often see ALL GP transactions for a certain type on one list – Work (unposted), Open (unpaid or not moved to history), and History (moved to history).   Now – don’t get me wrong – sometimes this feature is great, but a lot of my Builder Lists are to isolate just one status of GP transactions.  (For example, most of my clients that use Sales Order Processing have separate Open & History Lists from builder…)

What if you DO want more than one status on a Builder List?    On first glance, it seems difficult or impossible – that data is in separate tables and you can’t just start with the open table and add the work table (there wouldn’t be matching records – so you won’t get anything!)  Don’t worry – there is another way.

For example, you want to build a SmartList for payables transactions for a certain period of time and all of the distributions of these documents.  You really don’t care if the documents are paid or not (How they move to History in PM), you still want the documents to show on your list.

Matched Tables

  • First Start like normal and add your Transaction table for one status (I like to use open, but you could use history – it doesn’t matter).
  • On the Add Table screen – look for the Button “Matched Tables” on the bottom and press this button.

 
On additional tables (second or more) you will have to save the table and then edit it (paper and pen icon) to see the Matched Tables button:

  • On the Matched tables screen, Hit the plus sign on the left section to add your equivalent table for the other desired GP status (You can add more than one, if desired.)

You will see that on the right, Builder has already matched equivalent fields (If a field isn’t matched, it won’t show in the final list).  You can review and change if desired.

  • Press OK and Save back on the Add Table screen

Now you are done!   It is that easy.  

What is does is the equivalent of a SQL UNION ALL query.  It selects all of the data from the first table and then, as part of the same set of data goes and select all of the data from the second or Matched table.

Happy Building,
Devon

Adding Internet Information (Email addresses) to Lists in SmartList Builder, etc

August 6, 2010 1 comment

There are certain tables that you really should not link directly to in the Builders.  Internet Addresses (MS Dynamics > Company > Internet Addresses   In SQL:  SY01200 )  is one such table. 

Now, until recently this wasn’t a big deal since not many users took advantage of this table. BUT now in GP2010 this table is storing the Email addresses for the new Email functionality for Purchase orders, Invoices, etc.  Since this data is NOT shown on any stock SmartList or Navigation List, there is going to be a HUGE demand to view this data in the Builders.  

Below I will give you some SQL views to use to add this data to your Lists, but first let me explain why you shouldn’t link directly to this table.  It is a combination of a few factors:

  • First this table stores ALL of the Internet information for Customers, Vendors, Items, etc. 
  • When you link in the Builders there is no way to just select part of the data, just Customers for example.   (You could in SQL)  (Note to the MS programmers – being able to add a constant to the link section would help with these issues! :-) )
  • Not all Master records (Customer for example) will have records in the Internet Addresses table – so you will normally want to do a Left Outer link to this table (so that you can get all Customers and Internet addresses, if there are any.)
  • Adding a restriction to Builder to filter on the Master Type for the Internet Addresses table will not work for two reasons:
    • Adding a restriction directly to table with a Left Outer link will remove all records that aren’t in this table – EG any records without Internet Addresses  (basically turning it into an equal link)
    • Also, the restriction will apply AFTER the join and may remove legitimate Master records. 

I think an example will highlight the problem:

  • You are building a customer list
  • You have a customer ABC001  and a vendor ABC001  – only the Vendor has internet addresses
  • when you link the table you will get the vendors internet data in the customer record
  • adding a filter to the type will then completely remove this record from the list
  • I agree that this may be rare – but I like to build lists that will ALWAYS work correctly.

    OK – so here are the SQL views – (NOTE – I am having a font problem on this blog and can’t get the ‘&’ to work correctly – after you cut & paste these you will need to retype the ‘and’ in the Where clause in SQL – sorry!):

    First you will need to replace the constant in the Where clause with the correct code as well as change the name of the view on the first line:

    • CUS – Customer
    • VEN – Vendor
    • ITM – Item    (only one key field – Address ID is not used, obviously)
    • EMP – Employee
    • SLP – Salesperson
    • CMP – Company

    Basic SQL view:

    CREATE VIEW CustomerInternet AS
    SELECT     SY01200.*
    FROM         SY01200
    WHERE     (Master_Type = ‘CUS’)

    View in GP10 (or earlier) with nice field names (remember that these are changeable in the company set-up so you will want to modify to match yours, if different):

    CREATE VIEW CustomerInternet AS
    SELECT     Master_ID AS [Master ID], ADRSCODE AS [Address ID], INET1 AS Email, INET2 AS [Home Page], INET3 AS [FTP Site], INET4 AS Image, INET5 AS Login, INET6 AS Password, INET7 AS [User Defined 1], INET8 AS [User Defined 2], INETINFO AS [Additional Information]
    FROM         SY01200
    WHERE     (Master_Type = ‘CUS’)

    View for GP2010 with the new fields and nice field names (remember that these are changeable in the company set-up so you will want to modify to match yours, if different):

    CREATE VIEW CustomerInternet AS
    SELECT     Master_ID AS [Master ID], ADRSCODE AS [Address ID], EmailToAddress AS [Email To Address], EmailCcAddress AS [Email CC Address], EmailBccAddress AS [Email BCC Address], INET1 AS Email, INET2 AS [Home Page], INET3 AS [FTP Site], INET4 AS Image, INET5 AS Login, INET6 AS Password, INET7 AS [User Defined 1], INET8 AS [User Defined 2], Messenger_Address AS [Messenger Address], INETINFO AS [Additional Information]
    FROM         SY01200
    WHERE     (Master_Type = ‘CUS’)

    Don’t forget to run the grant script when you are done:

    GRANT SELECT ON CustomerInternet TO DYNGRP

    You last step is to go to Security and give access to your new view (MS Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security)

    One last hint – this data is NOT stored per customer or vendor, but stored by ADDRESS ID on the customer or vendor.  So you will need to use both the Master ID field and the Address ID field as your link fields.

    Happy Building!
    Devon

    Distribution Tables – adding the GL accounts to Builder Lists

    August 5, 2010 1 comment

    As Part two of my response to a question on my Suggestions Page, I thought I would discuss the Distributions tables from GP transactions.

    Couple points to remember when thinking about adding Distribution tables to your lists in Builder:

    • Transaction Distribution tables are different then the actual Journal Entries stored in the Financial Tables. 
      • Distribution tables store data per transaction and do not contain JE numbers. 
      • Depending on the posting setting in GP the JE in Financial tables could be for one or many transactions. (Or could not make a JE at all.)  If your posting setting are per transaction, your could try to link the Financial tables – but this is not a good link via SmartList Builder – most of the time you will need to do a SQL view. (Issue with key fields.. whole different post…)
    • There will be at least two entries for each Transaction and may have many more.   IE – Taxes, Misc, COGS, etc —  all of the entries on the Distribution Button on the transaction. (Important point – since this will cause transactions to be listed several times on your final list.)

    • There is a Distribution type field that can be used to limit what parts of the entry are shown on your list. (For example, you may not want to have the Accounts Payable accounts show on a Payables transaction report.)  This is a Dropdown type field – unfortunately Builder generally doesn’t know the values for these by default – but typically they are in order on the dropdown on the transaction entry screen.  See my post: Drop Down Lists in SmartList Builder (Document Type on Transactions, etc) for more info on this type of field.

    • Most Distribution tables store records for various GP status (WORK & OPEN)  or (WORK & HIST).   Avoid the tables with the word TEMP in them – they don’t have what you want!   Some common tables:
      • PM Distribution WORK OPEN File  / PM Distribution History File
      • RM Distribution Work File (Don’t be confused by the name – this has Open too!) / Receivables GL Distribution History File
      • Sales Distribution Work and History
    • Generally, you will link to the Transaction table with the key fields from the Transaction table – often the GP assigned unique transaction ID (Voucher number, SOP Number, or Document Number) and the Document Type field. To find these – see my post: Adding additional tables to your SmartList (Successfully)

    Good luck & Happy Building!
    Devon

    Follow

    Get every new post delivered to your Inbox.

    Join 25 other followers