3 Replies Latest reply on Feb 18, 2011 8:28 AM by philmodjunk

    More Problems with Relationally-Limited Value Lists



      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.

        • 1. Re: More Problems with Relationally-Limited Value Lists

          How did you set up the relationship for your value list?

          If I set up this relationship:

          Invoices::VendorID = ProductsByVendor::VendorID  (ProductsByVendor is a table occurrence of Products)

          And defined the value list to list values from ProductsByVendor, starting from Invoices,

          It works just fine--without having to first create a record in the portal.

          Creating a portal record auto-enters the InvoiceID from the current invoice. Since this then links the current portal record to the current invoice, I suspect you are using "starting from lineItems" instead of Invoices.

          • 2. Re: More Problems with Relationally-Limited Value Lists

            Thanks!  That fixed it. I need to understand why the starting table for the related values had to be in the top-level context (invoices or purchase orders table).

            Is this because I needed the invoice id resolved?  I noticed that if I simply hand-edited the Line Items::InvoiceID field with the current invoice id, the line items record would fill-in its fields correctly.  And, re-selecting the Product ID for that line always generated the correctly related values.

            Hope I'm not hopelessly off-base, because I think I understand it now -- hope I really do!

            Thanks again.

            • 3. Re: More Problems with Relationally-Limited Value Lists

              You got it. Until a record is created and committed in LineItems, there's no valid relationship from the line item record back to the parent record.