Using Repeats in XLSForm with Google Drive and Aggregate

I am trying to survey few households in my area. The logic is to collect the details of house like houseno,wardno and then the number of members present in the house and then to collect the details like name,age and sex of the all the members.
To make the same possible came to know about the repeat feature in XLSForms.
i.e Once entering the basic details of house and no. of members a repeat function is called based on the no. of members to collect the name, age and sex of the members.
Now tried running the Form using Google Drive and Aggregate and in both the instances encountered few errors as below.

  1. While using ODK Collect & Google Drive
    While submitting data to Google Sheets from ODK Collect the error thrown in ODK Collect was member_count is an invalid column name in your form. Came to knew that though i had not created any column name with an _ , the online ODK XLSForm creator actually creates a member_count tag in the xml file which caused error in creating the column names in Google sheets.
    I was able to resolve the issue by editing the xml file and renaming the member_count tag as member-count tag.
    Eventhough i was able to resolve the issue, the data submitted from ODK Collect stored only the final repeat value in the row.
    i.e for a househould, i gave basic details and member count as 2 and then entered name age and sex of two person and submitted. but in the google sheet the data present was the basic details and data of the second person.
    How to resolve this issue? i.e i need the basic data along with all the repeat group datas in the same google sheet.?

  2. While trying to use the form with Aggregate, it works fine, but again the data of the repeat groups are clubbed as a separate entity and in csv export also it comes as an external data?
    Is there a way to combine the repeat data along with the basic data and present as a single csv.

Households.xml (3.7 KB)
Attaching the xml file and xlsx file for referenceHouseholds.xlsx (13.9 KB)

Google Drive has a few limitations.

  1. No repeats (see Repeat groups and google sheets upload - #11 by LN)
  2. The form must have less than 254 variables. Any non-group row (e.g., text, integer, select_one, geopoint, calculate, date) is considered a variable.
  3. Only alphanumeric characters (123ABCdef) and dashes (-) are allows in the name column of the survey and choices sheets.

@adammichaelwood can you please make sure this is documented?

As far as repeats behavior of Aggregate, there is currently no easy way to flatten the data, @mistcrrgpsa. You can stream data to Fusion Tables and that will flatten it. Or export to CSV and flatten as you wish. I don’t see this filed as a feature request yet. Perhaps you can file it?

Thank u Yaw,

At the same time i would like to suggest the below.

As the repeat is part of the succeeding questions, there can be a login in
repeating the master data for the repeat questions if the user wishes for
the same.

i.e Lets say in my case the parent data is Local Body Name, House No, Ward
No and No. of Members and then comes the repeat data about the members i.e
name, age and sex.

In this context i dont mind the parent data being replicated for each
repeat row, if a provision for the same in provided while creating the form.

The scenario is explained as below

Current Scenario

Datasets in ODK Collect
LB Name Ward No House No No. of Members
Adat 13 1345 2

Repeat Members Data

Name Age Sex
A 18 Female
B 21 Male

In Google Sheets it shown as

LB Name Ward No House No Members
Name Age Sex
Adat 13 1345 2
B 21 Male

Proposed Scenario in Google Sheets

LB Name Ward No House No Members
Name Age Sex
Adat 13 1345 2
A 18 Female
Adat 13 1345 2
B 21 Male

Is this possible to implement in Google Drive may be as an optional feature
for user to select.

Thanks & Regards,Anup Krishna P,Specialist MIS,State Program Management
Unit(SPMU),Rasthriya Gram Swaraj Abhiyan (RGSA),Local Self Government
Department,Govt. of KeralaPh: 94960 46910

As the repeat is part of the succeeding questions, there can be a logic
in repeating the master data for the repeat questions if the user wishes
for the same.

Thanks & Regards,Anup Krishna P,Specialist MIS,State Program Management
Unit(SPMU),Rasthriya Gram Swaraj Abhiyan (RGSA),Local Self Government
Department,Govt. of KeralaPh: 94960 46910

This feature is in ODK Aggregate server and you are using 2 methods one is ODK Aggregate and second is google drive. Use ODK Aggregate and then append all data in a one CSV file with the help of Stata and then export that into excel and put that on google drive. This is solutions which I can give you or Google is always there.

Narendra

1 Like

If you would like to see repeat support in the Google Sheets integration, please vote and comment at Repeat groups and google sheets upload.

A post was merged into an existing topic: Repeat groups and google sheets upload