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…
Best answer by Jeremy_Oglesby
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.
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!
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.
Check template Product Catalog - It has Order Line Items.
It will give you idea.
Neal
Whats the best way for a client to complete an order form if I use the Product Catalog Template? I would like for a client to be able to order their own items with quantities.
+15You’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…
That’s why we built a solution for this problem. Our form allows your clients to add their own line items inside the from while still linking them to the order.
+5You 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.
Hi Jeremy,
Tried to wrap my head around your answered solution and replicate it, but with no success. I think it’s beyond my Airtable knowledge and complexity skillset.
Have you created a base with that particular solution you provided, that you could share?
Thanks in advance, looking forward to your reply :grinning_face_with_smiling_eyes:
+7Hi Jeremy,
Tried to wrap my head around your answered solution and replicate it, but with no success. I think it’s beyond my Airtable knowledge and complexity skillset.
Have you created a base with that particular solution you provided, that you could share?
Thanks in advance, looking forward to your reply :grinning_face_with_smiling_eyes:
I love Jeremy's explanation. For a visual example, this random video here https://www.youtube.com/watch?v=88YShTRwQ94 helped me understnad how it actually works.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
