How to get max value of a csv file

Support
1

Hi all,

Here my question :
I'd like to increment by 1 the value of the "id" question of my formular. For the moment I'm tring to use entities and to get the max value of the "id" field.

Here is the survey tab :

Capture1
Capture11236×85 3.3 KB

Here is my entities tab :
Capture2

Here is my entities fields :

Capture3
Capture31085×99 3.79 KB

I attach my xls form
gestionBouquetinsMarques.xls (70.5 KB)

Thanks a lot for any ideas !

2

Hi @cborotPNV,

The formula to get max value of id from entities is max(instance('capra_ibex')/root/item/id). Then you can put ${max_id} + 1 in calculation column of identifiant instead of default column.

However, you may need to revise your approach to set an ID for new submissions. If there is only one enumerator then this approach is fine, otherwise there are chances of duplicate IDs since two enumerators may see the same max_id at the same time. You can do few things to avoid duplicate IDs:

  • By having same offline coordination mechanism between your team so they override the id field
  • Add a prefix to the ID and assign different prefix to each enumerator
  • Use UUID as the ID, you can have different UUID than instanceId if you want by using uuid() formula

Thanks,

Sadiq

3

Thanks @Sadiq_Khoja !! I tried this but some syntax errors, now it'ok with your answer. Thanks for the advice about client side building id. It's ok for our needs.

1 Like
4

I hope someone can help me. I have a similar problem to this one. But I fixed my syntax and still get the error. My goal is to the exact same, iterate +1 over the last code submitted. My file is:

smc_registo_abrigos.xlsx (84.1 KB)

The error I get when I try to upload the form:

"The XLSForm could not be converted: ODK Validate Errors:

Something broke the parser.
Error evaluating field 'ult_abrigo' (${ult_abrigo}[1]): The problem was located in Calculate expression for ${ult_abrigo}
XPath evaluation: Instance referenced by instance(abrigos_morcegos)/root/item/cod_abrigo does not exist
Caused by: org.javarosa.xpath.XPathMissingInstanceException: The problem was located in Calculate expression for ${ult_abrigo}
XPath evaluation: Instance referenced by instance(abrigos_morcegos)/root/item/cod_abrigo does not exist
... 10 more

The following files failed validation:
${tmppnhv8d_1}

Result: Invalid"

Thanks in advance

5

Hi @dsacras

You need to attach your entity-list by either using select_one_from_file or csv_external, see more details at https://docs.getodk.org/entities-intro/#how-do-i-access-entities-from-my-forms.

After that you would need to modify your calculation to handle the case when there is no entity in the entity-list like this: if(count(instance('abrigos_morcegos')/root/item) = 0, 0, max(instance('abrigos_morcegos')/root/item/cod_abrigo))

On a side note: We have created this awesome XLSForm Template (Excel or Google Sheet) that makes it easier to write XLSForms: https://xlsform.org/en/ref-table/

Thanks,

Sadiq

2 Likes