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"?
Great question, and I'm really glad you're thinking about this.
Look-ups in large look-up tables can definitely degrade CommCare performance. I would recommend doing some testing with different sizes of look-up tables to better understand the effect this has on your particular app and device.
Your strategy of breaking up the look-up tables into multiple smaller tables is a kind of partitioning, and it's a recommended best practice for creating maintainable, scalable apps. Please see this wiki page for additional details about how to configure the filtering as you describe above: Setup a Question with Filtered Choices - CommCare Public - CommCare Public
My recommendation would probably be to add a Custom User Field called "country" or "country_code" for each mobile user. Then you can compare the value of the mobile user's country to a field inside your loo-up table. You may want to drill down to something more granular than country (perhaps a country / state combination)?
Eventually, you may also want to consider indexing the look-up table fields that you're using for the filtering (i.e. "country"). I will try to find documentation similar to what's above to link you to more information about implementation.
Thanks for your response. Since I received a response from both you and Kishan Sampat on this subject, I’m sending you the same response I just sent to Kishan through the support@dimagi.com address.
First let me say that, after a ton of searching and fumbling around, I did find a solution to my issue. I’m not quite sure how I found it, but I think this process has shown me some gaps in the Help Site documentation for this functionality, and maybe even a hyperlink that is pointing to the wrong page. So that the next person doesn’t have endure what I have, let me share what I’ve found in the Help Site and potentially how to fix it:
Despite all of the pages that have been suggested to me, I never found any specific discussion in the Help Site of what the “Is_global?” flag in the “Types” worksheet of a downloaded set of Lookup Tables actually does.
On the “Creating and Updating Lookup Tables” page, there are two places that mention the “Types” worksheet and offer a link labeled “Advanced Lookup Tables” so the reader can find more information (“Downloading or Uploading Tables for Editing” section and the “Editing Table Data in MS Excel” section). However the only reference to the “Is_global?” flag on this page is in a graphic in the “Editing Table Data in MS Excel” section of the page, with no further discussion of its significance at all. Since “Is_global?” only shows up in a graphic, a text search will not find it.
Turns out those “Advanced Lookup Tables” links lead to a page titled “Using Lookup Tables with Multiple Languages”. Multiple languages is an advanced topic for lookup tables, but it’s clearly not the only one, or the one I’m looking for. As far as I can tell, the only thing “multiple languages” has in common with “Is_global?” is that both are enabled on the “Types” worksheet.
On the “Creating and Updating Lookup Tables” page, there is an interesting side note in the “Editing Table Data in MS Excel” section. That side note refers to assigning rows in a table to specific users or groups of users, which is a sort of what I’m looking to do. But here are the problems:
The following “learn more” link points to a page named “Mobile Worker Groups”. While that page is a great recitation of how to put mobile workers into groups, that page says absolutely nothing (as far as I can tell) about how to then assign rows in a lookup table to those various groups.
As I understand it, CommCare has two methods of sharing cases among multiple mobile workers: creating groups of mobile workers, or through the “Organization” function. Even if the “Mobile Worker Groups” page did explain how to then assign table rows for use by specific groups (which it seems not to), that would leave “out in the cold” all the developers like me who are instead using the Organization function for case sharing. Not good.
One other Help Site page that was suggested to me was the “Setup a Question with Filtered Choices” page. I had already discovered this page quite some time ago, and it was really helpful as I was learning to use several “multiple choice lookup table” questions successively on a form to filter the choices available in one question based upon the answer to a previous question. In fact, it was the lookup tables I was using in this fashion that grew so large that the performance of the related form on my mobile device degraded so badly.
As far as I can tell, this page tells me nothing about how to assign rows in a lookup table to specific mobile worker groups or to specific areas of an organization.
And, we had already established that “filtering” a table in this manner does not solve the issue that a table has simply grown too large for the mobile device to handle. The system still has to crunch all the data in the large table just to execute the filter.
So, after all these dead ends, how did I find a solution? By stumbling across a Help Site page named “Assigning Lookup Table Rows to a Location”. I haven’t found this page is referenced by any of the Help Site pages to which I’ve been directed. And even that page still doesn’t present a complete view of how to actually do what it’s title says. Instead, I had to put two and two together to figure it out. Actually, all I can say is that my app is now working very well on my mobile devices, and here’s why I THINK it may be working:
I’m gathering that the “Is_global?” column is a flag that indicates to CommCare whether to download an entire lookup table to each mobile worker’s device (Is_global? = “yes”), or whether it should look for an additional column in the lookup table designating which rows should be downloaded to the mobile devices of which groups of mobile workers (Is_global? = “no”).
So, if one is using the Organization function to group mobile workers and share cases, an Is_global? = “no” directs the system to look in the associated lookup table for one or more columns named “location 1” or “location 2”, and so forth, as is illustrated on “Assigning Lookup Table Rows to a Location”. And it seems apparent that the system expects to find organization defined “site codes” in those columns.
It also seems apparent that, to accomplish all this, the “Types” worksheet row for the lookup table in question needs to have “Is_global?” set to “no”, and also list the “location” columns along with the other columns for that particular lookup table. Then, the associated lookup table needs to have those “location” columns setup and filled in with “site codes”.
While this is somewhat akin to filtering a lookup table, it’s different in that it involves a system-level “filter” that dynamically reduces how much of a lookup table is downloaded to a particular mobile worker’s device based upon the part of the organization to which the mobile worker has been assigned, thus improving application performance.
I’m guessing there’s probably even more to be known here and that I may have not gotten something exactly right. But the reality is that, based upon this “educated guess”, my app is now working beautifully again on my mobile devices, even with the lookup tables having grown to such a large size.
Side note: developers using mobile worker groups, rather than organizations, won’t be helped at all by this solution. However, I suspect “Is_global?” works similarly for them, only they probably need to create columns in the lookup table with a designation other than “location” so that the system knows to look for a mobile worker group identifier rather than a site code.
The point is that I shouldn’t have to work so hard to come up with my own “educated guess”, and I don’t think anyone else should have to either. Here’s what I suggest would be a good fix for the Help Site:
The “Advanced Lookup Tables” links on the “Creating and Updating Lookup Tables” page really need to point to newly created “Advanced Lookup Table” page that introduces the topic appropriately, and then offers links that lead to pages covering the following topics:
“Using Lookup Tables with Multiple Languages” – (already exists, wouldn’t change a thing about it because it’s already very effective at helping a developer get up-and-running with multiple languages in lookup tables)
“Managing the Impact of Large Lookup Tables on Device Performance” (a new page explaining how large lookup tables can significantly decrease device responsiveness and that the “Is_global?” column in the “Types” worksheet can be used to switch on a system-level function that dynamically reduces how much of a large lookup table is downloaded to a mobile worker’s device based upon some developer-defined criteria). This page would have links to additional pages describing in better detail exactly how to do this:
“Assigning Lookup Table Rows to One or More Locations Specified by the Organization Function” – (the existing “Assigning Lookup Table Rows to a Location” page would be augmented to cover exactly how to get this to work:
How Is_global? works with Organizational data
How does the “Types” worksheet need to be altered for the lookup table in question
Hoes does the lookup table in question need to be altered
“Assigning Lookup Table Rows to One or More Mobile Worker Groups” – (this would be an entirely new page to be written, but I’m guessing it would be laid out much like the previous page for persons working with Organizations)
Wow, that was a lot to type, but I hope this is helpful to you guys. I realize that CommCare is not a huge organization, and there’s only so many people to create Help Site pages and to support users. My hope is that this makes it easy for you guys to make the Help Site to be more helpful, and that this might free up your support team resources to tackle other topics.
I have been working recently with some very large lookup tables and users organized in locations. I have been frustrated with the lack of detailed documentation on this, and have had arrived at essentially the same questions and educated guesses as @kevin36. It sounds like you might have continued the conversation with in a support email chain, but I'm really curious about the outcome of that discussion and want to know if the answers to Kevin's questions can be posted here. Or if there will be any documentation added on these topics to the wiki. Since this was almost 4 years ago it seems like maybe it won't be, but I think that would still be very a useful addition.