How to make a nested IF statement evaluate ALL nested statements

Cole_Wedemeier · ‎Apr 08, 2022

Hello!

I have a formula with nested IF statements that works except that what I realize I actually need is to not have the logic stop when it finds 1 of the IF statements true, and actually continue on to validate the remaining IF statements.

It very well could be that IF statements are not the right way to reach my goal, so I’ll also explain what my goal is.

We have several ways that our participants in a course interact with us. These include:

Critiques of work
Q’s for Coaching submitted
*** Last Lesson in Course**
Community Engagement
Milestone Reached

Each of these has an Airtable field with the most recent date they’ve interacted with us in the respective category.

What I want to do is create a formula that evaluates each of the 5 dates and populates the most recent of the 5. When I’m doing it with nested IF statements currently, if it finds that 1 fields date is greater than the compared fields date, it stops there and populates that date without validating if the others are actually more recent.

If an IF statement is the proper way to do this, but I’m missing another logical argument to be included for this to work, please let me know :slightly_smiling_face: OR, even if there’s another type of formula to create that would truly provide the most recent date of the 4 categories, that’d be helpful too.

The below is only evaluating 4, I want to include the 5th, but already realized the issue when evaluating just the 4…so that’s why it’s missing all 5 fields, and only lists out 4 total ({Critique: Last Submitted}, {Q&A: Last Submitted}, {FB: Last Posted}, and {Most Recent Milestone})

IF(
  {Critique: Last Submitted}>{Q&A: Last Submitted}, 
  {Critique: Last Submitted}, 
  
   IF(
    {Q&A: Last Submitted}>{FB: Last Posted}, 
    {Q&A: Last Submitted}, 

     IF({FB: Last Posted}>{Most Recent Milestone},
       {FB: Last Posted}, 
       {Most Recent Milestone}
)))

augmented · ‎Apr 08, 2022

Ah. Absolutely. I definitely didn’t make it as bulletproof as possible. You need to wrap each of the MAX() items in IF({field name},…). Like so…

DATETIME_PARSE(
   MAX(
       IF({Critique: Last Submitted}, VALUE(DATETIME_FORMAT({Critique: Last Submitted}, 'X'))),
       IF({Q&A: Last Submitted}, VALUE(DATETIME_FORMAT({Q&A: Last Submitted}, 'X'))),
       IF({FB: Last Posted}, VALUE(DATETIME_FORMAT({FB: Last Posted}, 'X')) ),
       IF({Most Recent Milestone}, VALUE(DATETIME_FORMAT({Most Recent Milestone}, 'X')))
   ),
'X')

You could also wrap the entire formula in an IF(OR(field1, field2…field5), DATETIME_PARSE…). Make sense?

Cole_Wedemeier · ‎Apr 08, 2022

augmented:

DATETIME_PARSE(
   MAX(
       IF({Critique: Last Submitted}, VALUE(DATETIME_FORMAT({Critique: Last Submitted}, 'X'))),
       IF({Q&A: Last Submitted}, VALUE(DATETIME_FORMAT({Q&A: Last Submitted}, 'X'))),
       IF({FB: Last Posted}, VALUE(DATETIME_FORMAT({FB: Last Posted}, 'X')) ),
       IF({Most Recent Milestone}, VALUE(DATETIME_FORMAT({Most Recent Milestone}, 'X')))
   ),
'X')

Ok yes, so the adding of that IF statement in front allows it to validate if there’s a date to format or not before converting to the format “X”.

But I’m laughing now because suddenly, everything is January something of 1970 dates.

Ben_Young1 · ‎Apr 08, 2022

Ahhh… I have been out of the loop!

I was under the assumption that you were working with a table-to-table schema, not a many-to-one architecture.

With that being the case, then the implementation of @augmented’s formula structure is the straightforward way to implement this.

Now, For People In The Future…

Here's the work I did under my understanding.

On the matter of precedent, I get a feeling that at some point in time, someone will stumble upon this thread looking for more methods, so here’s what I built in my sandbox under what I was interpreting.

If each record has multiple editable fields, and you’re just looking for a timestamp of the most recently edited, then the formula found below, as well as from earlier in the thread will produce what you’re looking for.

In the screenshot, the results of this field are shown in the Time/Date Formula field.

The Timestamp Formula

LAST_MODIFIED_TIME(
    {Critique},
    {Q&A},
    {Feedback},
    {Milestone}
)

If you’re looking to get the actual value of the field that was last edited, and you do not want to have an individual modification timestamp field for each respective field in question, then you can use the formula found below to do so.

The results of the formula can be found in the Value Formula field in the screenshot above.

The Value Formula

IF(
    LAST_MODIFIED_TIME(
        {Critique},
        {Q&A},
        {Feedback},
        {Milestone}
    ),
    IF(
        LAST_MODIFIED_TIME(
            {Critique},
            {Q&A},
            {Feedback},
            {Milestone}
        ) = 
        LAST_MODIFIED_TIME(
            {Critique}
        ),
        {Critique},
        IF(
            LAST_MODIFIED_TIME(
                {Critique},
                {Q&A},
                {Feedback},
                {Milestone}
            ) =
            LAST_MODIFIED_TIME(
                {Q&A}
            ),
            {Q&A},
            IF(
                LAST_MODIFIED_TIME(
                    {Critique},
                    {Q&A},
                    {Feedback},
                    {Milestone}
                ) = 
                LAST_MODIFIED_TIME(
                    {Feedback}
                ),
                {Feedback},
                IF(
                    LAST_MODIFIED_TIME(
                        {Critique},
                        {Q&A},
                        {Feedback},
                        {Milestone}
                    ) =
                    LAST_MODIFIED_TIME(
                        {Milestone}
                    ),
                    {Milestone}
                )
            )
        )
    )
)

