Thanks for your response.
So, a bit more detail (simplified for ease of understanding below):
- I have 13 forms in total, call them Form A: Form M for simplicity. Form A is a case registration form, and Forms B to M are case list forms.
- Only forms G and H have repeat patterns in them.
- In Form H, I have questions such as:
- Form H additionally has has a question
h0010_clinic_visit (“How many times have you visited the clinic”?)
- There is then a repeat loop, with questions
h0019_dateofvisit, that ask details of each clinic visit.
- There is a similar pattern in Form G, but none of the other forms have repeat patterns.
In the CommCare export Excel spreadsheet, I have sheets for each form, Form A to Form M:
- In the sheet for Form H, I have columns for
Source Field and
Field for questions
- I additionally have entries for the repeat loop questions
- There is a similar sheet for Form G.
I run the CommCare export tool to update the SQLite database using the following code:
commcare-export --output-format sql --output sqlite:///prospect.db --project prospect-1 --query prospect_extract.xlsx --username USERNAME --password PASSWORD
This updating has worked very smoothly until I added sheets for Forms G and H with repeat loops to the export spreadsheet.
Now, when the database is updated, I see the following (when using DB browser, and same when imported into R):
- Some fields on Forms G and H are missing, despite being included in the Excel spreadsheet (e.g.
h0009_severity are both missing.
- The variable names for the repeat questions have the format:
h0010_clinic_visit.h0011_clinic_name, but don’t seem to have any data for instances when there was more than one repeat loop completed.
I have tried making separate Excel sheets for the only the repeat loop questions in Forms G and H, but:
- Fields still remain missing in the main Form G and H tables
- I can’t see a way to link repeat loop fields by case_id
Very grateful for any suggestions