Auto Increment integer using Survey123

[RhondaThynne]

Would it be possible to auto increment an integer field (within a Repeat) calculated from the previous entry; on the same field?

For example: during our fish surveys we can catch up to 30 fish at one site. Each fish needs be be measured and weighed and have a Fish Number.  The first fish caught would be "1", second fish "2", third fish "3" etc.

So, it would be fantastic (within a Repeat) after the first fish information is entered, the survey form would auto-populate the Fish Number field with "2" and so on.

I understand there are still some bugs with Repeat's but just wondering if this would be a possibility down the road!! Even if this could be accomplished outside of a repeat, it would still be useful!!

Thank you

[DougBrowning]

I got this to work for repeats

In a repeat have a field with a calculate (it can be editable)

Use this formula subbing in any field in the repeat (except itself)

        once(count(${RecKey}))

ref https://community.esri.com/message/787003-re-survey123-functionality-calculate-position-in-repeat-gr...

[JürgenBiendara1]

Hi Doug,

I'm trying to get this auto-increment working but I fail. Can you attach a sample xls-sheet that shows how to do it?

Thanks and best regards,

Jürgen 

[DougBrowning]

I am not allowed to share the whole form but this should help.

Inside the repeat you would have the field you want to hold the Auto Incremented number - lets call it PointNbr.  That field would have a calculation of once(count(${RecKey})).  Where RecKey is any other field in the repeat.  You cannot count itself so I just picked a field.

You are basically saying count up all the repeats and use that number as my Auto Increment number.  The Once tells it to just do that calc when the repeat is first created.  Otherwise it would constantly change when you went back to a old repeat record.

Hope that helps

[JürgenBiendara1]

Thanks Doug, I got it.

In my first attempt I missed to enter a value into the "counted" question. Now I am using a hidden field with a preset default value, which seems to make it countable.

[Heena1]

Hi JürgenBiendara1,

Is this working automatically without clicking + sign in Repeats? I am looking for a way to assign auto increment ID every time a user opens the form. Would this work in this situation?

Would you be able to share your xlsx?

Thank you,

[KarenTuerk2]

Thank you, thank you! The lack of auto-increment function in S123 has been driving me mad. This works beautifully!

[DeonLengton]

Hi Rhonda and other Survey123 users

I have successfully managed to generate an auto-incremented field with Survey123 in two ways:

Option 1) Inside a repeat: Create a calculation variable inside the repeat that results in a value of 1 and then use that variable in a new calculation inside the repeat to auto-increment with each repeat: once(count(${count1})). The once is important because if the user goes backwards through the repeat then it shouldn't add 1 again but stay at its first calculated value.

Here is a blog indicating how I used the auto-incremented value to pull data from a CSV for a questionnaire with a repeat in it (this is still one of my favourite "hacks"):

https://esri-southafrica.blog/2017/12/14/survey123-with-a-single-attribute-for-repeating-generic-que...

Option 2) For each Survey record captured: For another project that required an auto-incremented field for each new survey record captured (not a repeat) I actually created triggers and database objects (tables) inside the Survey123 SQLite database to do the following:

 - a table to store the last used sequence number

 - an AFTER INSERT ON Surveys trigger for the specific survey I wanted to auto-increment. This trigger did the following:

      - retrieve the next sequence number

      - update the DATA field for the new survey record and inject the new sequence number into the correct field. I defaulted my auto-increment field in my survey to #### and then just did a replace on the DATA string:

UPDATE Surveys SET
DATA = replace(DATA, '####', (<your incremented number>)),
feature = replace(feature, '####', (<your incremented number>))
WHERE rowid = new.rowid;

So in essence what is happening is that when you submit a new survey the trigger catches the creation of the record in the Survey123 database and then injects the auto-incremented value into the DATA string of the feature before it gets synced.

I have a working example of this but I haven't felt comfortable sharing this with the Survey123 community since I am basically hacking my way into the Survey123 database... This is obviously only for advanced users because you can easily mess up your whole Survey123 installation.

If there's enough interest I will write a Blog and provide the code to do it but only if James Tedrick‌ and Ismael Chivite‌ agrees to it.

[DougBrowning]

On No 1.  You do not have to make a calc variable of 1 if you do not want to.  It will count up strings just fine.

The issue I see with number 2 is if a team is using multiple tablets.  Or if a tablet say runs out of battery and they pick up a different one.  How is this handled?

Thanks

[DeonLengton]

Hi Doug

Thanks for the tip on Option 1 - i just wanted it to be a little bit more understandable if someone else worked on my survey design 🙂

Option 2 - I had a team of field workers and each team member were allocated a block of numbers. I then created a config "survey" which allowed the users to set up their Survey123 setup with a Range Start indicating the start of the auto-incremented values:

When the users saves this questionnaire I actually caught it with a trigger in the database to save this into my custom tables in the Survey123 database. So if a device became problematic the user only had to take a new device and "program" it to start at the previous number they used.

[LMedeirosUI]

This isn't pretty, but it works...

My scenario is that I need to add a prefix for blood sampling fish (seriously - fish person as well!). So, I have to combine the prefix with an integer that auto increments from a given start number. The default value of that start number is one, but there are cases where we will have multi-day samplings and want to start on a different number.

In a header/not repeated group I have cells for the prefix (blood_prefix) and the start number (blood_start). These are editable text fields.

Within the repeat (but NOT within a group within the repeat) combine the fields into another field (${bloodnum}) using a calculation: once((${blood_start}+position(..))-1)

For my "Blood Sample ID" field I have a calculation that combines it all: concat(${blood_prefix}, '-', ${bloodnum})

You have to do the "-1" of the start position number and the position(..) count.

Like I said, not pretty. But works. Alternatively, you could use "last known sample/count number" instead of the desired start number. This simplifies the code as you don't need to (start number + position(..)) - 1), but is more confusing to the end user than just asking for the starting number.

You can also use the string-length function to format your number with leading zeros. So, ${bloodnum} becomes ${bloodnum_format} using the calculation of: if(string-length(${bloodnum})=1,concat("000",${bloodnum}),if(string-length(${bloodnum})=2,concat("00",${bloodnum}),if(string-length(${bloodnum})=3,concat("0",${bloodnum}),${bloodnum})))

Then you concat this with the prefix (i.e., concat(${blood_prefix},'-',${bloodnum_format}))

(see https://community.esri.com/t5/arcgis-survey123-questions/survey123-leading-zeros-for-integer/td-p/80...)

Type	field name	Label	Calculation
integer	blood_start	Starting number of Blood Sample
calculate	bloodnum	Blood Number	once((${blood_start}+position(..))-1)
calculate	bloodnum_format	Formated sample ID	if(string-length(${bloodnum})=1,concat("000",${bloodnum}),if(string-length(${bloodnum})=2,concat("00",${bloodnum}),if(string-length(${bloodnum})=3,concat("0",${bloodnum}),${bloodnum})))
text	BloodSampleId	Blood Sample ID	concat(${blood_prefix},'-',${bloodnum_format})

The ${bloodnum} field cannot be within a group in the repeat, but the rest can go where you want them.

Hope this helps someone else! Also, if I'm working way too hard and this has been incorporated into Survey123, please let me know.