Formula keeps overriding!

AmyAC · ‎Nov 03, 2023

Can anyone spot any glaring mistakes in this formula? The more expressions I add on, then it 'saves' but then overrides with a simpler version of the formula... so assuming there's an error but I'm not seeing an error message and it's infuriating me (facepalm)

There are some fields that need to reference themselves if they are not empty, the rest need to refer to another column if column A contains ***OTHER CHARITIES***

I've been going round in circles for a while now and losing the will 😄

IF({Charity Selection -Series and Challenge Charity Partners 2024}!='', {Charity Selection -Series and Challenge Charity Partners 2024}, IF({Charity Selection - Mixed funding 2024}='***OTHER CHARITIES***', {Other Charity Selection - Mixed funding 2024}, {Charity Selection - Mixed funding 2024}, IF({Charity Selection - Listed Charity Partner 2024}!='', {Charity Selection - Listed Charity Partner 2024}, IF({Charity Selection - Associate Charity Partners}!='', {Charity Selection - Associate Charity Partners}, IF({Charity Selection - Own Place Fundraising 2024}='***OTHER CHARITIES***', {Other Charity Selection - Own Place Fundraising 2024}, {Charity Selection - Own Place Fundraising 2024}, IF({Charity Selection - Mixed funding 2024 WW}='***OTHER CHARITIES***', {Other Charity Selection - Mixed funding 2024 WW}, {Charity Selection - Mixed funding 2024 WW}, IF({Charity Selection - Own Place Fundraising CWC}='***OTHER CHARITIES***', {Other Charity Selection - Own Place Fundraising CWC}, {Charity Selection - Own Place Fundraising CWC}, IF({Charity Selection - Mixed funding CWC}='***OTHER CHARITIES***', {Other Charity Selection - Mixed funding CWC}, {Charity Selection - Mixed funding CWC}, IF({Charity Selection - Mixed funding 2024 SWC50}='***OTHER CHARITIES***', {Other Charity Selection - Mixed funding 2024 SW50}, {Charity Selection - Mixed funding 2024 SWC50}, IF({Charity Selection - Own Place Fundraising LSW}='***OTHER CHARITIES***', {Other Charity Selection - Own Place Fundraising LSW}, {Charity Selection - Own Place Fundraising LSW}))))))))))

AmyAC · ‎Nov 06, 2023

Hi all,

Thanks for your feedback. I've managed to get somewhere with separate formula per field and then combining those together in a master formula so I think we're all set now.

I'll remember not to overload the if statements going forward 🙂

Cheers,

Amy

See Solution in Thread

Sho · ‎Nov 03, 2023

I haven't tested it, maybe like this, with the help of ChatGPT.

