Jump to content

Filtered Value Lists Alternatives


This topic is 2943 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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


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?





Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 2943 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.