Managing a subscription database along with transaction information

[benzac]

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!!

[TheTimeSavingCo]

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

[benzac]

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 Name	Email	Expiry Date
John	Doe	johndoe@gmail.com

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

INCOME

Paid Date	Email	Subscription Period	Expiry Date
13/4/2019	johndoe@gmail.com	2	13/4/2021
13/4/2021	johndoe@gmail.com	3	13/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!

[TheTimeSavingCo]

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:

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