Skip to main content

Filter by 'contains any of' on Lookup Field

  • June 11, 2020
  • 1 reply
  • 61 views
E

Hi there,

We have a schedule of all our training dates (Schedule) in one tab and then a separate list of all of our trainers in another. In the schedule tab, we have a lookup field that we use to input which trainer(s) are delivering which training. Oddly it won’t let me create a filtered view in the schedule that says When trainer IS ANY OF Serena, Joanna, or David. We only see contains, does not contain, is empty, is not empty. Am I missing an option here or is there a way of using commas to separate the values?

1 reply

Justin_Barrett
Forum|alt.badge.img+21

Welcome to the community, @Emily_Morgan! :grinning_face_with_big_eyes: The “is any of” option is only available for single- or multiple-select fields because they have a known number of options. I can see the benefit of expanding this concept for other field types, though. You might consider adding a request in the #show-and-tell:product-suggestions category (perhaps after a search to see if a similar request already exists).

If the view you’re setting up will always isolate entries tied to that specific group of trainers, you could make a formula field to do the search, with the formula outputting a 1 if any of the trainers is found, and a 0 if not. Then you would add an option to your filter to show any records where that formula field returns a 1.

Here’s an example formula. Keep in mind that with a lookup field, the value seen by the formula might be an array under certain circumstances, which is why I am concatenating that with an empty string first before looking for a name:

OR(
    FIND("Serena", Trainer & ""),
    FIND("Joanna", Trainer & ""),
    FIND("David", Trainer & "")
)
Terms & ConditionsAccessibility statement