Comparing two fields and filling another field with checkbox

Shang · ‎Jul 13, 2022

Hello.
Nice to meet you!
I want to compare 2 fields and fill another field with checkbox.
For example, I have Company_tags and Current_firm fields in A table.
Company_tags fields contain several companies name, and Current_firm contains 1 company name only or not contains.
So what I want is I want some automatic way of seeing if the Current_firm is included in the Company_tags.
If not, I want to check Confirmed field.
I think this can be done in Airtable automation script or using other script.

Check screenshot.

Please help me how to do this.
Thank you.

Ben_Young1 · ‎Jul 13, 2022

Hey @Shang!

You can certainly accomplish this pretty easily!

First, I built an identical setup to your screenshot.

From there, I created a formula field with the following formula:

IF(
    AND(
        FIND(
            {Current Firm},
            {Companies}
        ),
        {Current Firm}
    ),
    "✅ Found",
    IF(
        {Companies},
        "⛔️ Not Found"
    )
)

Airtable formulas treat linked record field values as a string, so doing a flat search using the Current Firm field will just return a clean boolean that we can use in the parent IF function.

Once you’ve done this, you can then create an automation that fires based on an update to the formula field.

Since our formula is doing all the heavy lifting, all we have to do is set our two conditions.

If the value is ⛔️ Not Found, then set the checkbox to true.
If the value is ✅ Found, then set the checkbox to false.

Once you finish up, all you have to do is hide your formula field, and you’re all set!

Shang · ‎Jul 14, 2022

Hello @Ben.Young
Thank you for your reply and help.
Yes, your explanations are very clear to me and helped a lot.
It works professionally. :grinning_face_with_big_eyes: You’re super!

I added LOWER() function to the formula for ignoring Case Sensitive and exact match.
IF(
AND(
FIND(
LOWER({Current_firm}),
LOWER({Company_tags})
),
LOWER({Current_firm})
),
“ :white_check_mark: Found”,
IF(
LOWER({Company_tags}),
“ :no_entry: Not Found”
)
)

So it works.
Thank you so much.

Shang · ‎Jul 14, 2022

Hello @Ben.Young
Sorry, I have one more question.
How to update Confirmed field at once? It looks like some fields are not updated properly even though Not Found. Of course, I added trigger in automation workflow, but for now, I want to update Confirmed field with Checked or Unchecked.

And I don’t want to check Confirmed field when Current_firm is a blank.

Please check this screenshot.

Please help me.
Thank you.

Ben_Young1 · ‎Jul 14, 2022

Here’s a viewing link to the base I did this in if you want to take a look at how it’s setup:

I did a few things:

Firstly…

I rewrote the formula to accommodate some possible scenarios.
I wrote it fast, so it might not be the most efficient, but it gets the job done.

IF(
    OR(
        AND(
            FIND(
                LOWER({Current Firm}),
                LOWER({Companies})
            ),
            LOWER({Current Firm})
        ),
        AND(
            LOWER({Current Firm}) = LOWER({Companies}),
            AND(
                {Current Firm},
                {Companies}
            )
        )
    ),
    "✅ Found",
    IF(
        {Companies},
        "⛔️ Not Found"
    )
)

Secondly:

I cleaned the automation up to look for all changes to the Companies, Current Firm, and Result field.
This will allow the automation to keep an eye for every possible scenario that might influence what you want to happen.

I changed the conditions on the first action group to make sure that the Current Firm field is not empty.

Then, just setup an action group as your “Otherwise”, and tell it to set the checkbox to false.

I tested it a few times and it seems to be running smoothly.
Again, feel free to copy the base I shared into your workspace and peek at how it’s setup if you’re having trouble with it.