Conditional Value List--multiple fields in another table

[Rich]

Greets, all:

I'm having a royal brain hernia trying to figure out how to create a conditional value list between two tables where there are multiple key fields. (Please refer to the attached.)

In the JOB table there is a field, JOB::CAD_Type, which stores a handful of custom values for a value list: COMM, DIST, PROV, ST, and TERR. In the screen capture, I chose ST (for State) as a value for JOB::CAD_Type. To the right of it, under the pop-up value list, is JOB::CAD_Abbreviated--it gets its values from another table (VALUE_LIST) where you can see VALUE_LIST::Cad_Type (ST), VALUE_LIST::Country_Abbreviated (US). VALUE_LIST is a table dedicated to storing values that are used throughout the database/solution. (VALUE_LIST::Country_Abbreviated is underneath the pop-up value list.)

What I want to do is filter the pop-up value list so that it only shoes pertinent Values per JOB table's CAD_Type and Country_Abbreviated fields. So, with ST in JOB::CAD_Type field and US in JOB::Country_Abbreviated, I only want the U.S.'s fifty states to appear in the pop-up value list.

Another example would be if the JOB::CAD_Type was PROV and the JOB::Country_Abbreviated was CA, only Canada's provinces would appear in the value list.

So far, I "get" that that Cad_Type and Country_Abbreviated will have to be key fields in both tables, but that's where I'm stuck. If you can point to, or upload, a solution I can parrot in mine it would be greatly appreciated!

 

TIA for your help.

[Steve Martino]

Can you post a sample of your file?  This would be the quickest way (at least for me) to figure it out.  But it's basically a dwindling value list that relies on multiple TO's/relationships.  

Of course the devil is in the (setup) details. 

[Rich]

I wish I could but it's a proprietary solution I'm working on so I can't upload it. I'll make a clone of it and will strip away as much as I can tomorrow (Monday) and will then upload it directly.

Edited July 30, 2018 by Rich

[Rich]

Thanks for your patience, Steve--here it is, attached.

Account Name and Password: admin

JCC_Stripped.fmp12.zip

[Steve Martino]

Quite complicated, so I have some questions.  I understand what you wish to accomplish via CVL's, but what is the workflow. 

IOW, are you using this to filter or find records?  Or is this for populating new records?

[Rich]

Populating new records.

[Steve Martino]

See if this sample file helps (I'll continue with your sample).

It comes from the great mind of PhilModJunk: https://community.filemaker.com/people/philmodjunk

from the other forum.

He's posted his sample files many times:

Adventure 1 CVLs.fmp12

[Rich]

VERY cool! By the looks of it, ExecuteSQL Hierarchical Value Lists would probably be the simplest way to implement the CVL--what do you think? Just change Region to Company_CAD_Type and County to Company_CAD_Abbreviated. Still, I don't know how to implement the WHERE part with Country as a referenced field. (I'm not at all proficient with SQL.)

It would be tempting to include the City field (in that example) but that would mean changing the field order in the database which would confuse users: When addressing an envelope--which is (hopefully) familar to everyone--the order is name, adddress, city, state, ZIP, and country.  To use the example in Adventure 1, the order would change to Country -> State -> City.

Edited July 30, 2018 by Rich