Field to list records that occur in two other lookup fields?

[Max_Markin]

This is my first post in the community forum, so I hope I chose the right category.

I am wondering if I can create a column that shows records that occur in both of two other lookup columns.

I’ll describe my use case, and in addition to my specific question, I welcome any feedback on the overall design. I’m very new to all of this.

I have a directory of therapists that our business refers clients to, among other attributes we list their specialties and the locations where they practice. Each of these are many to many relationships, where a therapist may have 1-3 specialties and may practice in 1 to many locations. I have two link to records on the therapist table, one for locations and one for specialties.

We are creating unique landing pages for each combination of specialties and locations (e.g. Addiction therapists in Phoenix, Addiction therapists in Tucson, Trauma therapists in Phoenix, etc.) Each landing page corresponds to a single location + specialty.

So far, I have four tables. One for therapists, one for specialties, one for locations, and a junction table for landing pages that I generated from the locations and specialties. I would like to have a column on the junction table that shows which therapists should appear on each landing page.

So far, I’ve set up two lookup columns on the junction table, one that shows which therapists are connected to the location of a given landing page, and another that shows therapists connected to the specialty of that landing page. What I would like is a column that shows which records appear in both of these columns.

Is this possible? Am I even on the right track with this design? Any help would be appreciated.

[TheTimeSavingCo]

Hi Max, if I’m understanding correctly, you want to compare two lookup results and extract the values that are present in both? If so, I think the only way to accomplish this may be with scripting.

To solve your issue though, an alternative might be to link every therapist record to every landing page record, and use the conditional lookups to match both speciality and location, but that essentially means having one lookup field per landing page record, which…I mean, it works, and honestly I would go with that option just because it’s cleaner in the long term maybe?

Let me know if you need a script or something and I’ll see what I can do

[ScottWorld]

Welcome to the community, @Max_Markin!

The idea when setting up a junction table is that only one therapist should be attached to each record, meaning that each record in your junction table will represent the unique combination of ONE THERAPIST, ONE SPECIALTY, and ONE LOCATION. Only ONE value for each of these fields should show up on any given line in the junction table.

Then, you can group/filter/sort this table accordingly.

[Max_Markin]

Sorry for my delayed response. Thank you both for the feedback.