IF(
  AND(
    {Charity Selection - Series and Challenge Charity Partners 2024},
    {Charity Selection - Series and Challenge Charity Partners 2024} != '***OTHER CHARITIES***'
  ),
  {Charity Selection - Series and Challenge Charity Partners 2024},
  IF(
    AND(
      {Charity Selection - Mixed funding 2024},
      {Charity Selection - Mixed funding 2024} != '***OTHER CHARITIES***'
    ),
    {Charity Selection - Mixed funding 2024},
    IF(
      AND(
        {Charity Selection - Listed Charity Partner 2024},
        {Charity Selection - Listed Charity Partner 2024} != '***OTHER CHARITIES***'
      ),
      {Charity Selection - Listed Charity Partner 2024},
      IF(
        AND(
          {Charity Selection - Associate Charity Partners},
          {Charity Selection - Associate Charity Partners} != '***OTHER CHARITIES***'
        ),
        {Charity Selection - Associate Charity Partners},
        IF(
          AND(
            {Charity Selection - Own Place Fundraising 2024},
            {Charity Selection - Own Place Fundraising 2024} != '***OTHER CHARITIES***'
          ),
          {Charity Selection - Own Place Fundraising 2024},
          IF(
            AND(
              {Charity Selection - Mixed funding 2024 WW},
              {Charity Selection - Mixed funding 2024 WW} != '***OTHER CHARITIES***'
            ),
            {Charity Selection - Mixed funding 2024 WW},
            IF(
              AND(
                {Charity Selection - Own Place Fundraising CWC},
                {Charity Selection - Own Place Fundraising CWC} != '***OTHER CHARITIES***'
              ),
              {Charity Selection - Own Place Fundraising CWC},
              IF(
                AND(
                  {Charity Selection - Mixed funding CWC},
                  {Charity Selection - Mixed funding CWC} != '***OTHER CHARITIES***'
                ),
                {Charity Selection - Mixed funding CWC},
                IF(
                  AND(
                    {Charity Selection - Mixed funding 2024 SWC50},
                    {Charity Selection - Mixed funding 2024 SWC50} != '***OTHER CHARITIES***'
                  ),
                  {Charity Selection - Mixed funding 2024 SWC50},
                  IF(
                    AND(
                      {Charity Selection - Own Place Fundraising LSW},
                      {Charity Selection - Own Place Fundraising LSW} != '***OTHER CHARITIES***'
                    ),
                    {Charity Selection - Own Place Fundraising LSW},
                    {Other Charity Selection - Own Place Fundraising LSW}
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)

Andy_Lin1 · ‎Nov 03, 2023

It might be helpful to reformat your formula with line breaks and tabs for legibility. For example:

IF({Charity Selection -Series and Challenge Charity Partners 2024}!='',
  {Charity Selection -Series and Challenge Charity Partners 2024},
  IF({Charity Selection - Mixed funding 2024}='***OTHER CHARITIES***', 
    {Other Charity Selection - Mixed funding 2024}, 
    {Charity Selection - Mixed funding 2024}, 


IF({Charity Selection - Listed Charity Partner 2024}!='', 
  {Charity Selection - Listed Charity Partner 2024}, 
  IF({Charity Selection - Associate Charity Partners}!='', 
    {Charity Selection - Associate Charity Partners}, 
    IF({Charity Selection - Own Place Fundraising 2024}='***OTHER CHARITIES***', 
      {Other Charity Selection - Own Place Fundraising 2024}, 
      {Charity Selection - Own Place Fundraising 2024}, 


IF({Charity Selection - Mixed funding 2024 WW}='***OTHER CHARITIES***', 
  {Other Charity Selection - Mixed funding 2024 WW}, 
  {Charity Selection - Mixed funding 2024 WW}, 


IF({Charity Selection - Own Place Fundraising CWC}='***OTHER CHARITIES***', 
  {Other Charity Selection - Own Place Fundraising CWC}, 
  {Charity Selection - Own Place Fundraising CWC},  


IF({Charity Selection - Mixed funding CWC}='***OTHER CHARITIES***', 
  {Other Charity Selection - Mixed funding CWC}, 
  {Charity Selection - Mixed funding CWC}, 


IF({Charity Selection - Mixed funding 2024 SWC50}='***OTHER CHARITIES***', 
  {Other Charity Selection - Mixed funding 2024 SW50}, 
  {Charity Selection - Mixed funding 2024 SWC50}, 


IF({Charity Selection - Own Place Fundraising LSW}='***OTHER CHARITIES***', 
  {Other Charity Selection - Own Place Fundraising LSW}, 
  {Charity Selection - Own Place Fundraising LSW}

))))))))))

It's easier to see now that the formula has multiple IF functions with more than three arguments. I'm not sure how Airtable would handle it, but I can imagine that everything after IF({Charity Selection - Listed Charity Partner 2024}!='', is skipped.

The solution is dependent on what you want to do. I'm not sure what the relationship is between these fields. Do you want the formula to return the first valid field? Or do you want to sum all valid fields?

If the former, then the formula needs to be rewritten – namely, all IF functions with '***OTHER CHARITIES***' must first be wrapped in another IF function that checks if the field is populated (!='', as in the first line; note that you can also omit the check (!='') if the field is blank). Below are the first two blocks, with the extra IF functions added:

IF({Charity Selection -Series and Challenge Charity Partners 2024},
  {Charity Selection -Series and Challenge Charity Partners 2024},
  IF({Charity Selection - Mixed funding 2024}, 
    IF({Charity Selection - Mixed funding 2024}='***OTHER CHARITIES***', 
      {Other Charity Selection - Mixed funding 2024}, 
      {Charity Selection - Mixed funding 2024}) 
    IF({Charity Selection - Listed Charity Partner 2024}, 
      {Charity Selection - Listed Charity Partner 2024}, 
      IF({Charity Selection - Associate Charity Partners}, 
        {Charity Selection - Associate Charity Partners}, 
        IF({Charity Selection - Own Place Fundraising 2024}, 
          IF({Charity Selection - Own Place Fundraising 2024}='***OTHER CHARITIES***', 
            {Other Charity Selection - Own Place Fundraising 2024}, 
            {Charity Selection - Own Place Fundraising 2024})
          [...]

If the latter, then going back to the first code block, add "+" between all the blocks and fix the commas and parentheses, here's the first two blocks of that:

IF({Charity Selection -Series and Challenge Charity Partners 2024},
  {Charity Selection -Series and Challenge Charity Partners 2024},
  IF({Charity Selection - Mixed funding 2024}='***OTHER CHARITIES***', 
    {Other Charity Selection - Mixed funding 2024}, 
    {Charity Selection - Mixed funding 2024}
  )
)
+
IF({Charity Selection - Listed Charity Partner 2024}, 
  {Charity Selection - Listed Charity Partner 2024}, 
  IF({Charity Selection - Associate Charity Partners}, 
    {Charity Selection - Associate Charity Partners}, 
    IF({Charity Selection - Own Place Fundraising 2024}='***OTHER CHARITIES***', 
      {Other Charity Selection - Own Place Fundraising 2024}, 
      {Charity Selection - Own Place Fundraising 2024}
    )
  )
)
+
[...]

Arthur_Tutt · ‎Nov 03, 2023

Hey @AmyAC ! I'd love to help but I won't lie when I saw your formula I got an instant headache 😂 The number of nested IF statements is on another level. But I do want to congratulate you for the ambition.

First thought: Could you break this down into multiple smaller formulas (split into separate fields)? I'm guessing there's either an error (or multiple errors) hidden in there somewhere, or perhaps Airtable even has a limit on formula length (which is why it keeps reverting back to a simpler shorter version...)

I'd recommend breaking each piece down to it's own formula field (can hide those fields after created so don't clutter your table view). And then have a 'master' formula, that takes the result of each sub-formula and gives the final answer.

By breaking it down into smaller pieces will also help you to identify any errors.

If you're still getting errors, send screenshots of the formulas and the results you're getting and we'll investigate further.

Let me know if that makes sense!

Andy_Lin1 · ‎Nov 03, 2023

Hmm, my earlier reply disappeared. The problem is that the formula has a few IF functions with more than three arguments (condition, true, false), particularly those where the condition is ='***OTHER CHARITIES***'. Below is your formula, but with an extra line break before the extra fourth argument in an IF function:

IF({Charity Selection -Series and Challenge Charity Partners 2024}!='',
{Charity Selection -Series and Challenge Charity Partners 2024},
IF({Charity Selection - Mixed funding 2024}='***OTHER CHARITIES***', 
{Other Charity Selection - Mixed funding 2024}, 
{Charity Selection - Mixed funding 2024},

IF({Charity Selection - Listed Charity Partner 2024}!='', 
{Charity Selection - Listed Charity Partner 2024}, 
IF({Charity Selection - Associate Charity Partners}!='', 
{Charity Selection - Associate Charity Partners}, 
IF({Charity Selection - Own Place Fundraising 2024}='***OTHER CHARITIES***', 
{Other Charity Selection - Own Place Fundraising 2024}, 
{Charity Selection - Own Place Fundraising 2024},

IF({Charity Selection - Mixed funding 2024 WW}='***OTHER CHARITIES***', 
{Other Charity Selection - Mixed funding 2024 WW}, 
{Charity Selection - Mixed funding 2024 WW},

IF({Charity Selection - Own Place Fundraising CWC}='***OTHER CHARITIES***', 
{Other Charity Selection - Own Place Fundraising CWC}, 
{Charity Selection - Own Place Fundraising CWC},

IF({Charity Selection - Mixed funding CWC}='***OTHER CHARITIES***', 
{Other Charity Selection - Mixed funding CWC}, 
{Charity Selection - Mixed funding CWC},

IF({Charity Selection - Mixed funding 2024 SWC50}='***OTHER CHARITIES***', 
{Other Charity Selection - Mixed funding 2024 SW50}, 
{Charity Selection - Mixed funding 2024 SWC50},

IF({Charity Selection - Own Place Fundraising LSW}='***OTHER CHARITIES***', 
{Other Charity Selection - Own Place Fundraising LSW}, 
{Charity Selection - Own Place Fundraising LSW}

))))))))))

AmyAC · ‎Nov 03, 2023

Thanks for everyone's insight....

To give some context on reasoning for the formula....

Basically in a registration form that is imported into Airtable, there could be a variety of questions where the answer is either ***OTHER CHARITIES*** or the name of a charity. There's 3 of 10 questions where we trying to validate that if the field is NOT blank, it pulls through the value of that same field.

Sounds like separate formula and a combining master formula might be most helpful! I'm a bit of a newbie with formula on Airtable but thank you for your help!

Thanks,

Amy

Andy_Lin1 · ‎Nov 03, 2023

So you want to have the field output a string of charities? Something like:

IF({Charity Selection -Series and Challenge Charity Partners 2024}, 
{Charity Selection -Series and Challenge Charity Partners 2024}&", "
)
&
IF({Charity Selection - Mixed funding 2024}='***OTHER CHARITIES***',
{Other Charity Selection - Mixed funding 2024}&", ", 
{Charity Selection - Mixed funding 2024}&", "
)
&
IF({Charity Selection - Listed Charity Partner 2024}, 
{Charity Selection - Listed Charity Partner 2024}&", ", 
IF({Charity Selection - Associate Charity Partners}, 
{Charity Selection - Associate Charity Partners}&", ", 
IF({Charity Selection - Own Place Fundraising 2024}='***OTHER CHARITIES***', 
{Other Charity Selection - Own Place Fundraising 2024}, 
{Charity Selection - Own Place Fundraising 2024}
)
)
)
&
IF({Charity Selection - Mixed funding 2024 WW}='***OTHER CHARITIES***', 
{Other Charity Selection - Mixed funding 2024 WW}&", ", 
{Charity Selection - Mixed funding 2024 WW}&", "
) 
&
IF({Charity Selection - Own Place Fundraising CWC}='***OTHER CHARITIES***', 
{Other Charity Selection - Own Place Fundraising CWC}&", ", 
{Charity Selection - Own Place Fundraising CWC}&", "
)
&
IF({Charity Selection - Mixed funding CWC}='***OTHER CHARITIES***', 
{Other Charity Selection - Mixed funding CWC}&", ", 
{Charity Selection - Mixed funding CWC}&", "
)
&
IF({Charity Selection - Mixed funding 2024 SWC50}='***OTHER CHARITIES***', 
{Other Charity Selection - Mixed funding 2024 SW50}&", ", 
{Charity Selection - Mixed funding 2024 SWC50}&", "
)
&
IF({Charity Selection - Own Place Fundraising LSW}='***OTHER CHARITIES***', 
{Other Charity Selection - Own Place Fundraising LSW}, 
{Charity Selection - Own Place Fundraising LSW}
)

AmyAC · ‎Nov 06, 2023

Hi all,

Thanks for your feedback. I've managed to get somewhere with separate formula per field and then combining those together in a master formula so I think we're all set now.

I'll remember not to overload the if statements going forward 🙂

Cheers,

Amy

Arthur_Tutt · ‎Nov 06, 2023

Hey @AmyAC glad that worked out for you! I'm guessing Airtable was just low-key having a melt down over the length of the formula. Nicely done. If you found my answer helpful in breaking the formula down to the component pieces, can you do me a quick favor and mark my answer as the solution (can have multiple solutions). Helps to build my credibility in the community. Thanks!! 🙂🙂