Conditional summary of child records

App Development
1

Hello community,

I’m trying to find a way to summarize multiple requests into a supplier order.

I have 3 tables:

  • Inventory (Parent)
  • Orders (Parent)
  • Requests (Child to Inventory and Orders)

When users need an Inventory item, they create a new Requests record with the Inventory item and Quantity needed.

Requests records have the following fields:

  • OrderID (Connection to the Orders table - blank until assigned to an order)
  • InventoryItem (connection to the inventory table)
  • QtyRequested (Quantity requested - number field)
  • Status (Pending / Ordered)

The user can easily add (connect) Requests to Orders (using pipes and JS - Thanks @Chem !).

My problem is that there can be multiple Request records with the same InventoryItem, so I need to summarize the total QtyRequested for every InventoryItem in a table component that shows records from an Order ID:

Order 1
5 | Inventory item 1
2 | Inventory item 2
3 | Inventory item 1

Becomes:

Order 1
8 | Inventory item 1
2 | Inventory item 2

This summary would also be used to enter quantities received for each inventory item.

I’m not sure of the best structure to handle this.

Any ideas?

Thanks!