More Problems with Relationally-Limited Value Lists
I have read all threads relating to value lists limited to related data, as well as the threads they lead to, and my issue has not been covered, nor has there been any suggestion that applied to my simple case (as far as I could tell).
My layout is based upon the INVOICES starter project, specifically the invoice layout, however the occurrances of the term "invoice" has been changed to "PO" for table, list and field names.
What I have as a result is a layout based on a table of purchase orders, and the functional modification is in generating a PO with line items all from a single customer (a mfr. outsourcing a service), indexed by the products::mfr field. In this PO layout, when the product manufacturer (the customer) name is entered in the PO::customer field, the contact information is filled-in as per the customers layout and table. I have kept the portal showing the LineItems table.
The association of PO:POnumber with the LineItems::POnumber allows a new record to be created for LineItems.
The LineItems::ProductID field is associated with the Products::ProductID.
My problem is that in the portal for LineItems, I want to select from a list of Product IDs that are manufactured by the customer named in the PO. So I simply used the Products::mfr field to specify the mfr for each product entered, then self-joined the products table through the mfr field, and created a single-column related value list using products 2::mfr, starting from Products. I also allowed "other" input. This works under specific circumstances:
If I enter a valid Product ID manually (as an "other" value), and later click back into that field, I get a valid pop-up (or drop down) list of Product IDs from the same manufacturer (identified by the PO::customer field). This is exactly what I want.
However, if the LineItem::Product ID is blank (the default case upon generating a new record resulting from the association to the PO::number field), then clicking into the LineItems::Product ID field shows NO VALUES.
Likewise, should I enter an invalid Product ID manually (again as an "other" value), the resulting value list (upon clicking into this field at a later time) also yields no values.
If I turn off the "relating only..." option in the value list definition, and allow it to use all values, then it behaves as I would expect -- if I click into a blank Product ID field, I get a list of every Product ID regardless of manufacturer.
I've tried to initialize the Product ID field througbh a calc field looking for IsEmpty(LineItems::ProductID) etc., as well as many other attempts to get around this.
How can I get the same intuitive behavior for a relationally-limited value list, as I get with an unlimited value list? More precisely, how can I get the pop-up or drop-down list of products from a specified manufacturer?
Incidentally, the way I came about the modifications I cited above, is through the example of limiting value lists to specific categories of products.
Thanks so much for your help -- I tried to exhaust all documented approaches to this seemingly simple problem.