Help

Managing a subscription database along with transaction information

Topic Labels: Base design Data
1916 3
cancel
Showing results for 
Search instead for 
Did you mean: 
benzac
4 - Data Explorer
4 - Data Explorer

I am trying to automate a subcription/membership database. I have a users table, and an income table. I want to keep track of expiry dates and what I've been trying to achieve is linking all transactions in Income table with a specific email address to a user record with same email in Users tables automatically. 

Logic:

Two transactions in Income table

1. John Doe - 2 year membership purchased - Expiry (20/1/2023

2. John Doe - 3 Year membership purchased - Expiry (20/1/2026)

The users record in Users table will display Expiry (20/1/2026) so that I can trigger an action (renewal email for example) for if they want to renew their membership.

Must I achieve this programmatically or is this possible within airtables columns? 

Feeling as if the learning curve here is quite high. Any help would be appreciated!!

3 Replies 3

Hmm, sorry, could you rephrase your question a bit?  I'd love to help but I don't understand what problem you're facing exactly, apologies

Hi Adam, 

Apologies for the confusion and I appreciate your time 🙂 So, let's say I have a User's table such as the following:

USERS

First Name

Last NameEmailExpiry Date
John Doejohndoe@gmail.com 

And another table called Income, where all subscription transactions are recorded:

INCOME

Paid DateEmailSubscription PeriodExpiry Date
13/4/2019johndoe@gmail.com213/4/2021
13/4/2021johndoe@gmail.com313/4/2024

I would like column 'Expiry Date' in table {Users} to display the latest date (13/4/2024) by finding all records in table {INCOME} that match the users email address (such as the two displayed) and returning the record with the latest date MAX(values). 

I guess what I'm failing to understand is how to link records, and perform a function on them such as what you would do in excel via LOOKUP and MAX(). 

I hope this clearly explains what I'm trying to achieve!

Ah I see!  Thank you very much for the details.  You'll just need to link up the records appropriately, then you can use a rollup field with "max(values)" to display the latest date:

Screenshot 2023-07-05 at 7.59.41 PM.png

Screenshot 2023-07-05 at 7.59.35 PM.png

Link to base

You could use an automation to keep the records linked, e.g. when a new "Income" record gets created, trigger an automation that'll look for a record in "Users" with the same email, and then link the two records together.  The rollup would then automatically update