I am trying to do something somewhat unique, and the best path I can find at the moment is to use List joins, but I'm not sure this is possible. I'm hoping someone can suggest a best course of action.
The issue at the moment is that I have several lists (i.e. Employees) that all have a lookup to the Assets list. I also have a Scheduling list that contains start and end dates during which assets are unavailable, which also has a lookup to the Assets list. I am trying to find a way to filter a selection list in an InfoPath form to only include Employees that are available. The problem is that I am dealing with five or six types of Assets, so I can't have the Scheduling list directly look up the Employee list (or any other Asset type). I am trying to find a way to have this work done server-side, so that the form doesn't get too heavy. I am accessing the lists via the REST API built in to SharePoint, and I am doing my form design with the InfoPath designer. Any thoughts as to how I can get this form working?
UPDATE 1: My current thinking is that I may be better off splitting the Scheduling table into individual lists for each Asset type, then finding a way to toss everything back together when I need it later. Does this seem reasonable? I have no idea how difficult it would be to put the pieces back together later- has anyone attempted something like this?
UPDATE 2: New line of though- I have resigned myself to being unable to do the joins that I would like to do, and instead am trying to accomplish my goal by filtering the data that appears in the selection lists. I am pulling the relevant data from the Scheduling list and all of the data from the Employees list, and trying to filter the Employees list for entries NOT IN the Scheduling list. Does anyone know how to do this, as it will accomplish the goal without having TOO much unneeded data downloaded to the form...