Order system for customers with multiples of several products

I am trying to build an order system with several tabs:

Orders at vendor (I buy from 1 vendor)
Orders from customers
Customers
Products

An order from a customer has several products and might have more than 1 from each item. And that is where I get stuck. How can I add more than 1 of an item when there are multiple items?
If it was just 1 item more times the colums would be product & amount, but that wont work.

I feel like I am missing something obvious…

You’re not missing anything obvious. This is a genuinely difficult problem to solve in Airtable. With your own orders from the vendor, you can easily solve this by having a Line Items table sitting between your Products and Orders at Vendor tables – but you can do this because you have direct access to your tables, so it’s easy enough to manage creating Line Items that can define quantities.

A customer, however, only has access to forms from your Airtable, and forms can only present data from a single table to the customer, and the customer can only submit a single row (record) of data at a time – so it’s not possible to have a customer create multiple Line Items as part of an Order through a single Airtable form.

I’ve not played with external form services like jotform.com – perhaps one of these could be configured to do what you need :man_shrugging:

Otherwise, the best I’ve been able to come up with is to have another table of Product Packages where Products can be linked to and then put into multiple “packages” of quantities.

So let’s say you are selling paper clips, so you have a single record in your Products table for a Paper Clip as a product. Now you’d create a record in your Product Packages table, with a link to the Paper Clip product, and then a Quantity of 1. In the name (primary) field of the Product Packages table, you’ll concatenate the

{Product} & " x" & Quantity

so it will read “Paper Clip x1”.

Now you can create as many Product Packages as you need for commonly purchased amounts of the Prodouct Paper Clips, and your Customer order form is linked to the Product Packages table instead of the Products table.

It’s not terribly elegant, but if a customer starts typing “Paper Cl…” into the Products field of your form (which is presumably a linked record field to your Product Packages table), they will be presented with a filtered list of Product Packages showing all of the packages available for Paper Clips.

Perhaps that’s an approach you could take…

Hey Jeremy,

Thank you for your extensive answer! That really helped. I don’t need my customers to be able to get into airtable directly. That would have been nice but I can fill it in for them. So the most important part is the Line items table. Could you explain or show an example of that?

Check template Product Catalog - It has Order Line Items.


It will give you idea.

Neal

Thank you! I looked at that one before but it didnt make sense then. I understand it now!

You have Products - a record for each Product.

A Line Item will be linked to a single Product, will declare an “Amount” of that Product to be ordered, will produce a “Total” for itself by multiplying Product > Price (obtained via Lookup) by “Amount”, and finally, will be linked to a single Order.

An Order will produce a “Total” for itself by SUM’ing Line Items > Totals (via Rollup) and applying any tax modifiers, discount modifiers, etc. that are needed.

A Product will end up belonging to many Line Items.
A Line Item will always only reference one Product and one Order.
An Order will usually include many Line Items.
A Product is never linked directly to an Order, it is linked only through Line Items.

1 Like