Would like to add up the value of products in a field

[Tamara_Bond-Wil]

I’m not sure this is something that Airtable can do - but let me describe the idea:

1. A customer fills a shopping basket full of items - so one field is “order contains” and it lists all the items contained.

2. The items are in a separate, linked table, and have associated values in that table.

3. I’d like to add up the values of each item so we know the value of the customer’s order.

4. Each customer has a coupon which tells us how much they can spend.

5. In a Best of all Possible Worlds scenario - that calculated value of the order would be compared to another value to determine if the customer overspent their allotted “coupon”.

Is this doable? How?

[Jeremy_Oglesby]

It seems like this should all be doable. I’m not sure how it is you are having customers fill shopping baskets – ie, I don’t know how you are getting your data into Airtable…

But, you need to incorporate the concept of “List Items” as a junction between a Product and an Order.

You have your Products table - each record (row) in that table represents a Product you offer, and presumably has a base Price attached to it.

You have your Orders table - each record (row) in that table represents an Order placed by a Customer (the Order “belongs to” a Customer, so you presumably have a Customers table as well) and has record of all the products and the total owed for the Order.

Your Line Items table will contain fields that link to an Order (a Line Item belongs to one Order), and to a Product (a Line Item references one Product), and probably has a field for Quantity, so that more than one of said Product can be accounted for in a single Line Item. Then you will have a Line Item Total field that multiplies the Price of the Product by the Quantity requested in the Line Item.

An Order may have any number of Line Items, and the Orders table will have a field that sums all the Line Item Totals for all Line Items linked to it to get the Order Total. You can then have a field where the Coupon amount is entered, and another formula field that can compare the Coupon amount to the Order Total, returning some warning message when the Coupon is exceeded by the Order Total.

[Tamara_Bond-Wil]

Jeremy_Oglesby:

You have your Orders table - each record (row) in that table represents an Order placed by a Customer (the Order “belongs to” a Customer, so you presumably have a Customers table as well) and has record of all the products and the total owed for the Order.

OK - this is what I didn’t have. I had the “order” listed in the customer table - essentially these are yearly orders, one per customer, but it is possible that a customer could have more than one, so maybe this makes sense. As to what goes in each “order” record, you write:

Jeremy_Oglesby:

Your Line Items table will contain fields that link to an Order (a Line Item belongs to one Order), and to a Product (a Line Item references one Product), and probably has a field for Quantity, so that more than one of said Product can be accounted for in a single Line Item. Then you will have a Line Item Total field that multiplies the Price of the Product by the Quantity requested in the Line Item.

And I admit to not completely understanding this. You’re proposing a separate table for line items? How does this differ from the price list? This is where you’re proposing, presumably, that I total all the products - but I really can’t figure out how to set up this extra table. I assume that I can write a lookup formula that allows me to bring back values in place of item names - is that what goes here? and if so - I still am not sure I know how to build this table. Do you have an example of this table or can you spell out a simplified version of this?

[Jeremy_Oglesby]

Here’s an example base:

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

When you join you will have “Creator” privileges, so feel free to look at all the formulas, rollups, etc. Play around with creating new records in all the tables to see how it all works.

I’ve included a “Stock Orders” table to track your own orders of product, as well as a way to track your own stock of product in the “Products” table – not sure if that was needed or not, but doesn’t hurt to have it in there.

I’d suggest your main workflow for placing orders be done within the “Orders” table - Create a new “Order” record, link it to a customer by hitting enter or clicking the + button in the Customers field and linking an existing customer or creating a new one. Then create Line Items within the Order by hitting enter or clicking the + button in the Line Items field and creating a new Line Item for each distinct Product ordered (so if 2 distinct products are ordered, there will be 2 Line Item records created within an Order). A Line Item can only ever be linked to ONE Order, and can only ever reference ONE Product (but can be for any amount of that one Product). An Order can only ever be linked to ONE Customer.

Using that workflow you can create an entire Order, Line Items and all, without ever leaving the Orders table.

Ask questions if you have them, and feel free to copy anything out of that base, or even to copy the entire base into your own Workspace if desired.

[Tamara_Bond-Wil]

You are awesome! Thank you! I will play around with this to see how to make it work.

[Caroline_Lee]

Hi, Im trying to solve exactly this problem…but i cant access your example to see how you did it? Ive got the Products/Line Items/Orders tables set up now…

[Jeremy_Oglesby]

Sorry about that, @Caroline_Lee.

Can you try this link and let me know if it works for you?

Airtable

Airtable | Everyone's app platform

Airtable is a low-code platform for building collaborative apps. Customize your workflow, collaborate, and achieve ambitious outcomes. Get started for free.

[Caroline_Lee]

Awesome thanks…much appreciated after all this time :slightly_smiling_face: