Server datasets and preloads

A preload is data a form reads during the interview: the household's name from a sample list, last round's answers, the price of a crop. Joripo reads preloads from CSV files attached to the form, and keeps shared ones on the server as server datasets.

Reading a preload

pulldata('hh_list', 'head_name', 'hhid', ${hhid})

reads the head_name column of the row of hh_list.csv whose hhid column equals the answer to ${hhid}. Column names with spaces work. Choice lists can come from the same data: select_one_external reads its choices from an attached CSV, and the search() appearance builds a question's choices from one.

The three things a preload needs

A preload only works when all three line up, and if one is missing every preloaded answer arrives empty, which in the field looks like the survey working:

1. the form calls pulldata('name', …) (or search('name', …));
2. a file name.csv is attached to the form, or a server dataset with the ID name is attached to it;
3. the name the form uses and the file's name agree. Joripo matches them without regard to letter case.

Each deploy names any preload file nothing has attached. The tester reads the same attachments a phone downloads, so if the preloads work in the tester they will work on the phone.

search()

The search() appearance takes its full SurveyCTO-compatible form: search(file), search(file, type, columns, text) and search(file, type, columns, text, filter column, filter text), with contains, startswith, endswith and matches. In Joripo's runtime each value is listed once, a fixed choice on the choices sheet (such as -99 Other) is kept beside the rows from the file, and a label may join several columns. ODK Collect reads the same file through a plain list, so on ODK Collect every matching row is listed, without the fixed choices. A malformed search() is refused at upload.

Server datasets

A server dataset is a table held on the server and attached to any number of forms, so a list kept in one place reaches every form that needs it.

• Create one on Design with Add server dataset: a .csv or .xlsx file and a unique ID column, whose values must all be present and different. Files must be UTF-8 text; a file in another encoding is refused with advice on how to save it.
• Attach it from a form's Attachments panel, or tick it when uploading the form. The form's phones download it as <dataset ID>.csv.
• Update it by uploading new data, in one of three ways: replace what is there, append the new rows, or merge on a column both have. Every form it is attached to gets the new data at once, and phones get it the next time they check for updates.
• Detach takes it off a form (on Datasets, beside each form that uses it). A dataset no form uses can be deleted; the workspace's case list cannot be while it is the case list.

Publishing form data into a dataset

A dataset can take its data from a form, so one form's answers become another form's preloads: a listing form fills a household list, and the follow-up form reads it with pulldata().

• On the dataset's card choose Publish from form and pick the form.
• Under Fields to publish, Add all adds a row for every field of the form (outside repeat groups), each published to a dataset column of the same name; Add adds one row at a time. In each row choose the form field, change the dataset column if it should be called something else, and remove rows you don't need with ×. KEY (the submission's ID) and SubmissionDate can be added too.
• Update says what happens to a column when a submission updates its row: Replace (the default) writes the new value; Add adds it to the number already there; Concatenate (after) or (before) joins it to the value already there, with the separator you choose, up to 255 characters. A field that was blank (not relevant) in the submission leaves its column as it was.
• Form field to identify unique records decides what each submission does: a submission whose value is already in the dataset updates that row; a new value adds a row. Left at None, every submission adds a row (a column fed by KEY keeps the same submission from being added twice). A submission with that field blank is skipped, and the activity log says so. A dataset with a unique ID column always publishes on that column.
• Include submissions only when this field is 1 publishes only the submissions whose answer to that field is 1.
• Tick Publish existing data to fill the dataset from what the form has collected so far.
• Each new submission publishes as it arrives. Every form the dataset is attached to gets the new data at once, and phones get it the next time they check for updates.
• With the form's review workflow on, a submission is published when it is approved, and published again if an approved submission is corrected.
• Several forms can publish into one dataset. Each one publishing is listed at the top of the panel: Edit opens its rows again, and Stop publishing takes it off; the rows it published stay.

The case list is updated from submitted data by its own mapping (Case management), and a garbling scheme's enumerator sheet changes only from Garbling, so neither can be published into this way.

Lists that name people

A dataset that names people (a household list with names, phone numbers or addresses) can be marked Identifying data: tick Contains identifying data when you create it, or use the Identifying data button on its row. Then only people with identity access to the workspace can download it, preview it from the form's attachments or find it in the attachments .zip, and every download, given or refused, is recorded. Phones and web forms still receive it with the form, because the form needs it. Only an owner, or a manager with identity access, can mark or unmark a dataset, and the mark stays when new data is uploaded.

The enumerator list

A form with an enumerator question reads its list of enumerators from the attachment enumerators.csv, with the columns id, name and, optionally, users (the sign-ins each enumerator is listed for). Make it a server dataset with the ID enumerators and attach it: anyone who adds themselves on a phone is then added to it when their form arrives.

Very large files

A preload file over 25 MB is sampled to its first 50,000 rows in the tester, so the page stays responsive; the tester says when it is sampling. Phones always get the whole file.