Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Need help with formula

Topic Labels: Formulas
Solved
Jump to Solution
939 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Davoies
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello everyone,

I'm fairly new to Airtable and I've been impressed with the level of questions the community has solved.  This is the first time I'm posting a question with the hope someone can help me.  Here is my situation:

In Table A, I have 3 single-select fields called Trade Choice 1, Trade Choice 2 and Trade Choice 3. Each record (applicant) has a Province of Residence.  I have a lookup field to Table B that lists each trade with the specific provinces where the trade is a Red Seal trade in a multiple-select field called Red Seal Province.

I want to add a verification step to confirm when the choices of jobs are entered that they are a Red Seal Trade in the province of residence of the applicant. I would ultimately use condition colour to highlight any choices where the trades don't exist as a Red Seal in the province of residence.

How do I write an IF formula that would say:  IF(Red Seal Provinces from Trade Choice 1, Trade Choice 2 and Trade Choice 3 has any of the Province of Residence, then return "yes", otherwise return "no")  

Could anyone help with a formula that would mean has any of below or suggest a better way to go about this?  Thank you for your expertise, time and dedication to the Airtable community.

IF({Red Seal Provinces Trade Choice 1} has any of {Province of Residence}, "yes", 
OR(IF({Red Seal Provinces Trade Choice 2} has any of {Province of Residence}, "yes"),
OR(IF({Red Seal Provinces Trade Choice 3} has any of {Province of Residence}, "yes", "no" ))

1 Solution

Accepted Solutions
Jason_Hill
6 - Interface Innovator
6 - Interface Innovator

I don't know if I understand it correctly, but based on your last part, I would rewrite it as such;

IF(
  OR(
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 1}),
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 2}),
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 3})
  ),
  "yes",
  "no"
)

See Solution in Thread

1 Reply 1
Jason_Hill
6 - Interface Innovator
6 - Interface Innovator

I don't know if I understand it correctly, but based on your last part, I would rewrite it as such;

IF(
  OR(
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 1}),
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 2}),
    FIND({Province of Residence}, {Red Seal Provinces Trade Choice 3})
  ),
  "yes",
  "no"
)