Indexed-repeat used in a repeat nested in a repeat returns wrong value

Hi community,

I have been looking extensively for solutions but can't find any, so I will try to explain my issue here and hope someone can figure out what is wrong. In advance thanks!

What is the problem? Please be detailed.
So. I am building a XLS form for fisheries catch monitoring. Several catch types are selected (i.e. tuna, mackerel, etc), then there is a set of repeated questions for each catch type selected (let's call it "catch_repeat"). Within this repeat group (catch_repeat), I have a nested repeat for individual lengths of each fish (length_repeat). Now this length_repeat question is repeated based on the number of fish per catch type, max. 5.
Now my issue is that the form works fine as such, expect that when publishing to GoogleSheets, the parentID for the length_repeat does not link to anything - I would assume it would link to a catch_repeat specific ID (for each catch type).

So, as I tried to fix that, I created a unique ID for each catch type in the catch_repeat (based on fisher name, time fishing etc).
Then I am using indexed-repeat in the length_repeat so that it returns that catch_repeat_ID, and I can then link the length_repeat data with the catch_repeat data on GoogleSheets.

Now after submitting data, and published on GoogleSheets, the length_repeat_ID that is returned for each length does not correspond to the catch type. Screenshot below (highlighted are what should be the different lengths of 3 catch types, however we can see the repeat_ID is not the same per catch type). It seems it is looping through the different catch_repeat_ID available, and I don't understand the error - is that due to the jr count setting for length_repeat? That it is a nested repeat?

Any help would be highly appreciated!

Hi @Effy

sorry for the late response...

Do you mean that values in the PARENT_KEY don't equal any value in the KEY column of the parent sheet?
if so please attach your form for testing.

PARENT_KEY and KEY columns are added automatically, It should work out of the box and you shouldn't have to add such key pairs on your own.

Hi @Grzesiek2010 !

Thanks a lot for your answer. Indeed the parent uid in the length_repeat tab (repeat nested in repeat) does not match any other key in the other sheets, hence why I tried to create another ID.

Attached the XLS form I've been working on. Com_ODK_Boatcatch_monitoring_2019_catchrepeat_v1.xlsx (103.8 KB)

Effy

I downloaded and tested your form. Are you sure it's a form you tried?
I'm asking because:

  1. You have a group with a field-list appearance which contains a repeat group what is not allowed and I needed to remove the field-list appearance.
  2. As I said above you use a repeat group inside a regular group what caused problems on v1.19.0 and older versions, which we have fixed recently and it will be available in v1.20.0 https://github.com/opendatakit/collect/issues/2896
    so to test your form I did have to use a beta version which includes the fix.
  3. The third issue was too long sheet title - we create another sheet title for repeat groups. The name is based on the form name + group name and it can't be longer than 100 chars. Your form name is long so it caused problems and I had to change it as well.

Finally, I prepared this form:Com.xml (41.4 KB)

I tried to send your form and analyzed keys. They seem ok to me. Take a look:
https://docs.google.com/spreadsheets/d/1Ub8WR7mL_e1e5Kx_xf5t_FPdN_ZzyTahOQh5IkwTgVY/edit#gid=892552864

I added colors to highlight the same values.

1 Like

Thank you so much @Grzesiek2010 !

I might have sent you an earlier version of the form... Though I made sure to test it before sending out, but sorry if I've sent the wrong version. Indeed I had the error of the group with field-list appearance containing a repeat group - so I had removed the field-list appearance as well.

I'll make sure to download the latest version. It seems the different variable names are created differently - before the group names did not appear.

Taking advantage of the opportunity, I was wondering if the groups we are using are relevant/necessary? Eg the "fisher agree" group. I feel like this does not bring anything to the database/form, considering how data is laid out on google sheets and that we use one question per screen on ODK (questions not grouped on one screen).

Many thanks again!

Effy

Regular groups without field-list appearance are usually used just to make the xls form more readable I think. It's not necessary.

@Grzesiek2010... I still encounter the same error (parent uid not linked to previous repeat id) even with the v1.20 ?! Attached the modified form I have used (I removed the groups).
Com_Boatcatch_2019.xml (62.4 KB)

The spreadsheet you shared also looks different (key/parentkey) and not uuid.

Am sorry if being a bother, but I don't understand where the issue might come from. Thanks in advance!

Effy

Could you attach a link to your form to show your results? In my case again everything seems fine:
https://docs.google.com/spreadsheets/d/18eH-UdmC-pAsIIMuYtLLYhdr3w3lPzY6FxCylY3vaJE/edit?fbclid=IwAR2GIAbFWggZM9NFuSiC-PhzfVy6kazjuCGsS-L6R5wvVmwoFj-0sAtsvRg#gid=11376083

It is as if I still have the older ODK version? Although I downloaded the v1.20 and it looks different on the Android (than the previous version). Can that be an issue with the publishing to Google Sheets from the appspot?

Below the GoogleSheet

Where are your PARENT_KEY and KEY columns? They are created automatically did you removed them?

They just don't appear... That's the Google sheets that is published from the appspot server. Is that due to the fact that server is on Aggregate? So forms are uploaded there and then published to Google sheets?

ahhhhh it's from aggregate... I thought it's a sheet filled from ODK Collect. So maybe @ggalmazor can help here.

1 Like

Ok sorry I had not made that clear before. Thank you very much for your help @Grzesiek2010 !

Hi @ggalmazor! :slight_smile:

1 Like

Hi, @Effy!

I see that there's been some discussion as to the form's structure at this point. Before starting to study this, could you link to the right form we can consider is OK and helps to reproduce the issue you've described?

Here is a working XLS version below. The only issue is that the uuid from the nested repeat does not link to a parent ID in the "higher" repeat (as illustrated in the first screenshot I sent).
Com_Boatcatch_2019.xlsx (99.7 KB)

XML version below
Com_Boatcatch_2019.xml (62.4 KB)

Thanks!

1 Like

Thanks! I'll take a look

Hi, @Effy!

Sorry for taking so long! Could you send me some submissions that reproduce the issue you're describing, please? You could use Briefcase to push the form to the sandbox and then I can pull it to my computer if you want.

Hi @ggalmazor !

No worries, happy you followed up.

I am not using Briefcase actually. We use Aggregate to upload forms and then download in ODK on tablets. With the link below you can find an example of the database with 2 test forms that have been sent (I am publishing to GoogleSheets from Aggregate server).

It illustrates the issue: the parent uid created for the "length repeat" entries does not link to the "catch repeat" entries. (however the catch repeat is linked to the main form instance (com boat catch). It should however as "length repeat" is nested inside "catch repeat"?

Hope this is clear enough, let me know if you need more details,

Effy

1 Like

Thanks, @Effy!

Unfortunately, the published Google Sheets doesn't help me to understand the issue. What I need to do is to reproduce it in my own environment, and that's why I'm asking for the submissions.

Would you consider using Briefcase to pull the form and send me the couple of submissions that produce the results you're seeing in the Google Sheets?

If you give me access to your Aggregate instance, I could do it myself (you could send me credentials to my email ggalmazor@gmail.com).

Duh. Makes sense. Sorry, am just a simple-minded human technologywise.

I'll try doing that through Briefcase right now! Unfortunately can't give you access to Aggregate as it is a work account.

Will let you know if I get stuck :slight_smile: