Building Form GoTo’s for Receivables & Customers

Here are Steps for building the most common GoTo’s for the Receivables module:

Basic Steps (Unless listed differently below):

1. Press the Go To … button on the middle of the top bar in SmartList Builder (Or Open Drilldown Builder.)
2. Enter your Description
3. Product is Microsoft Dynamics GP
4. Series is Sales
5. Select Form listed below
6. Press + on the middle right of the blue bar to add a task
7. Add task(s) as below, press Save on Add Task window
8. Press Save on Add Go To – Open Form window

Specific Windows (Forms):

• Customer Maintenance – “Set a field value and run the field script” on field Customer Number
• Address Maintenance
  
  .
• Receivables Transactions Inquiry Zoom — this is accessible via an Advance Go Tos rather than a Form Go To – use the Receivable Transaction Inquiry (Also will take you to a Cash Receipt Inquiry Zoom for a payment.):   **See note below

 

• Customer Payment Summary Inquiry  – “Set a field value and run the field script” on field Customer Number
• Customer Yearly Summary Inquiry  – “Set a field value and run the field script” on field Customer Number  Will open by default to year of your GP date
   
• Customer Period Summary Inquiry  – “Set a field value and run the field script” on field Customer Number  Will open by default to current period (based upon GP date)
   
• Customer Inquiry – this uses a calculated field called Integer3 (Type integer, in the calculation area enter the number 3) to select the “Bill to” address (Third value in the dropdown)
  
  
• Customer Transaction Inquiry – this Go To loads the customer, but also sorts by date, unchecks to display History (at least for Active customers) and Shows Details (second line for each transaction) See example screen below:
  
  
  .
• Collections Management Main — Note that the Product and Series is different than the basic steps:
  
  .
• Transaction entry (For transactions in Work status)  **See note below
  
• Cash Receipts Entry (For transactions in Work status)  **See note below
  
  .
• Apply Sales Documents (For Open Transactions, Payment, Return or Credit Memo types only)
  
  .
