I'm familiar with hierarchical/conditional value lists. This is something like that, but with a twist.
I have a list of Products. In addition to Product ID and Description, there's a Dept field.
I also have a list of Operators. In addition to the Operator ID and Name, there's a Dept field.
When Operators complete work orders, they select a product from a drop-down list of Products. These days the Product list is lengthy, so I'd like to restrict the list by an Operator's Dept. Operator Bob, who is in Dept A, can only see the Products in Dept A. Operator Jean in Dept R can only see the Products listed for Dept R.
So far it's straightforward, right?
Here's the twist: Several Products should be accessible by more than one Dept. So Product Thingamajig is not only for Dept A, it's for Dept R, too. At the moment when there are multiple Depts for a product, the Depts are all listed in the Product's Dept field separated by returns.
I can't get my head around this. Any suggestions? I really don't want multiple records for Products (one for each Dept).