Our Use Case — A Stock On Hand Report

We need a basic inventory report in order to identify how much stock we have on hand for any given item, how much is ‘Committed Stock’ and how much we have recently sold.

For our purposes, we’ll use the stock inflow table (for ‘On Hand’), the Invoices and Invoice Items tables (for amount sold), and assume that Sales Orders are ‘Commited Stock’.

So, we need a way to aggregate up inventory coming in and out and group it by items and date.

Fields We Need (and the Tables They Come From):

• Item Name (Items)
• SKU (Items)
• Item ID (Items)
• Quantity (Invoices Items and Sales Order Items)
• Stock in Hand (Stock In Flow Table)
• Status (Sales Orders)

Identify the various join fields:

• Product ID and Item ID, from the Stock In Flow and Items tables
•  Product ID and Item ID, from the Sales Order Items and Items tables
• Sales Order ID and Sales Order ID from the SO and SO Items tables