Purchase Order in Airtable

[UrbnJ]

Hello all!

At the moment we are organizing our purchase orders in Airtable.

We do this through two tables. In table 1 we put the separate products, each product has its own row.

Then we link each product to a Purchase Order Number, which is portrayed in table 2. So in this table each row is a Purchase Order (containing multiple products).

We want to organize this further, which makes it a bit more complicated.
Each product in the Purchase Order will need to be split up further for the shipping process. This is because for each product we can use different labels (for different warehouses).

So basically in the current situation it looks like table 1 and 2.

Table 1:
Product 1 (PO-01)
Product 2 (PO-01)
Product 3 (PO-02)

Table 2:
PO-01 (containing Product 1, and Product 2)
PO-02 (containing Product 3)

Do we need to create a third table to split each product into different products? Let's say:
Product 1 - label 1
Product 1 - label 2
Product 2 - label 1
Product 3 - label 1

Also good to note: right now we have some automations running from table 2. Placing, through email, the Purchase order with the supplier (each product is connected to a supplier), and asking for an update on this.

I'd like to keep it as simple as possible. Recommendations are welcome!

[ScottWorld]

Yes, you are correct.

You would need to create a 3rd table that is known as a “junction table”, and this type of relationship is known as a “many-to-many relationship”.

Each record in this 3rd table would represent the “intersection” of one product and one PO, so you can keep track of each label separately.

You can read more about many-to-many relationships in Airtable’s support document here: https://support.airtable.com/docs/understanding-linked-record-relationships-in-airtable

I also give a demonstration of many-to-many relationships on this Airtable podcast episode: https://www.youtube.com/watch?v=9tXHLU3i5GY&list=PLqssva4liHRwHhQIpTXekG8WObEoyC2F1

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld 

[UrbnJ]

Hi Scott,

Thanks for your reply, very helpful!

I've tried to build it this way, but it gets a bit complex.

I'll explain: I can make a production order per item (table 1): row 1: 5 apples, row 2: 10 pears, row 3: 20 banana's.
And then have that in one Production order (table 2): row 1: 5 apples + 10 pears + 20 banana's.
These products together form one production order.

Then let's say we ship this in 2 shipments. 1 by air, and 1 by sea. 

Shipment 1 contains: 2 apples + 5 pears + 5 banana's
Shipment 2 contains: 3 apples + 5 pears + 15 banana's

Another posibility is mixing 2 production orders in 1 shipment: So PO-1 (5 apples + 10 pears + 20 banana's) + PO-2 (10 candy bars)

So there's the complexity. Entries in table 2 (PO's) can either be split up in different shipments, or added together in combined shipments.
Combining multiple PO's I understand, I can just take the quantities from table 2 with a lookup field. But with the splitting up part I'm stuck.

Do you have any idea how to tackle the second situation of splitting up a PO in different shipments?