Summary table by customer

[Alex_Ma]

Running into a problem creating an order tracking form.

I have 4 tables
Customer Info - name, address, etc…
Product List - products and prices
Orders - each row has customer name and product and product quantity purchased. Primary field is a formula concatenating Customer name and product they purchased

Now I’m creating a 4th table Order summary by customer. Primary field is an Autonumber. Second column is a linked record to Customer Name pulled from the Customer Info table. What I want to do is group by customer, everything they ordered and the total they owe. Where I am running intotrouble is I can’t pull the Quantity field from the Orders table using a Lookup; I don’t know why. Please help! Thank you

[Nathaniel_Grano]

Hi @Alex_Ma ,

Without getting a few more details about how your orders are structured, I can’t give a too-detailed reply, but I suggest you probably need to use a “Rollup” type column for what you are trying to do, rather than a lookup.

This page should help you figure out which Rollup function to use: Rollup Field - Overview | Airtable Support

[Alex_Ma]

Here’s my Orders table, I think I figured out why I can’t pull the Quantity field because the Quantity is not unique to each Customer in the lookup; can you confirm that’s correct?

[Nathaniel_Grano]

Alex_Ma:

What I want to do is group by customer, everything they ordered and the total they owe. Where I am running intotrouble is I can’t pull the Quantity field from the Orders table using a Lookup; I

Can you give a simulated example of a row you want to see in the Order Summary table?

[Alex_Ma]

@Nathaniel_Granor I should step back for a sec. What I’m trying to create is a database to take store customer information, store products and associated prices, take orders by customer by date, calculate totals and determine whether the customer has paid or not.

Then I want to be able to filter by customer to easily see who has ordered what for packaging/shipping.
Then order by product to ensure my supplier delivered the right and correct amount of products.

I originally had 2 tables:
A Product table with Products and prices AND
A customer table with Name, Address, Paid? flag and a Linked record to Product table to select the product the customer wanted to buy. That worked fine until a customer wanted to buy more than 1 of something so that blew the table structure out of the water.

Now I need a better way to store the information. Then hopefully build an interface to record/update the data during sales.

My main goal in this post is to figure out how to organize the database because what I was doing before wasn’t working when a customer wanted to buy more than 1 of something. Any suggestions for how to organize this? If there’s already a post or tutorial on this, please do share, I’m at a loss. Thanks

[Nathaniel_Grano]

Yes that seems like a pretty common scenario and Airtable can definitely be useful for it. Of course, there are also tons of eCommerce specific tools out there as well.

Airtable does have a starter template that’s worth a look: Product Catalog Template - Free to Use | Airtable

Their model differentiates between an order and a line item, which gives a bit more flexibility in how to then view all the data.

Within their “client orders” table you can see the products/quantities/subtotals rolled up per order, and then you could use the “client” table to roll up those totals further per customer (one customer might have multiple orders over time)

Take a look at that example and then if you still have questions I’d be glad to help!

[Alex_Ma]

@Nathaniel_Granor Thanks, that template is great. I’m a bit confused and maybe you can unconfuse me. There appears to be a bit of circular logic between the Client Orders table and Order line items table where in the Order line items table, you assign a product to a particular order as defined int he Client Orders table.

Is there a way to assign an order directly to a Client without having to assign it to a Client Order #? In other words, the “Belongs to order” field in the Order line items table is just a Client instead of a Client/Order formula? Thanks

[Nathaniel_Grano · Accepted solution]

Hi again,
I didn’t make the template so I can only speculate :grinning_face_with_big_eyes:
It seems like you could do it that way, but the current design better accommodates the scenario where a particular client orders from you multiple different times.

With the current design, you can:

• Set individual products/qty in Order Line Items
• See the items/quantities/costs subtotaled in the Client Orders table
• See the clients’ all-time total bills rolled up in the Clients table.

If you really don’t care about having multiple orders from the same client, I think you could probably do away with the Client Orders table and then replace the “Belongs to Order” field with a “Belongs to Client” field that is a link to the Clients table. Then you would want to add in one or more rollup columns within the Clients table to total up the items/prices