• Edit Transaction (Open only)  (The dropdown for Type doesn’t have #2 Scheduled Payments.  A Calculated field is needed.)
  
• Posted Transaction (Open only)  (The dropdown for Type doesn’t have #2 Scheduled Payments.  A Calculated field is needed. See above.)
  
  .
• Receivable Batches this uses a calculated field called Integer2 (Type integer, in the calculation area enter the number 2) to select the “Cash receipts entry” (Second value in the dropdown) – Use 1 for Receivable Transaction batches
  

** Unlike the SmartLists that come from GP – we are not able to build a Go To that will open the Entry window for transactions in Work status and the Inquiry window for transactions in Open or History status.

Hopefully this helps!   Let me know if I have missed a Receivable window.  Next post, Payables!

Happy Building,
Devon

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

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!

Distribution Tables – adding the GL accounts to Builder Lists

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

Excel Report Builder – GP2010 Pivot Tables – Sales By… anything!

I do a lot of “Sales by” SmartLists for clients for sales analysis.  Sales by Customer; Sales by Salesperson by Month, etc.   You often end up with quite a large list of SmartLists for each different report that different parts of the organization wants.    That is why I am pretty excited about the new Pivot Table option for Excel Report Builder in GP2010.    (Now – don’t stop reading if you are on GP10, I have a trick for you so that you can use Pivot Tables too!… If you are on GP9, sorry…)  

Now – If you haven’t used Pivot Tables in Excel – boy have you been missing a great tool for analyzing data.  They make it so easy in Excel 2007.  If you are a newbie, play with that first to get an idea.  Take a SmartList and dump the data to Excel. Go to a new worksheet, press the Insert tab on the top and on the far left you should see PivotTable – click on that.  A screen will come up asking for your range of data to select – go select your exported data.  and Press OK (See Excel help for more details …)  You will now see this screen:

Now you want to drag your fields into each of the sections – like this:

And by magic – there is your summarized data.  
Want to add customer?  Just drag that field into the proper section  
Want a report for a certain date range?  Drag the Document Date field up to the Report filter and change the dropdown to only select certain dates.
Want each month in columns?  Add month to the column section… and so on.  So easy most people can get the hang of it.

(note – Double click on the field name in Values section to get the screen to select Sum rather than the Count that Excel will typically give you – also you can format your data here)

Now I am going to assume that you already have a Summary Sales by SmartList that you are using in SmartList Builder (See my post – Summary SmartLists in Builder (Sales by Item for a date range, etc)) so I won’t cover those same items here.   Remember items like restrictions, proper signs & quantities.     You can duplicate that SmartList into Excel Report Builder (See my post – Making a copy of a Builder SmartList) … or start a new one. 

The steps for exporting are exactly the same as a regular Excel Builder Report.  But when you launch the Excel you will not see a screen full of data, but instead you will see the first screen shot above.  In the field list you will see all of your fields selected on the Excel Builder for that report. You can build it for your users and save it – or leave it “blank” for users to build as they desire.  (Note – You can close the section on the right if you don’t want users to be tempted to change it – but you will also want to make sure they don’t have “write” privileges to the folder in Windows either!)

One of the feature of PivotTables you will not be able to use is some special date features in Excel ( the ability to select Years & Months for a date field) – but adding a couple calculated fields in Excel Report Builder will take care of this problem: 

• Month – field type integer – MONTH ( {table.field name} )
• Year –  field type integer – YEAR ( {table.field name} )
• Quarter  –  field type integer –  DATEPART (q, {table.field name} )

Before you know it you will have something like this:

There are a lot of tricks to PivotTables in Excel – if you are not familar with them I would suggest looking on the web or Youtube for some training to best make use of your data.

Now – How about those of you on GP10?  

Make your Excel Report in Builder like normal.  (Or use one of the “stock” ones from GP)  Launch your Excel Report. Go to a new worksheet and press the Insert tab on the top and on the far left you should see PivotTable – click on that.  On the screen that comes up change the option to “use an external datasource” and press Choose Connection button

the next screen will show your data connection created by Excel Report Builder, select it and press the Open button on the bottom (not shown)

Press OK back on the Create Pivot Table screen…and you will find yourself looking at the Pivot table wizard with your GP data!  (You could delete the worksheet with the data (one made by Builder) and save this Excel as a different name.  Now it won’t be overwritten by Excel Report Builder.)

Try it – you will start to love PivotTables too…

Happy Building,
Devon

PS – If you are on Excel2003, and you have the download to open Excel2007 files – you can still use the PivotTable features – it’s just that the steps are completely different – if you web search you should find the how to this…

Summary SmartLists in Builder (Sales by Item for a date range, etc)

One of the great features of SmartList builder is the ability to create a summary SmartList. Great for Sales reporting as well as many other uses. Here are some tips & tricks to get your summary SmartLists up and running:

• Build your SmartList like normal.  Add your desired tables and restrictions.  Check your data.
• Some restrictions to think about:
  • Voided records
  • Only include certain Document Types  (In Sales Order processing you typically only want to include SOP Type =3 (Invoice) and SOP Type = 4 (Return) )
• Make sure you have the “proper” signs on numbers you will be adding up… for example you will want Returns or Credits to be negative.   You will need to add calculate fields for these.  See my post Transaction Amounts with correct signs
• If you want Quantities make sure you are using the same Unit of Measure.  See my post Item Quantities, Unit of Measure and Base U of M
• Uncheck in the Display column ALL fields for ALL tables (select the little arrow by Display for the option to Unmark all)
• Now only check in the Display & Default column for fields that you want to:
  • Group by – for each value it will make a new row on your SmartList  (The “by” in your title)
  • Sum – add up values in this field for all records with the same values in the Group by field
  • Other options to consider:
    • Max or Min  – for fields that MIGHT have different values but you DON’T want separate rows
    • Count – great for fields that you want users to be able to enter a search criteria on – but you Don’t want to Group by.  (Don’t check these fields as Display options – the data will actually be a count of records and will confuse your users!)

Let me point that out again with an example.  It is an important “trick”:  
You want to make a SmartList for Sales by item for ANY date range. 
You want one row returned for Each item with total Sales $ and Qty for that date range.   

Group by fields — Item, Item Description
Sum fields — Qty and Ext Price (Calculated fields for sign correction)
Count field — Document Date and/or GL Posting Date 

This trick works because SmartList Builder sends the query into SQL with the restrictions THEN group by’s / sum’s the data.  

blockquote>

• Press the Options Button on the top
• 
  • Check the Summary box
  • Click on the first table on the left
  • Listed on the right you will see the fields you selected in the prior step, double-click on a field to select
  • Press the dropdown to select the correct summary method (Group by, Sum, etc.) for each field and press Save
  • Then continue with each of your tables on the left including the Calculated Fields
• Load your SmartList and check your data.

And when your users ask for a new SmartList for Sales by Customer by Item for a date range, use you the copy feature to copy this SmartList (Options on Top menu bar > Duplicate), check Customer ID & Customer Name fields as Default & Display and change the Summary options to add them a Group By fields and you are done!

Happy Building,
Devon

Transaction Amounts with correct signs (What you want Credit memos to be negative?)

Isn’t it irritating that for most Transactions (all in Payables Management & Receivables Management) are a “positive” number even Credit Memos and Payments?   Don’t you wish you could export GP SmartList to Excel, run the Subtotal feature and get a REAL total?  

Here is how to “fix” this in the Builders (SmartList, Excel & Navigational Lists):

• Determine what field you can use to decide if the value should be negative or positive – your “type field” (Document Type for Payables; RM Document Type-ALL for Receivables; SOP Type for Sales Order Processing) 
• Verify the field data type   (Press the Show Details (down chevrons) on the far left of the field header line, Now you should be seeing the Data Type on the second row under each field name)
  • If the Data Type is not Dropdown list (or Yes/No)  you will need to do a calculated field – scroll down… for Option #2
  • If the Date Type is Dropdown list (or Yes/No), then this field will be available in the next step.  But only if you have set the dropdown field options. See my post Drop Down Lists in SmartList Builder for details
• Select the amount field that you want to correct the sign by clicking on the Display Name of that field
• Press the Blue Arrow between Display Name and Display on the Field header line to launch the Set Field Options window. (This window changes its appearance based upon the data type for the field.)
• Press the Negative values tab
• Select your “type field” from above
• Check Mark the boxes for the type values that should be negative

Note – these values are only used for displaying in your List.  If you want to do a Summary list – these sign corrections will not be used – you will need to do a Calculated Field.

Option #2 – A Calculated Field to correct the sign of a field:

Here we are going to use our handy-dandy Case Statement:  (For more info see Try CASE )

CASE {type field}
WHEN 4 THEN – {amount field}
WHEN 5 THEN – {amount field}
ELSE {amount field}
END

or

CASE
WHEN {type field} >=7 THEN – {amount field}
ELSE {amount field}
END

Uncheck the Default & Display boxes for the original amount field and check the Default & Display boxes for your new Calculated field so that users see this new field instead.

Works like a charm.

Happy building,
Devon

Item Quantities, Unit of Measure and Base U of M

In MS Dynamics GP, items can have several units of measure and any of these units can be used in transactions.  So how do you make sure you are looking at the same units and quantities on transactions and reports?  Especially if you are trying to do a report with totals for a period? (sales, receipts, etc)

Let me introduce you to a very important field that can be seen on many transaction tables –  “Qty in Base U of M”   This field is the number of the base unit in the unit on the transaction.  An example will help:

Item 100XLG   Base unit is Each   Transaction unit is Case    Since, if you look on the Unit of Measure Schedule for this item you will discover there are 10 Each in Case,  the value in the field Qty in Base U of  M is 10.

If for the same item the Transaction unit was Each, then the value in the field Qty in Base U of  M is 1.  (There is one Each in Each)

To come up with the total quantity of the Transaction in the Base unit, you multiply the transaction quantity times the field Qty in Base U of  M. 

Another important point is that if the item changes (such as the item is now packed in a case of 12) this field was stored at the time of the transaction, so – as long as you haven’t changed the Base unit – the data will be accurate.   (Unfortunately, GP doesn’t store the Base unit on the transactions.)

Now, how do you add the actual name for this Base Unit on your report? 

• Add the Item Master table with a link on Item number (use a Left Outer join/link if you want non-stock items to show)
• Add the Inventory U of M Schedule Setup with a link on the U of M Schedule from the Item Master
• Now add the field Base U Of  M

Couple more notes on what quantity fields to use for several typical tranactions:

• Sales Order Processing (Sales Transactions Amounts Work or History)- these tables have lots of quantities and it can be tricky to find the correct one.  Here are my suggestions – these will work no matter how you have SOP setup:
  • Invoice – use Qty to Invoice
  • Return – use Quantity
  • Back Orders & Orders – depends on what you really want – but Qty Remaining may be best is you want what is left to ship (Work – Open orders)
  • Quote – generally use Quantity
• PO Receipts – Use Purchasing Receipt Line Quantities table.  QTY Shipped minus QTY Rejected. (Our field is called U Of M QTY in Base, here) 

  (Note – this is a work & history table, but Purchasing Receipt Line History doesn’t have a Quantity field!)  Link to Purchasing Receipt History table to get just the posted transactions.

For instructions on how to add a calculated field refer to my prior post: Adding a Calculated field with Basic Math

Happy Building,
Devon

Adding a Calculated Field – Basic Math (Margin, Qty Available, etc)

OK,  So you need a field for your SmartList and it isn’t on the table, but you know how to calculate it from fields that are on the table(s).    Before Builder, you would export your list into Excel and add a calculated column, but now that you are using Builder, you want to add the field for your users.   Here is how:

• Press the Calculation button on the top bar to launch the Calculated Field screen.
• Press the + sign on the left side to add a new field (remember the – sign is to delete a field and the paper/pen icon is to change an existing field).  
• Enter a name for your field (this doesn’t need to be unique, but should make sense to users)
• For data type, since we are talking about fields with math you will use either:
  • Integer – just whole numbers, no decimals  (positive or negative)
  • Long Integer – value greater than the max amount on Integer  – just whole numbers, no decimals  (positive or negative)
  • Currency – any number with decimals (0 to 5).  Remember that all quantities in GP are actually Currency type fields (without a $ sign), this is the only way to get decimals.   Also percentage fields are currency fields too. (also can be positive or negative)
• In the lower area add your calculation:
  • To view the fields to add, find the table listing on the right part of the screen.  Press the + in front of the desired table to view the field names. (NOTE – sometimes SLB prints fields multiple times on this list, this is just a display problem and doesn’t appear to cause any issues in your calculated field.)  Double-click on your desired field and it will add it into the window where your cursor is.
  • Now add your desired math function:
    • +   to add
    • –  to subtract
    • *  to multiply
    • /  to divide
    • (  )   as needed to have the math work properly
  • Use the field listing to add additional fields.  All fields you add must be either integers or currency type fields.  (If the aren’t, you will need to convert the data, see a future post on Advanced Math with the CAST function)
• Press the Validate button to check your field  (If you are on GP9 or GP8, this button is not available, see my post Help, my SmartList loaded no data on how to check Calculated Fields in these versions.)
• Press Save
• Back on your main SmartList builder screen, press the + in front of Calculated Fields on the left table section to view your fields on the right side of the screen.
  • Check the box in the Default column to have the fields appear on the Default list (the * favorite)
  • Don’t forget to press the Blue Arrow by Display Name to change the display settings (remove the $, add a % sign or change number of decimals displayed), if needed.

Example:

A typical field you may want to add is to calculate Qty Available on the Item Quantities table, here is how this will look:

Here is Margin on the Sales Transaction Posted table:

Here is Margin % on the Sales Transaction Posted table:

NOTE – you can’t use a Calculated field in a calculated field… you need to use your copy (Ctrl-c) and paste (Ctrl-v) to repeat the formula text from the first field into the desired section on the second field.

One more important point – If you are including fields from a table with the Link Method of Left Outer you will need to make adjustments to your calculated field, or just adding a calculated field will effectively make your Link Method into an Equals…  but that is a subject for a future post… (sorry, I didn’t say it was always easy!)

Happy Building!