My situation is this: I'm trying to set up a review site for the publishing house I'm working for. Basically, the editors should be able to pick reviewers for new books based on certain categories. For instance, a new book on local breweries has the following categories attached: Beer making, Local newspaper, Entrepreneurship. Editors should then be able to choose reviewers based on these categories.
I have a list called Categories with a single column called Category, which in turn has a bunch of items, such as Young Adults, Knitting, Local Newspaper, etc.
I have a second list called Reviewers with basic columns such as Name, Address, etc., and a lookup column called Categories, linked to the Category column in the Categories list, allowing for multiple picks (which is to say, a reviewer can be attached to one or more categories).
I have also created a third list called Reviews with various related columns: Book Title, Project Number, Date: simple enough columns Categories: A lookup column linked to the Category column in the Categories list, allowing for multiple picks. Reviewers: The troublesome column. It should give the editors a filtered view of the reviewers based on the chosen categories, and of course allow them to pick said reviewers.
So my problem is getting the last (Reviewers) column to work. Is my solution a cascading lookup field, a calculated column, a workflow, some filter, or am I simply going about this the wrong way?
I've been working with SharePoint Online and SharePoint Designer 2013 for less than two months, so any help is greatly appreciated. Thanks. :)