Help

Finding out the maximum number of accounts

Topic Labels: Formulas
Solved
Jump to Solution
276 2
cancel
Showing results for 
Search instead for 
Did you mean: 
VeeLaw
4 - Data Explorer
4 - Data Explorer

I am working with a subscription table and every row is an entry every time a customer subscribed to an account (It is possible for a customer to have more than 1 account). I have a field for Start Date and End Date, and I want to know the maximum account the customer had at some point, so I am thinking that if I would know which dates overlapped, I can then record it to a different table but is there any formula in airtable  where I can do that? 

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

Airtable formulas only have access to data in the single record, so formula fields cannot compare data across different records. Rollup fields can make limited calculations on a field value in linked records, but they also cannot provide the calculations that you want.

If you want to eyeball it for a single customer at a time, you could try using a timeline view grouped by customer, and then look for lots of records that have overlapping times. But I suspect this is not what you want.

You could calculate this with a custom script. I think a novice/beginner script writer would struggle with writing this script, but if you have experience writing scripts or know someone who can write scripts, that's the way to go.

See Solution in Thread

2 Replies 2
kuovonne
18 - Pluto
18 - Pluto

Airtable formulas only have access to data in the single record, so formula fields cannot compare data across different records. Rollup fields can make limited calculations on a field value in linked records, but they also cannot provide the calculations that you want.

If you want to eyeball it for a single customer at a time, you could try using a timeline view grouped by customer, and then look for lots of records that have overlapping times. But I suspect this is not what you want.

You could calculate this with a custom script. I think a novice/beginner script writer would struggle with writing this script, but if you have experience writing scripts or know someone who can write scripts, that's the way to go.

I see, 

I am a novice in terms of writing a script but I'll try to find a template and try to tweak it. Thank you!