Is there a way to add a HAS ANY OF in this IF OR formula?


#1

Hi, and thanks for reading this. I have a formula saying this: IF(OR({ProjectManager}=«Alan», {Assembler}=«Alan»), “1”, “9”)

This helps us sort out if Alan is either Projectmanager or assembler, or both, on a record. And in Alans view he can filter in records where he is involved, showing only records with «1». But, in some records Alan is not Projectmanager, he is Assembler together with Timmy. With that formula, this gives us «9», and the record can not be seen in Alans view. And this is where I need help.

In the filter function for Alans view I can let two filters do this:

where {ProjectManager} is exactly «Alan»,
OR
where {Assembler} has any of «Alan»

This gives us the relevant records in Alans view. But, i need that filter function to do a bunch of AND filters, not OR filters, and thats why i want that IF OR formula to provide a certain digit telling if Allan is involved.

The formula should be something like this:
IF(OR({ProjectManager}=«Alan», {Assembler}= HAS ANY OF «Alan»), “1”, “9”)

Is there a way to add a HAS ANY OF «Alan» in that formula?


#2

You could try using SEARCH() to find “Alan” as a string in the list of “Assemblers”:

IF(
   OR(
      {Project Manager} = "Alan",
      SEARCH(
         "Alan",
         {Assembler}
      )
   ),
   1,
   9
)

#3

Jeremy, you made my day! This was exactly what I was looking for, thanks.


#4

Help, How can I write the formula for:

If(awards total 2018)<750,If(awards total 2019)>750,“Plaque”))

I cannot get airtable to accept the formula. I am asking it to look at my awards 2018 field and my 2019 awards field and if the member has less than 750 hours last year but exceeded that 750 this year, then the member would be given a plaque.

Can you help me write that correctly?


#5
IF(
   AND(
      {Awards Total 2018} < 750,
      {Awards Total 2018} >= 750
   ),
   "Plaque"
)