Sorry for late Reply, just returned from my summer holiday.
I implemented ODK for a NGO that is supporting refugees in Lebanon with various activities like School-Bus for children, Teaching in Embedded community Centers, aid Distribution and also case Management for families that Need Special Support because of medical, education, financial or whatever reason.
We have 4 productive devices and 1 for testing/development... so it´s a small scale so far. Aggregate runs over a Google Appspot (GSuite... the professional account that ensures privacy) and we use a Device Management. We have 2 different GSuite Accounts, one with very limeted Access for Administration and Storage of private data as FusionTable in the Google Drive. The Devices are registered on another account where only the required data for preloading (.csv Files) is stored on the drive as anonymized as possible.
There are 3 forms productive so far:
Event Reporting: if there is a Distribution, an education Event or so we make a GPS-Pin, Description, Sponsor if somebody donated for this, type of Event, Distribution to a case is also an Event. We preload some Event Types and other Select fields fo make it configurable. And we preload a list of Cases and Settlements. That works well because the preloaded values don´t Need to be modified.
Settlement Assessment: We go through the Settlement, collect names of the Families, Needs, Infrastructure like if they have a toilet, We can select which aid they Need and which Projects are possible in this Settlement. We Preload Settlement "Names" (P-Codes defined by UNHCR) to have data consistency and some select-fields. We do not preload personal data as this changes too often so we make an assessment always from a more or less empty form. This is working well.
Case Assessment: This consists of questions in the fields: Health, WASH (Hygiene), Shelter.... to calculate a vulnerability score and to coordinate which aid this case needs. First this is done on an empty form, then over the time we Support this case (Family) we Need to do at least 2 Follow-Ups where we go through the questions and modify if needed. At least on a monthly basis. And for this we Need a preload that can be modified.
We want to use the same form for "new cases" and "Follow-Up" as it is the same data and shouls be stored at the same place. so it would be the same form. Steps to solve the case like distributions or medical Support will be tracked with the "Event Report" form, we can link it afterwards with FusionTables.
Now the Problem with the pulldata in "calculate" is that it´s called twice, fist when opening the form which is absolutely fine and second when the form is finished. with the second call it overwrites the edited values.
In an ideal world we could call if-functions and pulldata in the "Default" value field in the XLS-Form. Another Option would be to have a flag that disables the second call of the "calculate" field when finished.
Still the data can be locally stored and manually updated, so it does not Need a direct Connection to the Database.
Puh, i hope this made more clearly what is needed, thanks for reading and thinking!