We have three Custom Tables for a project that look like this:
Project Table:
Enviornment Table:
Role Table:
So, on the Catalog Item, they need to fill out three Reference fields (variables) based on these tables:
- Project selection
- Environment selection
- Role selection
The Role selection field has a Reference Qualifier, to only show active records where the Project field matches the Project selection and the Environment field matches the Environment selection. We have this working the way that we want. Here is the tricky part, they would like to add a Reference Qualifier to filter the Environment selection based on the other two tables.
For example, if they chose Project Name "AAA", we can look at the Role table, and see that there are 3 active records that have a value of "AAA" for Project. These represent two environments: PRD (for two of the Roles) and TST. So since PRD and TST are the only Environments for active records in the Role table that have a value of "AAA" for the Project, the Environments selection should be filtered to only allow them to choose from PRD and TST.
How would we write the Reference Qualifier (probably needing a Script Include) to do that?
By the way, the Project and Environment fields in the Role table are References to the other tables.
(Note: I thought originally of adding a check box for each Project type to the Environment table, and having them maintain that, but that isn't feasible, as new Projects may be added in the future, and we do not want to just keep adding new check box fields every time a new Project is added!).
Thanks
