Hi Veethahavya
Briefcase is a Windows desktop tool supplied by ODK to allow you to process
forms taken from a phone or tablet and either produce csv files or process
the forms onwards to ODK aggrigate. The briefcase tool produces a csv file
for responses to a given survey, and another csv file for repeats with a
linking field to the survey entry number of a response.
Ideally a new development could produce the same output produced by
briefcase but put onto 2 tabs of a Google spreadsheet (one tab for survey
response, another tab for repeats) when submitting forms to Google sheets
rather than to Briefcase.
The part about Briefcase was very easy for me to understand. However, I do not wish to use ODK aggregate and stick to google sheets integration. So, please correct me if I am wrong. To take in repeat entries, all I have to do in google sheets is create a new tab called "repeats"?
If your surveys are going to Google, then you cannot use repeats (repeats
only work with Briefcase or Aggregate).
This was a request from me to the development team to think about adding
the ability in a future release for submissions to google to allow repeats.
To be clear - it doesn't work today. Hopefully it will work in a future
release.
Is it possible to work around the problem, to create a calculated field in the main form in which the repeat data are serialized then processed by a macro-like a Google sheet?
That's an interesting idea. Unfortunately, it's not currently possible because Collect does not allow forms that contain a repeat to be sent to Sheets!
At the same time i would like to suggest the below.
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.
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 House No Members Name Age Sex
Adat 13. 1345. 2 B 21 Male
I.e the last record of repeat is stored
Proposed Scenario in Google Sheets
Scenario-1
LB Name Ward House Members Name Age Sex
Adat. 13. 1345 2 A 18 Female
Adat 13 1345. 2 B. 21 Male
Scenario-2
LB Name Ward House Members Name Age Sex
Adat. 13. 1345 2 A 18 Female
B. 21 Male
Scenario-3
LB Name Ward House Members meta-id
Adat. 13. 1345 2. Uid-233444
separate sheet repeats with repeat values
Name Age Sex. meta-id
A 18 Female Uid-233444
B. 21 Male. Uid-233444
Is any of the above option be possible in ODK collect with Google drive, Google sheets
Aggregate "used' to be able to do repeats into google fusion tables. I would suggest trying that out to see if it still works. (Changes to APIs could have caused it to break, I haven't tested it recently)
In continuation of the earlier communication and going through the Features
that Google Drive and Google Form Offers, would like to recommend an
enhancement in the current facility that ODK Offers in integrating with
Google Drive.
Lets say a scenario where i have 100 entities to be mapped by 5 persons and
the data includes images to be stored in Google Drive. Currently the
provision available in ODK is to share the XML form to the 5 users and give
write privilege to the result sheet to all the five users and then once the
data is capture and submitted by the 5 users then the images are uploaded
to the Open Data Kit Folder created in individual users Google drive and
tagged using Google Photos.
Now here the end user gets control over my project data. Time being what i
have done is i shared my Open Data Kit folder to all the 5 other users and
hence all the data submitted by them is being store in my submissions
folder. But still they have access control over my folders.
Suggested Solutions
While submitting Images from Google Forms , A submission directory is
automatically created with the field name of the data in the same directory
location of the results sheets and all the collected data is stored in the
same folder. This seems a better strategy to collect data using ODK Collect
and Google Drive.
Also i suggest that Google can change their access permission levels on
Drive Contents from
Can View and Can Edit, to Can View, Can Edit, Can Write/Can Submit, which
will help safe and consistent data capture using ODK Collect and others
incorporating tools.
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
I also use Google Drive. The obvious answer to get the permissions Granularity is to use Aggregate. I understand this is not always possible or practical.
As a work around to the same problem you could use Zapier (or MS Flow or ITTT) to simply copy each new row added to a different sheet (where only you have access). Same applies to copying new Photos.
This isn't a perfect work around, but may allow you to ensure you have moved any submitted data to a file only you have access to.
Oh man I'm so excited to see this happening. It will really improve the user experience making the data gathering flow much easier in the field, for us Google Sheets users
i have tested the ODK Collect v1.14 Beta with google sheet on two instances they they all work perfectly for me. below are the instances i used it on
single repeat group
repeat group inside repeat group.
but i have a suggestion with regards to the instanceID, the instanceID for the main data is the same to that of the repeat group instanceID which is perfect. but can there be another ID which could be uniquely identifying the entries in the repeat group sheets since they are all using the main data instanceID. like if we could have something like, key and parent kay where the parent kay will serve as main instanceID and the key will be for the repeat group entries id.
example: current situation
main instanceID
uuid:0818e2b8-427c-4693-b8b4-0b2a18d0dd6e
Interesting suggestion, @Fabla! Can you say more about what you want to do with that? Is it because order is important? Is there a particular kind of analysis it would enable?
I think I know what @Fabla means. Your problem is that having nested repeatable groups the main one has instanceId column which is ok but the child one also has only that column so you can't be sure which main repeat it refers to. Am I right?
I think the instanceID column on the repeat tab refers to the PARENT Instance ID. The repeat rows do not have a unique ID of their own.
An easy work around way to do this (assuming this is to give a unique ID per repeat instance that is still tied to the parent?) would be to:
-Add a couple of columns at the end of your repeat tab.
-In the first column use the ROW formula to get the spreadsheet row number,
-In the second column use CONCATENATE to add the instance ID, a "-" and the previous column value (the row number).
This will give you unique references for all repeat instances; if this is what you are trying to achieve? You could either do this manually from time to time or script it within your spreadsheet to put the formulas in where parent IDs are populated (so as not to break the sheet for future submissions).
In some cases, it's possible to fix the problem by building an appropriate form let's assume we have a form for collecting countries and their states, so we have two nested groups. One group is for countries and one for states and you can inject selected county to the second group treating that column as an ID. But generally, I agree it would be good to another column for each repeat group which contains another repeatable group inside.
thanks everyone for your response, you may released that each repeated question will have a responses, all that i was thinking was that, if for each response a unique id is created for it which can be identify. giving the same instanceid to the same repeated group responses will not be clear if you want to deal with one of the repeated response. that is why i suggested this. i know it can be done by creating addition column in the xlsform or xform with concatenate, but how many people can do that to create a unique id for the repeated groups.
Generally, I agree but I think it's not as important to work on this now. We are going to release v1.14.0 this week so adding that in the next release sounds good to me.
