Help

Re: Double IF condition with multiple rows

2772 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Matthieu_B
4 - Data Explorer
4 - Data Explorer

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!

example airtable 2

5 Replies 5

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.

Screen Shot 2020-09-08 at 9.39.45 PM

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.

Screen Shot 2020-09-08 at 9.41.22 PM

Screen Shot 2020-09-08 at 9.42.35 PM

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")

Screen Shot 2020-09-08 at 9.44.39 PM

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:

Screen Shot 2020-09-08 at 9.51.00 PM

Screen Shot 2020-09-08 at 9.51.31 PM

If both numbers match, the other result appears:

Screen Shot 2020-09-08 at 9.52.28 PM

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.

Matthieu_B
4 - Data Explorer
4 - Data Explorer

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…

example aritable 3

In Airtable terms, it’s actually a lot more complicated, and unfortunately it’s not possible, at least not by modifying the setup I created. The hiccup is that you’re not just comparing dates, but overlapping date ranges, and that’s not something that Airtable can currently do via formulas. For this, you’d need to resort to some type of scripting solution, either a manually-run script in the Scripting block, or one driven by Airtable’s relatively-new automation system. If you’d like help setting this up, just ask. There are several of us here who can take care of that, but it would most likely need to be taken on as a client project, as this is a very unique use case that would require custom code.

I have another thing I woulklike to go which need script. Do you know someone who can take care of that?
Thanks again!

I can create a custom scripting solution for you. Send me a direct message and we can discuss the options.