Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Summary table by customer

Solved
Jump to Solution
3394 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Alex_Ma
6 - Interface Innovator
6 - Interface Innovator

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

1 Solution

Accepted Solutions
Nathaniel_Grano
8 - Airtable Astronomer
8 - Airtable Astronomer

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

See Solution in Thread

7 Replies 7
Nathaniel_Grano
8 - Airtable Astronomer
8 - Airtable Astronomer

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?

image

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

Nathaniel_Grano
8 - Airtable Astronomer
8 - Airtable Astronomer

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

Nathaniel_Grano
8 - Airtable Astronomer
8 - Airtable Astronomer

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