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.