Build some kind of mental model for external data

lognaturel commented 7 years ago

"External itemsets": https://opendatakit.org/help/form-design/external-itemsets/ (http://xlsform.org/#external-selects)
- Very narrow. Allows for building a CSV in the same structure as the choices sheet that gets imported to a database to speed up large lists.
"Pulldata": https://opendatakit.org/help/form-design/data-preloading/ (http://xlsform.org/#pre-loading-csv-data)
- Function that can be used in calculations to pull a cell from a CSV.
"Search appearance-function": http://xlsform.org/#dynamic-selects-from-pre-loaded-data
- Function used in appearance that pulls a column from a CSV to use as a choice list.

These have overlap in functionality.

The XForms spec-compliant way to do all this and be able to use XPath for querying is https://opendatakit.github.io/xforms-spec/#secondary-instances---external.

Let's punt on this until we have a rough plan of how to resolve some of these historical challenges.

yanokwa commented 6 years ago

One gotcha that we should document. We only support select_one_external. select_multiple_external does not exist.

adammichaelwood commented 6 years ago

Adding this note here so it doesn't get lost...

This is from the old "gotchas' document:

external itemsets do not filter properly when using integer field values Excerpt from opendatakit@ contribution by Hung Pham:

Using ODK's south sudan example, I was encountering an issue with filtering for external select function in xlsform.

After some testing and examining xml & csv files, I realised that ODK Collect's select_one_external won't work if you have your filter as numeric values (e.g. 1, 2, 3, 4, etc) . I figured this out after changing column "name" 's value in South Sudan example (from ODK Help) to numeric value instead of short alphabetic values, then test it on ODK Collect and as expected, no choice items were displayed after "state" . So I went back to check itemsets.csv files and found that all numeric values are converted in to decimal format (e.g. 1 = "1.0", 2 = "2.0", etc.) . (find attached files)

So that means, unless numeric values for name include ".0", ODK Collect won't be able to display cascaded selection of choices. A quick work around that I used was to remove ".0" in itemsets.csv file. This has work like charms.

To conclude: 2 ways of fixing numeric value sets for select_one_external -

make value set in column "name" - decimal. Or remove ".0" in itemsets.csv file after compiling from xlsform.

lognaturel commented 6 years ago

The spec-compliant, cross-ecosystem approach to external data is https://opendatakit.github.io/xforms-spec/#secondary-instances---external. That's not really used because it's cumbersome to generate an XML file in the proper format (it's something a server should really do and/or XLSForm support should be provided https://github.com/XLSForm/pyxform/issues/176). Collect doesn't currently optimize reading of the XML files so it's pretty slow.

The two CSV-based approaches are hacks that have the same goal but were added by different groups. They're fast because they use a database.

MartijnR commented 5 years ago

proposal for formalizing CSV support (a way forward): https://github.com/opendatakit/xforms-spec/issues/88

getodk / docs

Build some kind of mental model for external data #73