Multiple Select Formula to return 'pending' subscriptions calculation?

[Gruss]

Hello Airtable community!

Hoping someone can assist with this formula conundrum. In short, seeking to calculate 'pending' (sold but not yet activated) subscriptions within an Airtable base. Subscriptions Table primary field is per nominated subscriber record but subscribers are grouped by their organisational contracts which can have multiple subscriptions sold/attached. To arrive at this true subscriptions pending figure I'm thinking the below calculation path:

Total #subs contracted (Number field)  -  Status  '_Activated'  (Multi Select field)  -  Status 'Deactivated/Cancelled'  (Multi Select field) = Pending (subs remaining to activate)

Is there a way of achieving this within the table or via rollup formula in another table??  (pics attached for elaboration). 

Status field is currently linked to an activations summary table,  which totals nominated subscribers activated and those deactivated. Just need a way of calculating true 'pending' subscriptions against total contracted/sold subscriptions. 

Any help much appreciated!

[MLO]

It looks like Pending is a linked record?  If so, you can just use the Count field type on the table with Subscription status.

If it's a single select field, you can use a rollup on another table that is CONDITIONAL on Field = Pending.  

I'm not sure if I'm understanding how your base is set up, 

[Gruss]

Hi MLO, many thanks for your response. 

You are correct, the multi select 'Status' field has been linked to an Activations Summary table (see attached). 

I've realised that the 'Pending' multi select option is confusing and not very useful so I am planning to remove this and just set nominated customer subscribers as either 'activated' or 'deactivated' .  

What I'm trying to achieve within the linked Activations Summary table is a calculation that states: 'of the total subscriptions sold ('#subs' in Subscriptions Table), how many are 'pending' (or yet to be activated?). Is there a rollup and formula combination I can insert into the Activations Summary table to achieve this? 

Something like:  Total #subs (minus)  _Activated  (minus)  Deactivated/Cancelled   ?

[MLO]

If I'm understanding your set-up and question correctly (which I'm not sure I am), you should be able to simply use a Count field type and then use the "condition" toggle option:

I think the part I'm confused about is this: some of the records that are linked to "Activated" are not truly activated?  What distinguishes them from a regular activated record?

[Gruss]

Hi MLO, 

Records marked "Activated" are individual subscribers that have had their subscriptions activated against their Orgs total contract subscription purchase. Nominated subscribers are often not known at time of contract sale and are therefore activated incrementally across the contract period, hence why the #subs doesn't align with number of records for each Org (e.g. Org 1 in attached image purchased 6 subs, but only 3 subscribers have been submitted for subscription activation). 

Trying to figure out best way to calculate the remaining subs to be activated of the total sold. 

[MLO]

How do you know that only 3 subscribers of the 6 have actually been submitted for subscription activation? What is different in the record of an actual "activated" subscriber and one that is only a potential?  Or do subscribers that have submitted for activation link to the org?

[Gruss]

That is correct MLO, nominated subscribers submit an onboarding form which auto-creates a subscriber record in the table and populates fields for first name, last name, email and organisation (linked to an org directory table). Submissions are then checked and set as activated. It's the calculation of those yet to be nominated/submitted against total subs sold we're seeking. 

[MLO · Accepted solution]

Okay, I think I finally understand and, luckily, this should be pretty easy. 

On the org table, you add a "Count" field that counts the activated subscriptions linked to the Org. Then just add a second field that is a formula that subtracts the count field from the total field.

[Gruss]

Hi MLO,

This certainly provides an option to work from. Gets a little trickier as we have different subscription types across several tables that all have slightly differing fields but some tweaking should enable 'pending' totals to be achieved.

Thank you very much for your insight and time. Much appreciated.