Dynamics Community Forum Thread Details

Filter down options based on other field but with an additional step (related records two times)

Hi All

It was really hard to write a good subject for this, so I hope this one described it enough for people to come take a look.

What I want to achieve in CRM (online so latest version) is the following:

I have 3 fields: Product / Production plant / packaging

Based on the product, users should be able to select a certain plant that can produce it

Based on the Product & the plant, users should be able to select a certain packaging.

I am familiar with lookup - related records so I've been trying that like follows:

Products table has unique products:
Product A
Product B etc

Production Plant has the same plant multiple times but for different products
name of the plant - lookup to the product table:
Plant A - Product A
Plant A - Product B
Plant B - Product A
Plant C - Product B

So if a user selects a product, they will see only the Production plants that are available for this product, without seeing a plant multiple times

Now comes the hard part, based on the Product & the production plant, the user should be able to select the packaging.
My idea, or the only option that I could think of was to make a table with all the possible combinations of packaging, plant, product
packaging description - lookup to Production plant table:
300 gram - Plant A (Product A)
500 gram - Plant A (Product A)
300 gram - Plant A (Product B)
100 gram - Plant B (Product A)
etc.

It's a lot of work to maintain and the lookup filtering down twice is of course not as convenient to use as a option set.

Any ideas to simplify this perhaps?

Thanks in advance!

Quick Links