Use for Catering Company Database

[Kayla_Walters]

Hello everyone! I’m trying to set up my small (but growing) catering business with a more dynamic customer database than the google sheet we currently use and I’m wondering if it’s possible to acheive the following with Airtable:

Here’s the scenario I want to start documenting better:

We cater for a corporate client called Acme. Acme is a large company and we have 4 different people from that company that contact us to place catering orders. We’d like to be able to track catering sales per individual in a company AND be able to see overall catering revenue from the entire company.

On the individual basis, we have Annie. Annie ordered from us twice in the past month (we love Annie). In addition to keeping record of Annie’s contact info, I also have a record of when she started placing orders with us, when her last order was, how long it’s been since her last order and how much catering (price wise) that she’s ordered from us in total. I record this data for the three other people from Acme that also order from us.

How can I use Airtable to get a summary of the overall company’s spending with us? I’d like to be able to see the following data:

The last date that anyone from Acme ordered from us
How long its been since anyone from Acme ordered from us
How much money Acme has spent with us to date

I hope that makes sense! Is this possible with Airtable?

[Jeremy_Oglesby]

This is all possible with Airtable.

Digging in:

• You’ll want to have a table for “People”, a table for “Companies”, and a table for “Orders”
• Your “People” table will link to the “Companies” table, such that each person belongs to one company, and each company has potentially many people
• Your “Orders” table will link to the “People” table, such that each order is placed by one person, and each person can place potentially many orders
• You will use Lookup fields to pull information about a “Person” into the “Orders” table, namely, what “Company” they are from
• You can use that “Company” Lookup field to group your “Orders” on, in order to see all orders from that company, and see the total number of orders and the sum of all orders from every individual in that company in the group summary bar
• You can sort your “Orders” by “Date” (assuming you have a “Date” field for when the order was placed), so that you can always see the most recent orders at the top of the list
• In your “People” table, you can pull in information from the “Orders” table using Rollup fields, to have summary information about all orders placed by that person; you can perform a Rollup that finds the MAX() date of all orders placed by that person
• In your “Companies” table, you can pull in information from the “People” using Rollup fields, to have summary information about all orders placed by people from that company; you can perform a Rollup on the Rollup field mentioned above, to find the MAX() date of all orders placed by anyone from that company

… and there’s more you could do to address the other info you want to see, but I think this is sufficient to answer your question for now :grinning_face_with_smiling_eyes:

[Kayla_Walters]

Thank you so much for helping me start! I’ve gotten as far as figuring out how to set up each of the three tables, and I’ve inputted order details for a company so I can get the data to cross reference, but this is where I hit a wall. I figured out how to associate an order with an existing customer and then once I have that information inputted, it automatically tags the company… but I can’t figure out how to get all the orders from “John Smith” that I’ve placed in the Order table to total in the People table. I’ve tried the rollup formula, but it says “Sorry, there was a problem saving this field. The options are not valid.”

Sadly, I’m not sure if this is something someone can help with without having access to my account, but if it is, I’d greatly appreciate it!

[Jeremy_Oglesby]

Assuming your “Orders” table has a “Total” field (currency), you can perform a Rollup in the “Person” table, pointing it at the “Orders” linked records, the “Total” field, and using the SUM(values) formula. Is that how you have it set up?