Best approach to present many records

Finishing up my project, I have located a Filemaker table that contains all the NAICS codes available that I have brought into my DB file. It's a huge list with more than 20, 000 records. What I'd like to be able to do is have the user pick from a drop down list that is organized by first by Industry Title, and then show the industries that are available under that title.

Industry titles are prefaced with a certain number. For example, Agriculture, Forestry, Fishing and Hunting all fall under the codes that begin with 11. You can see an example of how this is organized from the NAICS website here:

So my question is, what would be the best approach to handle this? I'd like for my value list to first show the 20 industries and then based on that selection, be taken to a list that only shows the titles for that industry. I know I can use conditional value lists....but can you make both selections from the same field or do you have to have two fields, one for the industry and then the second that populates with the titles for that industry?

Thank you!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

My first question is whether the Industries (which would be your first pull-down field) and the Titles (your second pull-down) contain the same type of information or different types. If things are as you describe, then your database should contain a field called "Industry" and another field called "Title". Why would you want these two distinct pieces of data in the same field? Even if you could combine them for this purpose, doing so will cause further problems elsewhere. As you suggest, you can get the desired result with a conditional value list based on a global field, e.g., "gSelectedIndustry". Define a value list consisting of the "Industry" field in your database, and put a pull-down field on your layout that populates gSelectedIndustry from that value list. Define a self-join relationship between gSelectedIndustry and Industry, then define a second value list, "Title", conditional on that relationship. Populate another global variable ("gTitle") from that value list, and you'll get all the records that match both selections. If you need to, you can repeat this process as often as you want, eventually selecting a specific record if you'd like to do that.
Will LovingPresident, Dedication Technologies, Inc.Commented:
Here is a small demonstration file that uses the NAICS codes and demonstrates how to select first the industry and then the code. A few notes:

1. You need a separate table of Industries, it's possible to do it with the industries contained in the Codes table but it's a little be cleaner with separate tables.

2. Since each Industry has a two number codes which is also the first two numbers of each code, you need a calculation field called "IndustryPrefix" = Left( NAICS_Code ; 2 ). You can use this to relate the Industry table to the Codes.

3. I've created two value lists, one for Industries, one for Codes. These are applied to popup menus. Note that the way the values list are constructed, it's the Code ID that is being saved (the number) but only the name of the Code and Industry actually show. This is nice for users unless you specifically want them to see the number (you can edit the value lists so it shows if you do).

4. I've set an Auto-Enter calculation for the NAICS_Code field to check each time an entry is made that the Prefix for the code matches the selected industry (a global selection field). If the Industry number and the two-number prefix for the code do not match - e.g. if someone selects an Industry and Code and then decides to change it later - the original code for some other industry is cleared.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_Astrid_Author Commented:
Wow, thank you so much Will for taking the time to set this up! This is extremely helpful in helping me understand how to handle this.

THANKS! :) You made my week!
_Astrid_Author Commented:
Will has been incredibly helpful in so many ways. Not only does he write clear notes on techniques, but he has taken the time to create example files that make complex set ups so much clearer. Thank you!!
_Astrid_Author Commented:
Thank you also, historychef! I had gotten partially set up following your instructions.....thanks for the guidance. It would have taken me forever to wrap my head around this!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.