Managing subscriptions and products in airtable

[Dirk_Jan_de_Wit]

Hi everyone!

We have a subscription business and ship out products to customers, pick them up and re-use them for other customers.

We are using WooCommerce and would like to use airtable to manage the subscription and products.

We would like to create a product database with all the products in our catalog. And then assign the products to a customer when we ship out those products to a customer. So we can always keep track of where our products are. We would also like to keep track of which products a customer received in the past, so we make sure to send out different product each month.

How can this be done in Airtable?

[David_Skinner]

The basic structure you require is three tables: Customers, Products, Orders.

The Orders table will require Link To Record fields to the Customers table and the Products table. So each order would be linked to a Customer and Product.

The Orders table will probably need a second Link To Record field that links to the Products table. This field would contain the products that are currently with the customer. You would need to remove the linked product from the order when it is picked up. This way, you will be able to use a Look Up field in the Products table to see which customer has the product. You would also be able to use a Look Up field in the Customers table to see which products a particular customer currently has.

WooCommerce has a pretty good API as far as I remember, so a lot of the process of updating your records could be done automatically in your preferred programming language. Alternatively, it looks like Zapier has a WooCommerce integration, so it should be fairly easy to use Zapier to update your Airtable directly with your WooCommerce data.

At the risk of sounding like an Airtable evangelical, I’d say that Airtable sounds ideal for your business’ needs and it would be well worth investing time into setting it up and structuring it properly.

[Dirk_Jan_de_Wit]

Hi @David_Skinner

Thanks so much for your time to answer this, I really appreciate it.

I understand the basis and I agree, Airtable is awesome for this!

Do you have any suggestions for the following add-ons:

• How to keep a record of which toys a customer had in the past?
• How to get a suggestion from Airtable which toy to give based on age of the kid, past toys sent and in stock toys. We know the age of the kids so I guess we can just give every toy an age range and when the kids is within this range it would be shown?
• What would be the best way to keep track of the inventory of a toy?
• We would have multiple units of the same toy. How can we keep track of how many times a particular unit has been “rented out”.

[David_Skinner]

How to keep a record of which toys a customer had in the past?
You can use a Look Up field in the Customers table. Use it to look up the Products field on the Orders table. That will give you a field full of linked records to the products that the customer has historically ordered.

How to get a suggestion from Airtable which toy to give based on age of the kid, past toys sent and in stock toys. We know the age of the kids so I guess we can just give every toy an age range and when the kids is within this range it would be shown?
This is a fairly tricky problem I think. It will require some clever combination of Formulae, Look Ups and Linked Records.

What would be the best way to keep track of the inventory of a toy?
+
We would have multiple units of the same toy. How can we keep track of how many times a particular unit has been “rented out”.
It seems like you should probably have an Units table as well then. The structure would then be that Orders are linked to Units, and Units are linked to Products (and Orders are still linked to Customers as well). You can then Roll Up the Orders in the Units table to count how many orders are linked to that unit.

Under this structure, all product specific information is entered into the Products table (price, age range, etc.)

All unit specific information is stored in the Units table (specific unit code, build date etc.). If you need product specific information in this table you should Look Up from the Products table.

In the Orders table you can Look Up the Products Linked Record field via the Units table. Crucially, you should not link the Orders table to the Products field directly; only link them via Units table. Maintaining this proper database structure can help save you from future problems.