Large lookup table management

By: Gerry Bolger | Asked: 01/29/2023
ForumsCategory: General questionsLarge lookup table management
Gerry Bolger asked 1 year ago
Hi Thanks to those who have helped me with my POC phase of a form I am getting ready. I now need to add lots more data to the lookup table which covers a number of countries. That data will be circa 2-3000 rows. That will not work for many as to find organisation near the bottom of the list they will give up. So, I wonder is there a way when in conditional logic that i can present a subset of that based on them choose questions, for example which country they are in and what sector they work in, which would limit the lookup offering. Whats the best way of making that happen? 1 large lookup table 2: country and sector type tables, but then how would these connect. I have minimal coding skills to looking for the most painless solution. I attach an image of a high level structure. of what are the high level elements of the look up table. Many thanks Gerry
3 Answers
Victor Font answered 1 year ago
Normally, I would favor the one lookup table approach, but I'm not sure after looking at the high level herring bone structure where the data elements define the breadth and width, or rows and columns as the case may be. Your structure is 3-levels deep. You have multiple countries in many-to-one relationships with organization type, and each organization type has a many-to-one relationship to what I assume are accounting periods based on the Q1 thru Q3 labels. The question that ultimately dictates the design is "What data are you capturing at each of those levels?", "What fields to you plan on using with the lookup table, dynamic or lookup?", and "How do you plan on displaying the data?". From a Formidable Knowledge base perspective, it sounds like cascading fields should be your first approach. You can learn about how to do that here: While you're in the Knowledgebase, please take the time to read through the documentation for dynamic vs. lookup. The difference is what gets stored in form entries as lookup field values. Depending on the field type, it can either be the actual value from the lookup table or the entry id of the row in the lookup table where the value is stored. Storing the entry id of the lookup row allows you to display the value of any field in the row of the lookup table in your views. It offers greater flexibility.
Michael ClarkMichael Clark replied 1 year ago

I have a similar use case, with lookups grabbing related data 3 layers deep. Cascading lookup fields is the way to go. Side note: make sure your server's SQL and PHP environment variables are tuned for maximum throughput (according to max RAM and CPU calculations).

Gerry Bolger replied 1 year ago

Not working for me, and i don;t know why!
I've started a new question

Gerry Bolger answered 1 year ago
Victor - wow thats an amazing feature, which shows why FF is the best plugin. However the way it structures countries and provences is causing a headache for me because in the United Kingdom there are 4 countries, Eng/Scot/Wales/NI - all legal seperate for the majority of things exceot for defece and a few other global aspects. So, I have reformatted the diagram to better help. Also I attach a very small (literally) extract of what my lookup looks like and key is that you will see I have Country as UK and State as England - then for the hospitals etc in wales etc etc, So, when a user selected say Wales, with my model (because the country is associated to the organisation) how would I get that subset (Wales NHS Organisations ) to show rather than what will be a very very long list which won't work? As I see it I could modify the location list to Country Provence to just should UK and 4 countries  - But I did that anyway in my sheet as UK Countries.... any thoughts? Thansk for this, I'm learning heaps  
Gerry Bolger answered 1 year ago
See new question - Cascading form not working

Making the Best WordPress Plugin even better - Together

Take on bigger projects with confidence knowing you have access to an entire community of Formidable Experts and Professionals who have your back when the going gets tough. You got this!
Join the community