Updating a formula to accommodate 4 or more items in an array

[Stuart_Rice]

Hi All:

We are currently using the following formula to calculate course enrollment based on the values in an array.

SUM(IF(VALUE(LEFT(ARRAYJOIN(Enrollment),FIND(’,’,ARRAYJOIN(Enrollment)),-1))=0,VALUE(Enrollment&’’),VALUE(LEFT(ARRAYJOIN(Enrollment),FIND(’,’,ARRAYJOIN(Enrollment)),-1))),VALUE(RIGHT(ARRAYJOIN(Enrollment),FIND(’,’,ARRAYJOIN(Enrollment))-1)))

This is able to successfully calculate up to three items, but fails when four are in the Enrollment column. Does anyone have a suggestion on how to amend this formula to work with 4 or more items in the array?

Thank you!
Stuart

[Zollie]

As you’re finding out, Airtable formulas are lacking meaningful array support. You might have more luck if you’re able to leverage a rollup field like this user was.

Otherwise, early access is available to the Scripting block (which is in beta).

[Bill_French]

Stuart_Rice:

but fails when four are in the Enrollment column. Does anyone have a suggestion on how to amend this formula to work with 4 or more items in the array?

Yes - two words - Script Block.

I’m pretty sure this is one of [possibly] many hundreds of ways to overcome the roadblocks of the lacklustre formula feature set.

While the code to do this in javascript will not likely be simpler, it will scale to 4, 10, or perhaps 100 items in the array.

[Stuart_Rice]

Unfortunately, roll-ups won’t work because the field in question is a look-up field.

[Stuart_Rice]

Thanks! I’ve requested early access to it.

[Bill_French]

@Stuart_Rice,

Feel free to share a few sample enrollment values here in the meantime. I might be able to get you started with some essential code.