My app collects state, city, and school data. I have one table (I know zoho creator calls them forms) with a single field and 51 rows - one for each US state plus DC. I have another table with state (lookup) and city. I have another table with state (lookup) and school. My actual user input form has (obviously) state, city, and school. The city and school fields are picklists that get auto-populated from an "on user input" script on the state field that looks like this:
if (count(City[State == input.State]) == 0) {
clear City;
clear School;
}
else {
for each r in City[State == input.State] {
City:ui.add(r.City);
}
for each r2 in School[State == input.State] {
School:ui.add(r2.School);
}
}
It works great if I have just a few rows of sample data in each table. But when I loaded 350 cities for one state, and 750 schools for the same state, the functioning of the data input form screeched to a halt. When I select the state in question, the "spinning arrows" to the right of that field do their thing for 30-60 seconds before the city and school picklists load. Sometimes, I get an "unresponsive script" error from the web browser.
I realize that 1,100 rows (350 + 750) isn't a trivial number, but it's far from large by normal database standards. Why does it run so slowly? Is it the query on the database end or the http transfer of the returned rows? Or is the deluge script not written efficiently?
I can't get this form to run normally with just one state's worth of data loaded. I don't know if it will function at all when I get all ~31,000 rows of city and school data in there to cover the whole country.
Any advice on how to get this to work would be greatly appreciated! My backup plan is to just give up on Zoho and find a decent SQL / PHP programmer.
Thanks!