I have a couple of questions here that I will do my best to explain using the following subtable as an example:
I have tried to simplify it as best as possible here, but it’s still a bit confusing. I am trying to setup a sales invoice subtable that links and loads product sales information from two other sheets. The difficulty is that each product (Product No.) has multiple suppliers (Supplier No.) that each offer a different minimum order quantity (Quantity) and price (Unit Price).
Let’s walk through the workflow as I would like it to occur. Selecting a product in the first field in the subtable (Product No.) loads in the recommended Supplier No., Quantity, and Unit Price. However, if we don’t want to use that supplier, the Supplier No. field should also be editable and link to all the suppliers for that Product No when clicked. Then, selecting a different Supplier No. will load a new Quantity and Unit Price for that supplier.
I can easily limit subtable field choices by using dynamic filters. However, I am running into two obstacles to creating this workflow:
When I link Product No. to load a value into Supplier No., Supplier No. turns grey in the link manager and won’t let me link it to another sheet. I’m pretty sure that I have been able to make regular fields both linked AND loaded before. Is this a limitation specific to subtables? Or am I doing something wrong?
If I link Product No. to load values into Quantity and Unit Price, I can’t also link Supplier No. to them to load (update) a new Quantity and Unit Price.
Any workarounds or suggestions for these two issues? Have I explained the problem clearly enough to understand?