Skip to main content

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

S

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

    5 replies

    Z
    Forum|alt.badge.img+18
    • Zollie
    • Inspiring
    • 254 replies
    • February 17, 2020

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

    B
    Forum|alt.badge.img+19
    • Bill_French
    • Inspiring
    • 3264 replies
    • February 17, 2020

    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.

    S
    • Stuart_Rice
    • Author
    • New Participant
    • 2 replies
    • February 17, 2020
    Zollie wrote:

    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.

    S
    • Stuart_Rice
    • Author
    • New Participant
    • 2 replies
    • February 17, 2020
    Bill_French wrote:

    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.


    Thanks! I’ve requested early access to it.

    B
    Forum|alt.badge.img+19
    • Bill_French
    • Inspiring
    • 3264 replies
    • February 17, 2020
    Stuart_Rice wrote:

    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


    Most helpful members this week

    Terms & Conditions