Summary table by customer

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

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?

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

@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

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!

@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