Introduction
In my previous article, I showed you how to automatically update your customers’ records in Airtable based on ThriveCart purchases.
Now you’ll learn how to manage customer account balances in Airtable based on ThriveCart as well.
This is especially useful if you sell any kind of ‘developer hours’ or flex time that needs to be replenished.
What you will need
1 – Airtable – the visual relational database tool that we use to run our entire business!
2 – Zapier – with Pro plan to be able to run multiple actions in a single Zap. Alternatively we can host and manage your zaps for you at $5/zap/mo.
3 – ThriveCart – our preferred shopping cart solution.
Setting up the Zapier automation
Since we are running a service where our customers can purchase a certain amount of developer hours for our team to work on their requests, we need to maintain balance records for them.
This will allow us to see how many hours we can provide to the customer.
Likewise, it allows our customers to check what has been done, how much time has been spent and what their account balance is at any given moment.
Everything is updated dynamically in real-time!
This is handled by Zapier:
1 – Login to your Zapier account
2 – Create automation selecting any event that you’d like to use as a trigger (in our case it is a ThriveCart purchase event)
3 – Create a new step and select Airtable as the application
4 – Select your Airtable account
5 – Choose Action Event – ‘Find Record’
We need to search for a record first to be able to update already existing records (if any) for the customers that are already in the database.
6 – Choose your Airtable account in Zapier
7 – Fill up the Customize Record section in the next step with the required Base and Table. Take the email address to search from your Trigger event:
This will allow you to search for a record you need to update, but we also need to create the record if it does not exist.
8 – Enable checkbox saying: “Create Airtable Record if it doesn’t exist yet?”
9 – Select the data from your Trigger event that you want to add to the record:
In this case, if no record has been found – the new one will be created with the data that you have selected.
Once we updated People table properly we need to update our account balances chart, here is how it look like:
- Name is a project name
- Timesheet – Linked record to Timesheet table
- Total time – Roll-up for ‘Timesheet’ by field ‘Total time’
- Rate – Number field, hourly rate for a contract
- Total value – Formula field, represents a total worth of time spent towards the project, calculated as: (({Total time}/3600)-{“Free” hours}/3600)*Rate
NOTE: all values in fields with type ‘Time’ are in seconds, so you need to convert them to hours before as in the example above - Paid – Number field, a total value paid to the account
- Balance – Account’s effective balance, formula field calculated as: Paid-{Total value}
These are the main fields for this table, we will also need a linked Email field for technical purposes.
10 – Create a new step and select Airtable as the application
11 – Select your Airtable account
12 – Choose Action Event – ‘Find Record’
13 – Choose your Airtable account in Zapier
14 – Fill up the Customize Record section in the next step with the required Base and Table. Take the email address to search from your Trigger event:
15 – Enable checkbox saying: “Create Airtable Record if it doesn’t exist yet?”
16 – Select the data from your Trigger event that you want to add to the record
At this point we are looking for a record by email that we have added as a linked field from People table, you may hide that field for better visibility.
If there is a very first transaction is coming in then the record will be created.
17 – Add one more Zapier step, navigate to app called ‘Formatter by Zapier’ and select action called ‘Numbers’
18 – Go to Customize Numbers and set the following:
- Transform – Perform Math Operation
- Operation – Add
- 1st Input field – In dropdown list go to Step 3 of your Zap and select ‘Paid’ from the list of the fields available
- 2nd input field – Select the amount of transaction from Step 1 of your Zap (That’s a trigger action for this Zap)
You should get the following setup:
19 – Add one more Zapier step and select ‘Find the record in Airtable’ again, use the same settings as in previous ones except that now we do not need an option “Create Airtable Record if it doesn’t exist yet?” enabled.
20 – Add next Zapier step and select ‘Update record in Airtable’
21 – In ‘Customize Record’ section set Base, Table and Record (for this one use record ID from Step 5 of your Zap):
22 – In the same step for the field ‘Paid’ select Output number from Step 4 of this Zap:
This will update the current data in the Paid column to the new one, where the new one is ‘old numbers+amount paid through this transaction’
You are now completely set, enable your Zap and enjoy!
Want Us To Set Up and Host This Automation For You?
Just get in touch with us here:
Now let’s hear from you!
How are you managing your accounting and purchases?
Tell us about your experience in the comments section below!
What do you think of this tutorial?
Name: How to manage Thrive payments using Airtable
Description: Tired to manage your customer's accounts manually? We have an automated solution you should check!
Author: Viktor Nadeyin
Provider: MemberFix
Provider Logo: 
