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