Skip to main content

The problem: Our manufacturer will ship the contents of a Purchase Order as they are made and ship the remaining products when completed. We have had several occurrences of not knowing what’s coming and what is left until it arrives and we open the box. (We have a middle man, there is no direct comms with the supplier).

 

My goal: Build an interface that the manufacturer can open, select the PO number, and input how much of each SKU is in the upcoming shipment, and put in the tracking number.

 

Where I’m at: I have a Master Products Base with all of my SKUs in one table, and purchase orders in another table. I can add my skus from the products table into each PO, but I can’t assign each sku an order qty within the PO record.

The other nuanced issue is that sometimes PO’s overlap and we will have the same SKUs on multiple POs. I want to be able to assign different order quantities to each SKU in a PO, and keep track of how many have been shipped from each PO.

 

Hi Sean! Its great that you are starting to use Airtable to optimize your inventory and shipping processes.

As for other bases that I’ve built with POs, Invoices, etc, you are going to need a “PO Line Items” table, where you can handle the information regarding every SKU-PO relationship. There you’ll have the amount, and you can also have pricing, shipping status, tracking number, etc.

So the SKUs will not connect directly to the POs, but to the PO Line Item. And the PO Line Items will be linked to their PO.

You can create great interfaces so that your supplier has a simple view with only the relevant information and fields for them to input what you need: the new shipments.

And I agree on how you’d like to simplify the data input process for your supplier (thinking of cases when they ship 100 SKU1s and you have purchased those 100 units across 3 different POs). You can have two tables: “Shipments” and “Shipments Line Items”. And with a simple interface the supplier can easily:

  1. Create new Shipments
  2. Enter global details such as shipping date, tracking number
  3. Within the Shipment he can create multiple Line Items: selecting a SKU and the quantity shipped.

After completing a Shipment I would apply an automation to link Shipments to assign the shipped SKUs to the relevant POs in a FIFO fashion. For this the PO Line Item table will have a “Shipment Line Item” linked record field and a “Amount Shipped” number field to save the actual quantity.

You can also have the supplier fill all the shipment information to a single “Shipment” record with multiple columns named: “SKU 1”, “Amount 1”, “SKU 2”, “Amount 2”, etc. But this will require more backend/automation effort to later process and the base structure won’t be as simple and pretty.

Let me know if this helps,

Best regards,

Matt Nixon

Hm does this look right?

And this is the data you end up with:

  •  

I’ve set it up here for you to check out!

Reply


Terms & Conditions