AnsweredAssumed Answered

Conditional Value List Assist for a Portal's New Record for Existing Order

Question asked by nclark4 on Aug 20, 2018

Hi, I have only been using Filemaker Pro Advanced v17 for not quite two weeks, so very new.  I am stuck after searching for an answer and would appreciate a pointer on where to go or what solution to use.  What I have done so far all works with one exception related to conditional value lists.

 

Layout: Order Revisions

Parent Table: Order

Child (Portal) Table: Order Items

 

I have created an Order Revisions layout as some processing occurs after the customer places an order; once done but before invoicing (separate layout), the order item information may need to be updated.  There are four product categories, so I have four portals which successfully filter each of the order items by the Product Category.  This is the layout example of the "Cut" Product Category, where the data shown are existing order items that can be updated as needed:

Order Items Portal Cut Filter.png

The 3 fields on the left should be hierarchical in descending order:  Product Name is a hierarchical conditional value (CV) list from Product Category and Product Detail is a hierarchical conditional value list from Product Name. Currently, the Product Name drop down is correct, but see cheater caveat below.  The Product Detail drop down either lists all values or none depending on how I set the value list. 

 

(Side note - For new order items, I have the portal field Product Category (not named but first on the left) populate through an on object enter script attached to the Product Name field to minimize user error; for existing items, it pulls from the Order Items table--I couldn't figure out how to hide this field in the portal and needed it to make other things work, but again I'm new so maybe missed something.  Otherwise this script works with no issue.  I am also sure the relationships are right since I can add a new row, fields auto-populate correctly, then I can delete it in both the portal and Order Items table; i.e., changes in the portal perpetuate in the table (I adapted Phil Caulkins's empty portal row script so thanks for that!))

 

Anyway, I think I know a reason for the CV issue: I have the portal field Product Detail pulling from Order Items::_fk_Product Detail with a drop down control set to a value list set-up based on the "Adventures in Filemaking #1" demo hierarchical conditional value lists (named "CL..." tables in attached ERD).  However this demo seems to be based on tables with set values (so changes to existing only, or new only, not adding new plus changes to existing), so I think I need to change how I have the fields set up?  Or model?  I was trying variations on hardwired (which is going to get complex fast plus be difficult to maintain) and hierarchical so the "CL_..." tables are in flux.  If I base the value list to pull values only from the Order Items table, that order usually doesn't have all products so the drop down list would be incomplete (tried this).  This is what I currently have:

ValueList_CutDetail.png

This results in no values in the portal Product Detail drop down list, probably because when I check the CL_Product_Main table, there are no values in the foreign key fields (they are set up as an auto-enter calculation per the AinF demo).  The Product Name drop down works because I have it set to "include all values" which I can do with hardwired (that table only includes the cut category now).  If I add other categories to that table that won't work so I recognize it's a cheat and it will need to be fixed too.

 

I have looked through the community forum and read the Filemaker documentation and Adventures in Filemaking 1 and 2 but don't see anything that tackles this specifically.   I also tried Soliant's model from the youtube demo but it didn't pull correctly--I'm guessing since that one also only made changes to a defined value list.  I feel like I'm missing something basic but I've been experimenting on this for a while so would appreciate any advice or pointers on what model to use or adapt.  Thank you!

Attachments

Outcomes