Aggregation and Relationship of 2 tables. Possible?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Originally posted:
I have 2 tables -- Orders and Inventory histories -- and I want to be able to track inventory availability by SKU.  Orders table has all line items for 7 days -- order id, order date, sku, quantity.  Inventory history (distributed across warehouses) -- snapshot date, warehouse id, SKU, condition (sellable, damaged, etc), and quantity.  Note that I will have snapshots for 7 days.  The idea is to have a tabular report that has a row per SKU, and 14 sortable columns, showing aggregated sold quantity per day and sellable inventory aggregated across warehouses for that day. Is it possible?  Any recommendations on how to get it done? Thanks!

1 comment

Just to clarify: Orders table -- order id (text), order date (date), sku_id (text), order_quantity (numeric). Inventory history -- snapshot date (date), warehouse id (text), sku_id(text), item condition (text -- sellable, damaged), and quantity_at_warehouse(numeric). The goal is to have a report that would show: sku_id, date, order_quantity (aggregated across all orders for that date), quantity_at_warehouse (aggregated across all warehouses, in sellable condition only for that date). Technically, I can also create a table of products, that would have sku_id as a logical key, if it helps.

Photo of Dan G. (QB Support)

Dan G. (QB Support), Official Rep

  • 490 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Dan G. (QB Support)

Dan G. (QB Support), Official Rep

  • 490 Points 250 badge 2x thumb
Answer

>Possible?
> Is it possible? 

Yes - everything is possible with QuickBase. In over 15 years I have never encountered a well stated problem that could not be solved with QuickBase (and script if necessary).

Unfortunately I can't understand your description of tables and fields. Could you explicitly list (1) the fields you currently have under each table, (2) their type, and (3) relationship to each other? Also, sample data and a specimen of your desired report would be helpful. Attach a screenshot of Excel if that would help.


Was this answer helpful?

Answered by:
Original
dandiebolt_QB_Pirate , Pres. of Service Worker Union
QuickBase Pro 22 days ago



Photo of Dan G. (QB Support)

Dan G. (QB Support), Official Rep

  • 490 Points 250 badge 2x thumb
1 comment

Thank you for looking into! Just posted clarification, hope it would help. I am a little uncomfortable posting proprietary data in public forum.