Filtered Value Lists Alternatives

[GisMo]

I've got a situation with value lists that I'm trying to find a "simple" solution for.

 

I have a table of records...300,000+ records. I have two fields of interest, Code1 and Code2. Code1 is a general filter and Code 2 is a more specific filter that has values that ONLY occur for Code1. Code 2 is a child of Code1

 

Example: If Code1 = A then Code2 will only be Red, Blue, Green

OR

If Code1 = B then Code 2 will only be Alpha, Beta, Gamma. 

 

This is inherent in the records. 

 

I need to search for specific Code2 values but I need the value list to be filtered by a Code1 selection, otherwise my Code2 value list from field values is TOO long and non-nonsensical. I've gotten nested values lists to work, but they do not work in find mode. Example, I can pick Code1 from a drop down and then see only the related Code2 values based on Code1. This works only in browse mode. It's also quite slow with this many records.

 

I've thought about extracting the Code1 and respective code2 values and putting them into a separate table and creating the value list based on this table. This will speed things up, but still won't work in find mode. Can you script creating a value list?

 

Anyone have any work-arounds?

 

 

 

 

[Ocean West]

You could use Execute SQL to result in a list of parent IDs from your lookup table then put that in a global field to show the related

values to Code2.

[GisMo]

So, I've gotten as far as running a SQL query to search for CODE1 and return DISTICT Code2 values. Then, I take the result, a variable as a list of text, and empty a table and create new records for each value in the list. Then, I can make a value list based on the table.

 

The would be if there are multiple users, then one user would fill the table with their values and another user could come in and wipe the table and make other values. I'm still working through that concept. Maybe using global values and not wiping the records..work in progress...

[jbante]

Have you looked at the Virtual Value List module?

[GisMo]

I haven't, but that looks great...I might try that out. It would be great if a global variable/field could be assigned to a value list natively.

 

The solution I currently have works ok. I have a new table that holds Code 1 and Code 2 values and if Code 1 does not exist in the table when the user selects it, then it executes the SQL command and adds the corresponding Code1/Code2 values. The user can also force a refresh on the values as well. Then, I simply use "Include only values starting from: " (Code1) in the value list definition. It works!