Linking multiple products/charges to one customer

Shawna_Haner · ‎Jun 13, 2019

In our business we have customers signing up to receive multiple grocery items on a weekly basis, and then we go to their homes and refill them. They sign up for an initial amount that they want, and then we refill it to that amount each week. Then, we charge them based on how much product was needed to refill it. I am trying to figure out if there is a way to use Airtable to link the records of what the customer is subscribed too, how much we add that week to refill, and what the charge is each week.

For Example: Sharon signs up for the service and subscribes to the following:

1 dozen eggs - 1 pack
8 oz of granola - 2 packs
1 loaf of bread - 1 pack
16 oz of rice - 3 packs

When I go in to do her refill, I want to be able to go to 1 table and see what items she is subscribed to, how many packs she is subscribed to, and then also enter how much product I’m adding to get her back up to these levels, while also having it calculate the cost based on the price of each item and how much I am adding.

I’m trying to figure out if there is a way to get all this information in one easy pop up window, rather than building a tab for each customer. Any ideas would be appreciated!

AlliAlosa · ‎Jun 14, 2019

Hi there Shawna! Welcome to the community!

I set-up an example base for you with 5 tables. With more time and more info it may be possible to streamline further, but I think the bones are all there with this set-up.

Clients
Subscriptions - linked to Clients and each Item individually, this is where the original quantities for each item are stored
Items - holds unit prices for each Item
Refill Dates - By linking each Item refilled per visit to the same record on this table, you can easily get the cost per week
Refill Items - the actual Items and quantities refilled per week

I also set-up a SUPER basic invoice (not pretty at all, but the idea is there) using the Page Designer Block pointed at the Refill Dates table.

I am happy to explain further or adjust if needed! :slightly_smiling_face:

Shawna_Haner · ‎Jun 14, 2019

Wonderful! I’ll check this out, thank you for the help!

Shawna_Haner · ‎Jul 01, 2019

Hello! This has been incredibly helpful, there is one more thing that I have been trying to and not quite figured out. Each customer gets their deliveries on a certain day of the week. I have added their delivery day to the summary table, but am trying to figure out how to build another tab that I can filter by day of the week and see the following:

-Day of the week
-Individual items that have been ordered by any customer (so the overall item like bread, eggs, milk, etc.)
-A count of the total quantity ordered of that item for that day across all customers (i.e. 5 loaves of bread, 4 cartons of eggs, 3 quarts of milk)

I want to be able to easily track the total amount of each item I need for each day of the week when ordering. Is this possible?

AlliAlosa · ‎Jul 03, 2019

Hi there! So glad you’ve found it helpfu! I played around with the base a little bit more and added a table called “Days” that rolls up the items needed for each day of the week.

There is quite a bit of complicated linking involved that I can try and explain if it looks like this is something you’d like to implement :slightly_smiling_face: Take a look and let me know what you think!