Repeat groups and google sheets upload

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?

1 Like

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).

I hope this helps.

2 Likes

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.

1 Like

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.

thanks

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.

Thanks for the good discussion about unique repeat identifiers, everyone. I think it would be ideal to match the Briefcase behavior. Briefcase specifies KEY and PARENT_KEY columns for each repeat. the KEY is the uuid of the instance followed by the XPath path of the node. The PARENT_KEY is the parent's KEY.

I think this is important enough that it should go with the initial release of the feature. Otherwise users who start using it will have partially-filled key columns added later which will be confusing.

Does that work for everyone?

1 Like

If you want I can prioritize it. Assuming I finish work on the code tomorrow it's possible to release everything together on the weekend.

2 Likes

Awesome, @Grzesiek2010, that sounds great to me!

Ok, my one additional idea here is that since we are going to create keys for that groups (adding columns KEY and PARENT_KEY), we can do the same for the main level itself - I mean using that instead of instanceID because now if the instance doesn't have instanceID it can't be uploaded what is a limitation. Do you agree?

2 Likes

Great idea, @Grzesiek2010, it will be good to remove another limitation. To summarize: for the top level record, KEY will be a duplicate of the instanceID column if it exists or a UUID that Collect generates at upload time if instanceID doesn't exist.

Perhaps you just save the UUID used as the instanceID? Otherwise when people use other things like Aggregate or briefcase you can end up with duplicates?

Generating a uuid would only happen in the case where the form definition doesn't include instanceID. Then the KEY of the row in the Google Sheets top-level sheet would correspond to that uuid.

@W_Brunette are you suggesting that Collect should alter the form definition to add instanceID? I think that would be required to also add it to the records.

How about we make the documentation state that forms without an instanceID are supported with Google Sheets upload but that if no instanceID is specified it may not be possible to detect duplicates there or submitted to other servers? I think it's going to be very rare anyway because a user would have to manually generate or alter XML to end up without an instanceID.

@LN, documentation is a fine solution. This was a problem we had years ago it was kind of solved by most form generators just adding instanceID automatically for most users. We took extra precautions in briefcase and aggregate. I am just trying to be helpful and bring up problems of history past.

2 Likes

Thanks to everyone who has contributed to this discussion! Google Sheets upload supports repeats as of ODK Collect v1.14.

3 Likes