Re: Double IF condition with multiple rows
Hello all!
I have a question. I would like to create a formula with 2 conditions. IF “Item” in row 1 = “item” in row 2 AND “P” in row 1 is equal to “P” in row 2 then “it’s ok” otherwise “it’s not”
Do you think it’s possible?
Thanks for your help!
Airtable’s data storage is closer to a database than a spreadsheet. Because of that, each row is a record, and database records have no inherent knowledge of each other, so it’s impossible to directly reference any other record to get values, make comparisons, etc.
Could you describe in greater detail what you’re trying to achieve? You didn’t mention where you want this “It’s OK”/“It’s not” output to go, but also knowing the bigger picture would help us to help you more effectively.
That aside, there’s one possible way to pull this off that I can think of. I’ve quickly recreated what I think might be your setup, using an [Items] table to contain the linked items from this main table, which I’ll call [Main] for now. In my setup, I made the {P} field a number instead of single line text, which will make the comparison easier.
Over in the [Items] table, I added a rollup field to pull in the contents of the {P} field based on those links from the [Main] table. If you just use an aggregation formula of “values” for the rollup field, this will show all the values separated by commas.
However, if we change the aggregation formula to this, we get the result you want:
IF(MAX(values) = MIN(values), "It's OK", "It's not")
values represents the array of collected values from the [Main] table for that linked item, and the MAX() and MIN() functions return the largest and smallest items in that array, respectively. In short, if the largest and smallest values are the same, then both values are identical.
We can now use a lookup field back in the [Main] table to pull in this result:
If both numbers match, the other result appears:
NOTE: This technique doesn’t really work that well if there are more than two links for any given item, but the use case you outlined only contains two items, which is why I’m suggesting this as a possible solution.
Wow impressive! in reality the picture is a little bit wider.
Here are all the parameters.
I wouldlike to know if “it’s ok” or “it’s not” with the parameter we talk about previously but there is one more parameter. The starting date and the ending date.
IF “Item” in row 1 = “item” in row 2 AND Item in row 1 has at least a day in common with “item” in row 2 then identify it as “conflict”. Once identify as a “conflict” IF “P” in row 1 is equal to “P” in row 2 then “it’s ok” otherwise “it’s not”
It’s a little bit more complicated :slightly_smiling_face:
I don’t know if it’s possible or not…
