I'm trying to get a multi-field lookup to progressively filter out information in a table. Basically, we have three fields: Inspector, Building Name, and Status. Right now all search fields have a lookup rule, and when one is selected it will return all the results of that field into new sections below it.
The problem I'm facing is that it doesn't 'narrow' down. When I select an inspector, I would like the Building Name field to only have the buildings that are tied to that inspector in the database (and vice versa) so that users can search by multiple fields.
I would like some behavior that works like this:
- when one search term is input, only the associated data will populate on the other search inputs.
- when multiple search terms are input, the results will only return items that match all search inputs.
The behavior I'm seeing now looks like this:
I input an inspector, and it returns only the results that have that inspector:
I then keep the inspector name populated and move over to input the building name, at which point the lookup results give me everything with that inspector's name and everything with that building name (notice the inspector is different from what was input - I color coded them):
I've tried adding multiple "when" statements to the lookup rule but then it requires that all fields be populated before it will return any results. I've tried adding multiple rules with single when statements, but then it returns two sets of results as in the example above.
The best solution I've thought of is to force the users to input their search 'sequentially,' that is by forcing them to enter the inspector then the building name then the status. This is imperfect because they will need to search by only one or the other at times.