When selecting items from a large inventory of items in an order entry app, is it possible to first select a manufacturer name, and then access a drop down list of part numbers associated with that manufacturer?
There are times when the parts list would be filtered by a manufacturer, and other times the manufacturer won't be known, so the entire inventory should be shown to pick from.
Then a formula would need to be used to calculated the final related part number and use that for the lookups if the part number information.
You can do this natively, although there are some considerations (explained at the end):
You need the following:
Manufacturers parts table
Order Lines Table
Manufacturers < Manufacturers Parts > Parts
Order Lines > Manufacturers Parts
Order Lines > Manufacturers
Manufacturers and parts are added via the table Manufacturers parts.
For each new part you will need to add it also to a manufacturer called Unspecified (this is your complete list of parts), if the same part is sold by 10 manufacturers then you only have to add it to unspecified once.
Then in your relationship in order lines to manufacturers parts you base the conditional value on the selected related manufacturer and the related manufacturer in manufacturers parts.
This will mean if you select unspecified you will get a full list of parts or if you select a manufacturer you will only get their parts.
Now you will also need a report link field setup so that it displays the records in manufacturers parts based on your selection (based on part number) it will display all manufacturers with that part when you chose unspecified and the relevant part, allowing you to then amend the drop down and proceed.
Consideration: you will need to amend the drop down to select a manufacturer if using this for ordering.