Nov 01, 2020 10:29 PM
How can we make a judgment between each date in column {Directive status 1} cell and the date in Column {Calculated ETA}, and then return the “Overdue” if one judgment result is false?
Or how do I get the smallest date in column {Directive status 1} cell.
Solved! Go to Solution.
Nov 03, 2020 06:48 PM
You’ve highlighted 6 columns which don’t seem relevant to your problem, really. It’s making deciphering your issue very confusing.
Can you try to rephrase this? I know {Calculated ETA}
is a Lookup, that shouldn’t matter. If you need to make a Lookup field to lookup {Calculated ETA}
(a lookup of a lookup), then do that.
Nov 02, 2020 08:49 AM
You can’t iterate over arrays in Formulas. But since that array is in the form of a Lookup, that means you can accomplish this in reverse:
Add a Formula field to whatever table that Lookup is pulling from, and compare each record’s value against {Calculated ETA}
(bring in a Lookup for this field if you have to). Then the table you’ve screenshotted could include a Rollup like so: IF(FIND(‘Overdue’, ARRAYJOIN(values)), ‘Overdue’, ‘Valid’).
Nov 03, 2020 01:50 AM
Thank you, but I am not very clear for your said, can you explain more about " Add a Formula field to whatever table that Lookup is pulling from, and compare each record’s value against {Calculated ETA}
(bring in a Lookup for this field if you have to)."
And I know the Rollup function from your description, so I used it to pick the minimum date of the Lookup value.
Then I have some questions below:
1, Rollup→IF(MIN(values),IF(MIN(values)>{Calculated ETA},‘Valid’,‘Overdue’)), why the return values is blank?
2, Why formula that MIN({Standards status(P1)},{Standards status(A1),{Directive status 1}}, return 0, not a date?( Based on the case where all three columns have values)
3, Why the following formula is error? (My thinking is that if 3 column are blank, return ‘Valid’, if one of date of 3 column is small than {Calculated ETA}, return ‘Overdue’)
Nov 03, 2020 08:06 AM
I don’t understand why your formula is suddenly so complicated. You’re referencing fields that didn’t seem involved in your original post. It also “asks” the same things several times and is generally inefficient. Once again:
{Directive status 1}
is a Lookup field, meaning its pulling one field value from each record linked to it. What you need to do is find the source that Lookup field is looking at.{Calculated ETA}
field.IF({name of Directive Status 1's target field} > {Calculated ETA lookup field}, "Valid", "Overdue")
{Direct status 1}
Lookup field, add a Rollup field that pulls in the Formula field referenced above using: IF(FIND('Overdue', ARRAYJOIN(values)), 'Overdue', 'Valid')
Nov 03, 2020 06:30 PM
I see your said now, but it won’t succeed because if {Calculated ETA} Lookup to another table it has a lot of dates back.
Nov 03, 2020 06:48 PM
You’ve highlighted 6 columns which don’t seem relevant to your problem, really. It’s making deciphering your issue very confusing.
Can you try to rephrase this? I know {Calculated ETA}
is a Lookup, that shouldn’t matter. If you need to make a Lookup field to lookup {Calculated ETA}
(a lookup of a lookup), then do that.
Nov 03, 2020 07:36 PM
Sorry, For your said that" 2. Next to that source/target field, add a Lookup field that pulls in the {Calculated ETA}
field." will get the problem of looking up a lot of dates because this links a lot of data. (like following picture).
and my question is solved, you can see the last picture, thank you for you help(very big help for idea)