I'm trying to populate a drop down of employees that aren't assigned to a project based on requests. The requests, assignments, and employees are all in different SQL tables, so I've resorted to nested filtering, which I'd rather avoid.
The first filter takes the start and end date from an empty assignment and filters the requests for which ones fall in that range. The second filter takes that list and filters the assignments for employee ID's attached to those requests. The third filters the full employee list for employee ID's not in the list produced by the second filter.
Is there a better way to do this? Can I delegate the filtering to SQL as a query using Flow? How would I return the query results to PowerApps? I've considered creating and dropping a temp table that PowerApps reads when my editing screen is opened, but I don't know how that would work with concurrency because a second person opening the screen would trigger a new table. I could maybe add it to a collection, but I don't know how to ensure that the query is done executing before building the collection and that no one else has executed it again in the mean time.
The first filter takes the start and end date from an empty assignment and filters the requests for which ones fall in that range. The second filter takes that list and filters the assignments for employee ID's attached to those requests. The third filters the full employee list for employee ID's not in the list produced by the second filter.
Is there a better way to do this? Can I delegate the filtering to SQL as a query using Flow? How would I return the query results to PowerApps? I've considered creating and dropping a temp table that PowerApps reads when my editing screen is opened, but I don't know how that would work with concurrency because a second person opening the screen would trigger a new table. I could maybe add it to a collection, but I don't know how to ensure that the query is done executing before building the collection and that no one else has executed it again in the mean time.