Help

Cleaning up and fixing nested IF( OR( AND( FIND(

Topic Labels: Formulas
1370 4
cancel
Showing results for 
Search instead for 
Did you mean: 
AnnicaT
7 - App Architect
7 - App Architect

Hi everyone!
I have a nested formula that supplies me with tag suggestions. The tags depend on info from 8 different columns. I’ve gotten it to do almost everything I want it to, it suggests tags really well. The problem is the ELSE part of the formula. If it finds no tags I would like it to say “« :no_entry_sign: No Tag »\n”.
I can get it to do that just fine by using a second column, but I would like it all in the one if possible.
Here’s what I’m working with. It’s way longer, but this is the gist of it structure wise.

IF(
OR(
    {Column 1},
    {Column 2},
    {Column 3},
    {Column 4},
    {Column 5},
    {Column 6},
    {Column 7},
    {Column 8}
),
IF(
	OR(
		FIND("light", LOWER({Column 1})),
		FIND("vanilla", LOWER({Column 8})),
		AND({Column 2} >= "2020-11-01",{Column 2} <= "2020-11-16")
	),
	"« Tag 1 »\n",
	""
)&''&IF(
	OR(
		FIND("bi", LOWER({Column 1})),
		FIND("barn", LOWER({Column 1})),
		FIND("bi", LOWER({Column 8})),
		FIND("barn", LOWER({Column 8}))
	),
	"« Tag 2 »\n",
	""
)&''&IF(
	OR(
		FIND("bi", LOWER({Column 1})),
		FIND("barn", LOWER({Column 1})),
		FIND("bi", LOWER({Column 8})),
		FIND("barn", LOWER({Column 8}))
	),
	"« Tag 3 »\n",
	""
)&''&IF(
	OR(
		FIND("week", LOWER({Column 1})),
		FIND("week", LOWER({Column 8})),
		AND({Column 2} >= "2020-08-01",{Column 2} <= "2020-08-09")
	),
	"« Tag 4 »\n",
	""
)&''&IF(
	OR(
		FIND("beethoven", LOWER({Column 1})),
		FIND("beethoven", LOWER({Column 8}))
	),
	"« Tag 5 »\n",
	""
),
"« 🚫 No Tag »\n")

Is it possible to do in one column as my brain keeps telling me?

Additionally, as icing on the cake with a cherry on top, I would like « Tag 2 » suggested only if the record has a start date, {Column 2}, within the first half of the year and reversely « Tag 3 » when the record falls in the years second half.

I’m beyond greatful for any and all help with this.

4 Replies 4

Technically, it’s possible to do what you want in a single formula. However, doing that would mean that you have to check the output of those other tag tests, and the only way to do that is to duplicate every single test inside a higher level test. That would make your formula twice as long, and more cumbersome to manage. In short, I suggest doing the final no-tag test in a second field.

That aside, there’s some minor cleanup you could do with your inner tests. You’re inserting empty strings into the ELSE part of those inner IF() functions, which isn’t necessary. If that last part of the function is omitted, Airtable will automatically return a proper empty-field value based on what the “true” portion returns. In your case, you’re returning strings, so Airtable will default to an empty string, which means that adding a literal empty string at the end is unnecessary.

On another optimization note, your tests for Tag 2 and Tag 3 are identical. I’m not sure if that’s actually what’s in your formula, or if you just copied the same sample piece into the example formula above more than once in the interest of time. If, however, you actually have two tag tests searching for the same content and returning different tags, those could be combined into a single section that just returns both tags.

Right now they return is the same for Tag 2 & 3 and I could combine them. However, ideally, I’d like « Tag 2 » suggested only if the record has a start date, {Column 2}, within the first half of the year and reversely « Tag 3 » when the record falls in the years second half and I’m not sure how to go about that the right way without having to do it year by year with something like `AND({Column 2} >= “2020-08-01”,{Column 2} <= “2020-08-09”). There has to be a cleaner way, but I’m clueless to how to achieve it.

First off, is your {Column 2} date field an actual date field, or a formula that formats the date as YYYY-MM-DD? If it’s a date field, then your date comparison formulas aren’t going to work as designed. The date field will feed a datetime object to the formula, but you’re comparing it against a string (the date in quotes), which won’t give you an accurate comparison.

To your specific question, you can format the date as a string that only contains the month and day, then turn that string into a number, which can then be used to see where the date falls in the year. I’m going to break this up into several formulas for easier processing.

First, here’s the basic conversion from a datetime object to a number that we can use later:

VALUE(DATETIME_FORMAT(SET_TIMEZONE("America/Los_Angeles"), "MDD"))

I’ll call this field {Date Number} going forward. Pick your appropriate timezone specifier from this page, and replace the one in my example.

Now we have a number between 101 (January 1) and 1231 (December 31). Here’s how to modify those two parts of your sample formula above to check for dates in a specific range:

...
IF(
    AND({Date Number} >= 101, {Date Number} <= 630, 
    OR(
        FIND("bi", LOWER({Column 1})),
        FIND("barn", LOWER({Column 1})),
        FIND("bi", LOWER({Column 8})),
        FIND("barn", LOWER({Column 8}))
	)),
	"« Tag 2 »\n"
)&''&IF(
    AND({Date Number} >= 701, {Date Number} <= 1231,
    OR(
        FIND("bi", LOWER({Column 1})),
        FIND("barn", LOWER({Column 1})),
        FIND("bi", LOWER({Column 8})),
        FIND("barn", LOWER({Column 8}))
    )),
	"« Tag 3 »\n"
) ...

Notice the two AND() functions that I added. The version for Tag 2 looks for a date number between 101 and 630 (January 1 and June 30). If the date matches that, and any of those other FIND() results are true, you’ll get Tag 2. A similar AND() on the Tag 3 section will only give you a result if the date falls in the latter half of the year.

Thank you so much! It all works perfectly now. A special thank you for taking the time to supply the reasoning and not just the answer. This way I understood how and why it was done so I should be able to do it on my own in the future. I love learning. So, thank you! Truly!