Select a single linked record but multiple times per row

[Mud_Chile]

Hello !, I am trying to make a database for my store, I have 3 tabs created as “customers”, “products” and “orders”. The problem is that if someone buys 6 of the same product from me, I must create 6 records of the same product. I would like to be able to select the product and its quantity. then be able to get the total order.
I would greatly appreciate your help!

[Mud_Chile]

It is like making a shopping list where you have the products and their unit price but you want to buy more than just one of each product. :grinning_face_with_sweat:

[Justin_Barrett]

Welcome to the community, @Mud_Chile! :grinning_face_with_big_eyes: Sorry that you haven’t had a response until now. While Airtable is an awesome tool, and it’s tempting to look at it for handling lots of different scenarios, it’s not really designed to act as an ordering system. I suggest looking at a dedicated ordering tool (PayPay, Stripe, Square, etc.) for setting up the actual orders, and use Airtable for storing the completed order, where you can use a quantity field to store the amount of a given product that’s ordered. You might be able to tie into Airtable to pull products and such during the ordering process, but let the order system do what it’s best at (order processing), and let Airtable do what it’s best at (data storage and organization).

[Mud_Chile]

Hi! thanks for your answer. Maybe I explained myself wrong, what I need is to perform a data analysis with Airtable, not to generate orders, I have complete orders but the problem arises when I want to enter an order that has more than one item, so I must create another item with the same characteristics, which for analysis purposes does not help me because I would be evaluating a product twice and with different parameters. I think Stripe is for ordering and payment, I just want to analyze the individual demand for my products in relation to my customers in the easiest way possible. I hope you can help me, I will stay tuned. Thank you very much in advance.

[Justin_Barrett]

Thanks for the clarification. Generally when storing order data, many users have a {Quantity} field that lets them specify the quantity for a given product in an order. Instead of ten records for that thing, they’ll have one record with 10 in the {Quantity} field. If there are ten different products, though, then it would be one record per product.

What often helps is to have one more table named [Line Items] to store this data. Then the [Orders] table ends up linking to several line items for a given order. For example, if someone orders 10 of Product A and 2 of Product B in a single order, there would be two line item records—one linking to Product A in the [Products] table, with a quantity of 10, and another linking to Product B with quantity of 2. Those line items would then be linked into a single order record in [Orders], which would also link to a customer, have its own order number and date, etc.

Does that help?

[Mud_Chile]

Thanks, but I did not understand the part of the [Line Items] table and how it would work, to clarify better these are my tables and their functions:
[Products] where I create the products that I have in stock (ideally I need to have 1 record created for each product (currently I have many records for a single product in cases where I have been asked for 4 of the same product)
[Customers] Where I have personal information of my clients for communication purposes.
[Orders] where is the detail of the order, prices, total of the order, if it is delivered or paid. (The problem here is that I need to be able to just take a record of the [Products] and be able to specify what they are Ex: 4. Then add the value of item x4 to the total.)
Did I explain myself better?
If the new table called [Line Items] works for what I want, I would appreciate you explain how to do it. Thanks in advance.

[Justin_Barrett]

Mud_Chile:

(The problem here is that I need to be able to just take a record of the [Products] and be able to specify what they are Ex: 4. Then add the value of item x4 to the total.)
Did I explain myself better?

Yes, and that’s exactly what this [Line Items] table will let you do. Let me try to break it down further, table by table.

[Products] - Here you have the products you’re selling. Ideally you’ll only have one record per product. Each product record has a price for that product.

[Line Items] - Where you build the list of items for your orders. Each record links to a single product. A rollup field pulls in that product’s price. A quantity field lets you specify how many of that product the customer wants. A formula field multiplies the quantity and the per-item price to get the full price. For example, let’s say someone orders 10 of Product A, and the per-item price (from the lookup field) is $1.50, the formula would output $15. They also order 5 of Product B, and its per-item price is $7.50, so that line item’s total is $37.50. They’re buying 15 total items, but you only need two records to track it because the quantity is stored in each line item’s record.

[Orders] - Here you’ll link to a customer record, set the order date, and link to the line item records from the [Line Items] table. Using a rollup field, you bring in the total price for all line items. Using the example above, you have two line item links: one for 10 of Product A, and another for 5 of Product B. The rollup field shows that the order total is $52.50.

Does that make things more clear? If you’d like one-on-one help setting this up, message me and we can set up a time.

[kuovonne]

If you want a bit more information beyond Justin’s excellent write up, you might want to look at this support article about many-to-many relationships and junction tables.

[Mud_Chile]

Thank you so much, it worked! Thank you!