Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Automations Formulas
1027 2
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