Summary from 2 tables

[Trung_Nguyen]

Let’s say I have an item A.

• A was ordered at $10/pc with qty of 100
• A was then ordered at $11/pcs with qty of 50
  I have a table “Order” which keep track of qty and unit price at which the item is ordered.

I made first shipment of 70 which is recorded in table “Shipment”

I would like to have a summary table grouped by Item code. In this table, it will show that I still have to deliver 30pcs at $10/pc and another 50pcs at $11/pc.

How can I do that?

[Mohamed_Swella1]

Hi @Trung_Nguyen,

Without seeing your tables, I would think you will need to do the following:

1. The Shipment table should have a link to the Order Table. So you would add a Field, Link to Order Table (if not already done).

2. This will create a new Field in the Order Table called Shipment and it will link to the Shipment table. (which is what we want).

3. Have a new Rollup field in the Order table. This rollup field should be linked to the Shipment Field, and the formula set to Sum. This will show you how much has been shipped under this order.

4. Make a new Field called “Balance Qty”, it should be a formula between the Order Qty and the Shipped Qty.

If you need more help, please let me know. In order to help you better, share a screenshot (or a view only link) to your base. If you cannot share them here, send them to my email mohamedswellam@hotmail.com

BR,
Mo

[Trung_Nguyen]

Thank you so much for your reply.

Following your instruction, I successfully got the Balance Qty (in table Items). However, I would like to have more details than that.

As in my original question, the unit price for 1 item might be different and I would like to know the balance for each price tier.

I cannot include link in my post so I will send you via email.

Is that possible with Airtable at all?

[Mohamed_Swella1 · Accepted solution]

Hi @Trung_Nguyen,

First thing, remove me from being a collaborator on your Base. :slightly_smiling_face: This will cause Airtable to charge you for an additional collaborator. Also, whenever sending such Bases to anyone for help, make it a View Only link, so they cannot edit in the Base.

Now as for your question, since the items has different prices, then you should treat them as different items (same as you are doing with items 11-025 and 11-026). This will show you the balance of each item with a different price.

If you want, you can add one more Field which is Item Code (maybe it is the HA’s code already?). This will allow you to group them later by Code and therefore know the profit from each Item even if it has different prices.

Is this helpful?

BR,
Mo

[Trung_Nguyen]

Thank you for such a quick response.

It seems that your solution is the easiest way to tackle this problem. It is indeed very helpful.

Thanks again Mo! Really appreciate your help!

[Mohamed_Swella1]

Anytime :slightly_smiling_face:

Please mark the solution as “Answer” so that the topic closes :slightly_smiling_face:

Feel free to ask any more questions that might arise later.