Hi, I have a lookup table that has now exceeded 3850 rows since we expanded our coverage from one country to several. I think the sheer size of this table may be related to a degradation in responsiveness on our mobile devices in and around form questions that reference this rather large lookup table.
I’ve looked into the help pages about splitting large lookup tables into two. I think I understand how to mechanically split a table, but those help pages are sketchy at best in explaining how to dynamically reference the correct table out of two or more “split” tables.
Maybe some specifics from my situation might help illustrate the challenge. My large original table named “district” contains all of the districts in each state in India, as well as all of the counties/parishes in each state in the United States. It seems obvious to split the table into two, one for the districts in India (perhaps call that split table “districts_india”), and the other for counties/parishes in the U.S. (perhaps call that split table “districts_us”) What’s not obvious to me is how I would write an instance statement that would direct a lookup table question on a form to query the “districts_india” split table table vs. the “districts_us” split table.
One strategy I’ve thought of is to leverage the national_id (or its associated name) to which each user is assigned in my organizational hierarchy. So, if the mobile user is assigned to India, then the form question would query the “district_india” split table, and if the mobile user is assigned to the U.S. then the lookup table question would query the “district_us” split table. Seems logical, but perhaps easier said than done. How in the world can I work national_id, or something like it, into an instance statement as a variable that would dynamically determine which split table would be queried? Keep in mind that I need this method to be scalable beyond just these two countries. I can’t be rewriting code just because our organization entered another country.
Any wisdom on this from anyone out there in the “CommCare nation”?
Thanks in advance,