Archive for the ‘Inventory’ Category

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.

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:
    DATEADD ( d , -30, GETDATE() ) > {RM Open File:Document Date}  AND
    DATEADD ( d , -60, GETDATE() ) <= {RM Open File:Document Date}
    (CASE WHEN {RM Open File:RM Document Type-All} > 6
    THEN -{RM Open File:Current Trx Amount}
    {RM Open File:Current Trx Amount}
    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!

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


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,

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.


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!

Inventory – Adding Item Quantities & Sites

A popular table to add to Builders in GP is the MS Dynamics > Inventory >Item Quantities table (AKA Site table) (SQL – IV00102).  Great for adding On-Hand, On Order, Allocated and other quantities and assigned Sites.  But this table will cause trouble with your List unless you understand and adapt to its structure.  Just this week, I had both a client and a co-worker say “Help! My records are doubling” after they added this table to their SmartLists in Builder.

How would that happen?  For a simple company with only one Site (which is Location Code, by the way, on this table) there will be TWO records in this table for each Item.   Why?  Well, one of the records is a Summary Record, the other is a Site Record.  And this fits with how you see this data in GP.   Let me explain further.  If you go to the window in GP where this data is maintained:  Inventory >> Cards >> Quantities/Sites, you will see  the following:

Notice how the radio button (circled in red) changes the screen from viewing the summary “All Sites” record to the record for a specific site.

If you open the SmartList (from GP) > Item Quantities, you will see each of these records:

So how do we only have one record for each item (or at least one for each Site assigned) on our List if we use this table?  Well, if you look at this same data in SQL (IV00102) you will notice the field Record type (SQL – RCRDTYPE):

See how the record without a Site (field LOCNCODE is blank) has the value 1 in this field  – This is the summary/overall record.  While the record with a Site has a value of 2 in the Record Type field?  We can use this to add a Restriction to our List.

Press the Restrictions button on the top bar.

Press the + to add a new Restriction.


  • Table: Item Quantities Master
  • Field: Record Type
  • Is Equal To
  • 2
  • Press Save to add this Restriction
  • Load your changes and TEST! 


Adding this Restriction will only pull records from this table for the Sites, not the summary/overall record, making so that this List will no longer pull “Duplicate records”.

Now, your next question may be “Where is the Quantity Available on this table?”  Well, it isn’t there. (Sorry! GP calculates this field on the “fly” on windows & reports.)    But do not fear, that will be the subject for a future post: “Adding a Simple Calculated Field to your SmartList”.

Happy building!

(Note – since we can now build Excel Reports (and soon Navigation Lists) in addition to SmartLists – I am using the generic term List for what we are building.)