Help

Re: Use value from latest filled field (IF this, IF not then this, but for multiple)

240 0
cancel
Showing results for 
Search instead for 
Did you mean: 
arsenal
4 - Data Explorer
4 - Data Explorer

I have a user groups as individual records. Each month, my team adds a field with the number of members in each group. 

This means I don't have a specific field i can put the 'latest' number in for more public views to the wider company. 
I am trying to create a formula field which says "If this field is blank, try this one, if that field is black, try this one" until a field with values is found... What I have tried, but got no success with (I am not a natural with code/formulas). Can someone help? 😄

 

 

IF(NOT(BLANK({2026-01 Members},
{2025-12 Members},
{2025-11 Members},
{2025-10 Members},
{2025-09 Members},
{2025-08 Members},
{2025-07 Members},
{2025-06 Members},
{2025-05 Members},
{2025-04 Members},
{2025-03 Members},
{2025-02 Members},
{2025-01 Members},
{2024-12 Members},
{2024-11 Members},
{2024-10 Members},
{2024-09 Members},
{2024-08 Members},
{2024-07 Members},
{2024-06 Members},
{2024-05 Members},
{2024-04 Members},
{2024-03 Members},
{2024-02 Members})))

 

 

2 Replies 2
Dan_Montoya
Community Manager
Community Manager

Hello,

I recommend not adding multiple fields to the same table to represent changes.  That is best done with a separate table.  Here is an example.

jsep
7 - App Architect
7 - App Architect

 

The issue is with how the nested IF statements are structured in your formula.

Here's a corrected version:

 

IF(
    {2026-01 Members} = BLANK(), "2026-01 Members",
    IF({2025-12 Members} = BLANK(), "2025-12 Members",
    IF({2025-11 Members} = BLANK(), "2025-11 Members",
    IF({2025-10 Members} = BLANK(), "2025-10 Members",
    IF({2025-09 Members} = BLANK(), "2025-09 Members",
    IF({2025-08 Members} = BLANK(), "2025-08 Members",
    IF({2025-07 Members} = BLANK(), "2025-07 Members",
    IF({2025-06 Members} = BLANK(), "2025-06 Members",
    IF({2025-05 Members} = BLANK(), "2025-05 Members",
    IF({2025-04 Members} = BLANK(), "2025-04 Members",
    IF({2025-03 Members} = BLANK(), "2025-03 Members",
    IF({2025-02 Members} = BLANK(), "2025-02 Members",
    IF({2025-01 Members} = BLANK(), "2025-01 Members",
    IF({2024-12 Members} = BLANK(), "2024-12 Members",
    IF({2024-11 Members} = BLANK(), "2024-11 Members",
    IF({2024-10 Members} = BLANK(), "2024-10 Members",
    IF({2024-09 Members} = BLANK(), "2024-09 Members",
    IF({2024-08 Members} = BLANK(), "2024-08 Members",
    IF({2024-07 Members} = BLANK(), "2024-07 Members",
    IF({2024-06 Members} = BLANK(), "2024-06 Members",
    IF({2024-05 Members} = BLANK(), "2024-05 Members",
    IF({2024-04 Members} = BLANK(), "2024-04 Members",
    IF({2024-03 Members} = BLANK(), "2024-03 Members",
    IF({2024-02 Members} = BLANK(), "2024-02 Members",
    BLANK()
))))))))))))))))))))))))

However, this method isn't scalable; adding a new field requires updating the formula. A more scalable solution might involve using a separate table to manage these records.

References:

  1. Nested IF statements
  2. Base design
  3. Link records 

I hope this helps! If you need assistance implementing this solution, feel free to schedule a free call with me. I'll help you out.

- Juan, Code and No-Code Solutions Expert