Making “Linked to” Fields Filter like “Multiple Select” fields

[Nathan_Renner-1]

Hello,

I am trying to filter records by a lookup field. I want all the records that contain that specific item, and only those items. However, because the filter options are only “contain,” “does not contain,” “is empty,” and “is not empty” AND because some of my items are contained with each other (“Technical Director” and “Assistant Technical Director”) I cannot filter the way I want to. For example, if I select “contains Technical Director” that pulls up records with both Technical Director and/or Assistant Technical Director. I can’t filter out “Assistant Technical Director” records because that

Wouldn’t it be fantastic if the filter terms for lookup records were the same as the filter terms for multiple choice? Then you’d have so much control!

[Paul_Warren]

Yes! Obviously with lots of records this could get out of hand, but it would be a nice feature.

Alternatively, the ability to filter by a View in a linked table would be awesome!

[Justin_Barrett]

Nathan_Renner-Johnso:

For example, if I select “contains Technical Director” that pulls up records with both Technical Director and/or Assistant Technical Director. I can’t filter out “Assistant Technical Director” records because that

You could filter those out by adding a second condition to filter out records where the record does not contain “Assistant”.

That aside, I agree that the filtering options for certain field types could be more robust.

[ScottWorld]

@Nathan_Renner-Johnso:

@Justin_Barrett’s suggestion above is probably your easiest & best solution for this.

Alternatively, assuming that you have a relatively manageable list of categories to work with:

You could go into your Attendees table, and create some a formula field that results in some unique number or phrase based on the original category name.

For example, “Director” might result in “1” or “1D”, “Technical Director” might result in “2” or “2TD”.

Then, you could create a lookup field in your Schedule table to bring in the formula field. And you could filter your records on that formula field instead.

[Nathan_Renner-1]

Thanks! I tried that, but the problem is that filters out all records which have both “technical director” AND “assistant technical director” (which is most records) only leaves the records which are only “technical director.”

I also tried “grouping” but that doesn’t work quite how I want it to either, since it creates groups for each unqiue collection (i.e. a “technical director” group, a “assistant technical director” group, AND a group for each record that has both values.)

[Nathan_Renner-1]

Interesting…but then would I have to re-enter the data in this new column? I can’t have it auto-pull since the attendees has multiple values?

I think this highlights probably the only reasonable workaround - just change the names of the attendees to be completely unique. Whether that’s number or letter codes. And then filter based on the code.

[ScottWorld]

No, you don’t need to re-enter data. Your new column would just be a formula in your Attendees table that automatically calculates the abbreviated category based on the full category.

And then you would lookup that formula field into your Schedule table.

[Nathan_Renner-1]

Instead of the current attendees lookup I have, right? So I would need to re-do all the work I’ve already done to set it up to work correctly.

I can’t pull auto-pull a second field based on the current attendees field bc the current attendees field uses multiple attendee records.

[ScottWorld]

No, you don’t need to redo anything.

You don’t have an Attendees lookup. You have an Attendees linked record field.

Your new lookup field will be based on your existing linked records field called “Attendees“.

Lookup fields piggyback onto an existing linked record field, which is what you have.

[Nathan_Renner-1]

oh oh oh! Got it; I hadn’t quite understood how to do a lookup in the correct way. Thanks!