Formula help!

Topic Labels: Formulas
350 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I have a table that tracks subscriptions for a specific product add-on. It tracks subscription movement for this product based on the unique account business id. Every time a user subscribes, upgrades, downgrades, or churns it adds a record with the new quota, business id, date of upgrade, month of upgrade, and the expansion or contraction MRR. 

A churn is recorded very similarly to a downgrade or contraction record so I'm trying to come up with a way to tell the difference for reporting purposes.  Basically if the business id doesn't occur in the list again, has a 0 quota, and a negative MRR then it's a churn. If it does occur again but has a 0 quota and negative MRR then it is contraction and not a churn.

I'd like to add a formula field in Airtable that based on the instance of the business id it labels the record with either New subscription (if it's the first instance of the business id in the table), expansion (if it's after the first instance of the business id AND has a positive MRR associated with it), contraction (if it's after the first instance of the business id, is not the LAST instance, and has a negative MRR associated with it), or churn (if it's the last instance of the business id AND there is a negative MRR associated AND the quota is listed as 0).

Is there a way to do this in a formula field? or if anyone has alternative suggestions I'm open to them! The business id field unfortunately is not a linked record field so I can only use the information recorded within this table. 

I've included a screenshot of the records for an example.

1 Reply 1

Hmm, a formula field has no access to data outside of the row it exists in, and so a formula field can't figure out whether it's the first instance, last instance, etc.  At the minimum you're going to need to create a new table that consists of the business IDs and link the records appropriately, and you could then use formula fields and rollups to figure it out from there

If you can't change the structure of the base, then I think you're going to need a script I'm afraid