I posted a question a few days ago here Conditional Value List Assist for a Portal's New Record for Existing Order and got no response, so kept plugging away at it off and on while working on other things. Made progress, but still need help! In case there are other new users struggling with this, I tried to be detailed as I saw a lot of the same questions and found myself equally confused. I read all of the FM documentation AND watched all of the videos but still found a lot of information one step short of what I needed, so hopefully this helps someone. I will be the first to say technical writing is hard, especially for those of us between new and experienced, so definitely not criticizing. If you don't need 2 lists but have a similar setup, what I have below should work for you.
So now I have a "hybrid" Magic Value List Magic Value Lists | FileMakerHacks and conditional value list based on this Populate Value list with a calculated selection? , which halfway works. I have tested just about every configuration and am not sure where I am going wrong so I would GREATLY appreciate guidance.
As in my original question, Parent (layout) Table is Order.
Child (portal) Table is OrderItems. OrderItems is joined to a Product table by ProductID (allow creation in OrderItems is checked). I initially only had _fk_productID in the OrderItems table, but eventually added text fields product category, product name, and product detail as well. These 3 fields are what I need to base my lists on in hierarchical descending order.
THIS WORKS: I have four portals (one per category) that filters a customer's existing order by each category. I can add a new product and delete the row (one per order item) if needed (see prior post). The order items::productcategory field defaults to each portal category per an OnObjectEnter script set on order items::productname field so the user doesn't have to enter this.
THIS PARTIALLY WORKS:
I created a gInput global field in OrderItems, duplicated this table and made a self-join from OrderItems::ProductName to OrderItemsCL::gInput (see image). OrderItemsCL is what I named my copied OrderItems table.
In the portals, for OrderItems::ProductCategory I have an OnObjectModify script to MVL's EmptyGlobalFields script with optional script parameter set to list(2,3). See image:
For OrderItems::ProductName, I have OnObjectEnter set to my "Set ...Category" script with optional script parameter
ExecuteSQL("SELECT ProductName FROM Product WHERE ProductCategory = ?" ; "" ; "" ;Order Items CL::gInput)
and an OnObjectModify script set to MVL's EmptyGlobalFields script with optional script parameter set to 2.
The control style specifies a dropdown list from my custom list called CL_ProductName, which is OrderItemsCL, choose gInput only related values from OrderItems.
For OrderItems::ProductDetail, I have OnObjectEnter set to MVL's EmptyScript with optional script parameter
ExecuteSQL("SELECT ProductDetail FROM Product WHERE ProductName = ?";"";"";Order Items CL::gInput)
The control style specifies a dropdown list from my custom list called CL_DetailName, which is OrderItemsCL, choose gInput only related values from OrderItems. [I know this is the same as above, but see next para).
Result: no matter what portal, I get a ProductName dropdown list for the cut category (this happened to be the first one I did). The ProductDetail list is also the same ProductName list.
THIS DIDN'T WORK:
I created a second gInput global field (gInput2) in OrderItems, duplicated the table again, but made a self-join from OrderItems::ProductDetail to OrderItemsCL2::gInput2. I updated the CL_DetailName custom list to pull from OrderItemsCL2, choose gInput2 only related values from OrderItems and updated the eSQL script. The first list pulled up names (albeit same issue as above) but the second list came up blank. I also tried linking OrderItems::ProductName to OrderItemsCL2::gInput in addition to OrderItems::ProductDetail to OrderItemsCL2::gInput2. Blank list. I tried a separate table with only gInput columns (a la MVL demo but with those relevant fields) joined to Product or OrderItems; blank list.
I tried removing the OrderItems::ProductCategory OnObjectModify script and saw no effect.
I also tried all of this but self-joining Product instead of OrderItems and I just got blank lists.
I joined ProductCategory, ProductName, and ProductDetail in OrderItems-Product but this had no effect.