While the formula does work, it becomes exponentially harder to decipher and work with if you can’t easily keep track of the nesting.
Not to mention that adding a new field to evaluate would be an absolute pain.

Now, if you do want to start evaluating those fields and modification times at scale, then I recommend you utilize a script to do this.

augmented · ‎Apr 08, 2022

Hmmm. Here is my test case using only two dates.

I wonder if your issue is that these are rollup fields. I’m not sure what the MAX() function returns in a rollup of dates, but I would try converting the rollup field values to dates first inside
VALUE(DATETIME_FORMAT(DATETIME_PARSE({Critique: Last Submitted}, ‘format’), ‘X’)) ).

Basically, you need to make sure Airtable sees a date inside DATETIME_FORMAT. Make sense?

Cole_Wedemeier · ‎Apr 08, 2022

@Ben.Young So appreciate you and always love your advice! Thank you so much for still including your solution if this wasn’t my one to many setup. I’m sure others will definitely benefit.

Cole_Wedemeier · ‎Apr 08, 2022

@augmented I was hopeful, but it doesn’t like adding the additional datetime_format (or at least that’s what I’m contributing to the “sorry, there was a problem” message when trying to save.

I’m pretty sure I have all my parenthesis and commas in there appropriately though.

DATETIME_PARSE(
   MAX(
       IF({Critique: Last Submitted}, VALUE(DATETIME_FORMAT(DATETIME_FORMAT({Critique: Last Submitted}, 'MM/DD/YYYY'), 'X'))),
       IF({Q&A: Last Submitted}, VALUE(DATETIME_FORMAT(DATETIME_FORMAT({Q&A: Last Submitted}, 'MM/DD/YYYY'),'X'))),
       IF({FB: Last Posted}, VALUE(DATETIME_FORMAT(DATETIME_FORMAT({FB: Last Posted}, 'MM/DD/YYYY'),'X'))),
       IF({Most Recent Milestone}, VALUE(DATETIME_FORMAT(DATETIME_FORMAT({Most Recent Milestone}, 'MM/DD/YYYY') 'X'))),
       IF({Lesson Completed}, VALUE(DATETIME_FORMAT(DATETIME_FORMAT({Lesson Completed}, 'MM/DD/YYYY'), 'X'))
   ),
'X'))

augmented · ‎Apr 08, 2022

@Cole_Wedemeier I hate doing this, but if you reread my post, you’ll see that you need a DATETIME_PARSE inside the DATETIME_FORMAT. Not another DATETIME_FORMAT. :winking_face:

Cole_Wedemeier · ‎Apr 08, 2022

No need to hate doing that!

I have adjusted, but the parse merely made it so that some fields have errors and others still live in the 1970’s. :slightly_smiling_face: I did make an adjustment to the formula for 2 fields that have ACTUAL date’s and not roll-ups, which allowed it to be saved, where as prior to that adjustment the parsing caused an error (rightfully so).

Definitely going to keep working at this and truly appreciate the help.

I’ll work to create a mockup of the situation that can be shared too as that may be helpful.

augmented · ‎Apr 08, 2022

I just created a single rollup field for dates using the MAX() aggregator in a test table of mine. I was able to apply DATETIME_FORMAT just fine. So, your original problem isn’t there (meaning you don’t need that DATETIME_PARSE inside the DATETIME_FORMAT.

Can you give me a screenshot of the columns being rolled up?

Cole_Wedemeier · ‎Apr 11, 2022

@augmented Here’s a screen shot. The first Most Recent Interaction is with the latest iteration of your suggestion, but is either throwing an error or providing dates in 1970.

For recording #3, you can see that it clearly would have been the date of 3/17/2022 that should have populated, but instead we have 1/20/1970 in the field.

For record #4, We should have had 2/11/2022 but it’s providing an error.

Here’s the formula I have in there:

DATETIME_PARSE(

   MAX(

       IF({Critique: Last Submitted}, VALUE(DATETIME_FORMAT(DATETIME_PARSE({Critique: Last Submitted}, 'MM/DD/YYYY'), 'X'))),

       IF({Q&A: Last Submitted}, VALUE(DATETIME_FORMAT(DATETIME_PARSE({Q&A: Last Submitted}, 'MM/DD/YYYY'),'X'))),

       IF({FB: Last Posted}, VALUE(DATETIME_FORMAT(DATETIME_PARSE({FB: Last Posted}, 'MM/DD/YYYY'),'X'))),

       IF({Milestone Date}, VALUE(DATETIME_FORMAT({Milestone Date},'X'))),

       IF({Lesson Completed}, VALUE(DATETIME_FORMAT({Lesson Completed}, 'X'))),
     
'X'))```