Skip to main content

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

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).



Yes - two words - Script Block.



I’m pretty sure this is one of fpossibly] 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.

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).


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



Yes - two words - Script Block.



I’m pretty sure this is one of fpossibly] 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.


Thanks! I’ve requested early access to it.

Thanks! I’ve requested early access to it.


@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.

Reply


Terms & Conditions