How to make a nested IF statement evaluate ALL nested statements
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 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}
)))
Page 1 / 1
Hi Cole. Your intuition is right that using IF isn’t the best way here. Pretty straightforward answer to this question.
DATETIME_PARSE(
MAX(
VALUE(DATETIME_FORMAT({Critique: Last Submitted}, 'X')),
VALUE(DATETIME_FORMAT({Q&A: Last Submitted}, 'X')),
The function you’re looking for is the LAST_MODIFIED_TIME() function.
The function accepts multiple arguments, but will only return the most recent modification time on any of the given fields.
Here’s an example:
LAST_MODIFIED_TIME(
{Critique},
{Q&A},
{Feedback},
{Milestone}
)
There might be something I’m missing, so please let me know if I am.
The same goes for if you have any questions!
Quick Edit:
Using this method, you don’t need any of the additional dedicated fields for a last modified time (unless you explicitly want to see the modified times for each of the fields.
The function you’re looking for is the LAST_MODIFIED_TIME() function.
The function accepts multiple arguments, but will only return the most recent modification time on any of the given fields.
Here’s an example:
LAST_MODIFIED_TIME(
{Critique},
{Q&A},
{Feedback},
{Milestone}
)
There might be something I’m missing, so please let me know if I am.
The same goes for if you have any questions!
Quick Edit:
Using this method, you don’t need any of the additional dedicated fields for a last modified time (unless you explicitly want to see the modified times for each of the fields.
Hi Ben. I didn’t understand the problem to exist as you described. Your answer will give the most recently modified field time, but not the most recent date contained in those fields. Did I miss something?
Thanks for your response. I realize I should elaborate more.
The “most recent” dates that are populated for the 5 ways they can interact with us are all roll-up fields set to show MAX (values). This is because we have linked records where they can submit, or we track, more than once to many of these categories.
So if I do a last modified rule, it will error out as the rollup field in this table is not modified in that way.
@augmented You did not miss anything, however, I see where Ben was going with the logic.
If all the fields have a modified date/time associated with them, and assuming each is updated as the activity occurs, that would work. However, the flaw there is not only in the field time being looked at for a most recent date, but also in that often there can be more than 1 type of interaction captured on the same day, but that actually took place at different dates/times. So I really want to be looking at the actual date input of the interaction, not the field being modified.
Hi Ben. I didn’t understand the problem to exist as you described. Your answer will give the most recently modified field time, but not the most recent date contained in those fields. Did I miss something?
Wassup!
If we actually wanted to return the field value, that would be extremely easy to add in as well!
It’s possible that I may have misunderstood it, but nonetheless, I’ll build the formula that returns the value of the most recently edited field as well and post it.
It’ll probably benefit someone in the future going through the forums looking for an answer.
Thanks for your response. I realize I should elaborate more.
The “most recent” dates that are populated for the 5 ways they can interact with us are all roll-up fields set to show MAX (values). This is because we have linked records where they can submit, or we track, more than once to many of these categories.
So if I do a last modified rule, it will error out as the rollup field in this table is not modified in that way.
@augmented You did not miss anything, however, I see where Ben was going with the logic.
If all the fields have a modified date/time associated with them, and assuming each is updated as the activity occurs, that would work. However, the flaw there is not only in the field time being looked at for a most recent date, but also in that often there can be more than 1 type of interaction captured on the same day, but that actually took place at different dates/times. So I really want to be looking at the actual date input of the interaction, not the field being modified.
Got it. All my formula does is convert each date/time to seconds from the Unix epoch, find the maximum seconds, then transform it back into a date format.
Thanks for your response. I realize I should elaborate more.
The “most recent” dates that are populated for the 5 ways they can interact with us are all roll-up fields set to show MAX (values). This is because we have linked records where they can submit, or we track, more than once to many of these categories.
So if I do a last modified rule, it will error out as the rollup field in this table is not modified in that way.
@augmented You did not miss anything, however, I see where Ben was going with the logic.
If all the fields have a modified date/time associated with them, and assuming each is updated as the activity occurs, that would work. However, the flaw there is not only in the field time being looked at for a most recent date, but also in that often there can be more than 1 type of interaction captured on the same day, but that actually took place at different dates/times. So I really want to be looking at the actual date input of the interaction, not the field being modified.
Is there anything about your use case that prevents you from actually going to the original table, using the formula I wrote, and then using your rollup or lookup field to look at that field on the record?
Hi Ben. I didn’t understand the problem to exist as you described. Your answer will give the most recently modified field time, but not the most recent date contained in those fields. Did I miss something?
So when I amend your formula, and put it in, I get #error in all the fields. I’m guessing this could be because some dates may be blank for any one of the 5 options?
DATETIME_PARSE(
MAX(
VALUE(DATETIME_FORMAT({Critique: Last Submitted}, 'X')),
VALUE(DATETIME_FORMAT({Q&A: Last Submitted}, 'X')),
Is there anything about your use case that prevents you from actually going to the original table, using the formula I wrote, and then using your rollup or lookup field to look at that field on the record?
So, to be sure I understand… You’re asking if there’s a reason I couldn’t put a “last updated” field in the other tables and then doing the rollup of the last updated field on that table.
I think I essentially do something very similar in that I’m just doing the rollup date on the date of activity field (so not the last updated type, but an actual date field type), am I not?
The tracking of the different interactions happens within each table.
So we have a table for last lesson completed date, linked back to a main student table. Similarly, a table for them to submit their Q’s for coaching linked back to the student record of the main student table.
So when I amend your formula, and put it in, I get #error in all the fields. I’m guessing this could be because some dates may be blank for any one of the 5 options?
DATETIME_PARSE(
MAX(
VALUE(DATETIME_FORMAT({Critique: Last Submitted}, 'X')),
VALUE(DATETIME_FORMAT({Q&A: Last Submitted}, 'X')),
You could also wrap the entire formula in an IF(OR(field1, field2…field5), DATETIME_PARSE…). Make sense?
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.
So, to be sure I understand… You’re asking if there’s a reason I couldn’t put a “last updated” field in the other tables and then doing the rollup of the last updated field on that table.
I think I essentially do something very similar in that I’m just doing the rollup date on the date of activity field (so not the last updated type, but an actual date field type), am I not?
The tracking of the different interactions happens within each table.
So we have a table for last lesson completed date, linked back to a main student table. Similarly, a table for them to submit their Q’s for coaching linked back to the student record of the main student table.
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.
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.
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?
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.
@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.
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?
@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'))),
@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'))),
@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 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:
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. 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.
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. 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.
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?
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?
@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'))),
@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'))),
The {Milestone Date} is a lookup field and it might need the DATETIME_PARSE treatment
@augmented I don’t think I followed quite what you were saying with the portion for step 2. I made updates, and it lets me save. The new difference is that if there is only 1 date in the fields we’re comparing then it populates that date. If there are no dates, then it defaults to 1/1/1970 (Ok, I can deal with that), but as soon as there are more than 1 date, I still get the error. I think it’s just that I am not clear on all of the updates you were mentioning.
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'))),
@augmented I don’t think I followed quite what you were saying with the portion for step 2. I made updates, and it lets me save. The new difference is that if there is only 1 date in the fields we’re comparing then it populates that date. If there are no dates, then it defaults to 1/1/1970 (Ok, I can deal with that), but as soon as there are more than 1 date, I still get the error. I think it’s just that I am not clear on all of the updates you were mentioning.
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'))),
Hi Cole. Looks like you got the parens and commas where I’d like them. One other thing I noticed is the DATETIME_PARSE specifier is not correct for your data. I’m not sure if it matters to Airtable or not, but the one you theoretically should use is ‘M/D/YYYY’ since you don’t seem to have leading zeros on your months or days.
I still can’t see why it doesn’t resolve #3 correctly. If I get a free half hour, I’ll try to recreate everything as you have it.
@augmented I don’t think I followed quite what you were saying with the portion for step 2. I made updates, and it lets me save. The new difference is that if there is only 1 date in the fields we’re comparing then it populates that date. If there are no dates, then it defaults to 1/1/1970 (Ok, I can deal with that), but as soon as there are more than 1 date, I still get the error. I think it’s just that I am not clear on all of the updates you were mentioning.
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'))),
@Cole_Wedemeier So, I created a smaller, more accurate example of your setup in order to see if I could help any further. I created three date rollups in a table, using the MAX() aggregator. Below is the formula I used.
DATETIME_PARSE(
MAX(
IF({dt1}, VALUE(DATETIME_FORMAT({dt1}, 'X'))),
IF({dt2}, VALUE(DATETIME_FORMAT({dt2}, 'X'))),
IF({dt3}, VALUE(DATETIME_FORMAT({dt3}, 'X')))
),
'X')
Here is the screen shot of the table I am using.
As you can see, in rows where there is at least one date, the formula works fine on rollup fields. No inner DATETIME_PARSE is even needed.
All that needs to be added is an outer OR() wrapper that checks all the date columns. I wish I could get into your base to see what’s different from my mock-up, but sadly Airtable doesn’t make that easy.
@Cole_Wedemeier So, I created a smaller, more accurate example of your setup in order to see if I could help any further. I created three date rollups in a table, using the MAX() aggregator. Below is the formula I used.
DATETIME_PARSE(
MAX(
IF({dt1}, VALUE(DATETIME_FORMAT({dt1}, 'X'))),
IF({dt2}, VALUE(DATETIME_FORMAT({dt2}, 'X'))),
IF({dt3}, VALUE(DATETIME_FORMAT({dt3}, 'X')))
),
'X')
Here is the screen shot of the table I am using.
As you can see, in rows where there is at least one date, the formula works fine on rollup fields. No inner DATETIME_PARSE is even needed.
All that needs to be added is an outer OR() wrapper that checks all the date columns. I wish I could get into your base to see what’s different from my mock-up, but sadly Airtable doesn’t make that easy.
I was guessing that there may be a data table that was displaying the date in local vs everyone seeing GMT, etc. I ensured they were all the SAME and that finally stopped producing the error when I applied your latest example
I cannot thank you and @Ben.Young for all your assistance in trying to resolve my formula with me.
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'))),
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.
