Auto Increment ID Field

3143
11
Jump to solution
02-02-2022 11:52 AM
AmandaBeck
Occasional Contributor

Hi there, 

I created a survey with Survey123, which I have connected to Integromat in a scenario for generating a report every time a survey is submitted, and at the same time, emails the submitter an attachment .pdf with an ID number on it. At first, I was using the ObjectID field as my ID identifier, but after further reading and running into my own issue of every time I tried to "test" my scenario, a new survey entry was submitted, therefore using ObjectID numbers which cannot be deleted. The problem is, once I get my scenario working properly, I need the ObjectID field to restart at 1,2,3 and so forth. Obviously, it is not possible to restart ObjectID's without restarting and recreating the survey (and all the other steps I've taken to get here), but I was hoping there would be another solution. 

I've read about auto incrementation, but I cannot figure out how to get it to work. The survey I created was authored in the web version of Survey123. 

I also had a thought of adding a field to my Survey123 table (not sure how to do that) and putting the auto incremented ID number in there, which could be calculated from a date/time field I currently have. But I'm not sure how to do that either, or if it is even possible. 

If anybody has any thoughts on this, it would be greatly appreciated! P.S. I have read through all the posts on Auto Incrementing with Survey123. Hopefully somebody has something new to say. 

Thanks in advance!

0 Kudos
2 Solutions

Accepted Solutions
jcarlson
MVP Esteemed Contributor

You can get something like this to work using custom JS functions in your survey. We use a function like this to pull the next ID in line for a layer, based on the total count of features.

This does assume there are no gaps, and it has the potential to create duplicates if two surveys are being filled out at once. But working with the total count, you'll only have those two as duplicates, and the id number gets back on track with the following submission.

For our survey, this outputs a padded string, so '003' instead of 3. But you could easily adjust that.

function nextid() {

    var lyr = "service-url-for-layer";

    var xmlhttp = new XMLHttpRequest();

    var url = lyr + "/query?where=1=1&returnCountOnly=true&f=json"
        
    xmlhttp.open("GET", url, false);
		xmlhttp.send();

	if (xmlhttp.status!==200){
        return '';
    } else {

        var responseJSON=JSON.parse(xmlhttp.responseText)
        if (responseJSON.error){
            return '';
        } else {
            var r =  responseJSON['count'];
        }
    }

    var next = String(r + 1)

    var outstr = next.padStart(3, '0')

    return outstr
}

 

- Josh Carlson
Kendall County GIS

View solution in original post

jcarlson
MVP Esteemed Contributor

Here you go! The "investigation number" is what auto-increments, using year value from the complaint_year field, outputting text like "22-003".

typenamelabelcalculation
hiddenthe_tokenTokenpulldata("@property", "token")
integercomplaint_yearComplaint Yearint(format-date(now(), '%Y'))
textinvestigation_numberInvestigation Numberpulldata("@javascript", "functions.js", "nextpermit", ${complaint_year}, ${the_token})
- Josh Carlson
Kendall County GIS

View solution in original post

11 Replies
jcarlson
MVP Esteemed Contributor

You can get something like this to work using custom JS functions in your survey. We use a function like this to pull the next ID in line for a layer, based on the total count of features.

This does assume there are no gaps, and it has the potential to create duplicates if two surveys are being filled out at once. But working with the total count, you'll only have those two as duplicates, and the id number gets back on track with the following submission.

For our survey, this outputs a padded string, so '003' instead of 3. But you could easily adjust that.

function nextid() {

    var lyr = "service-url-for-layer";

    var xmlhttp = new XMLHttpRequest();

    var url = lyr + "/query?where=1=1&returnCountOnly=true&f=json"
        
    xmlhttp.open("GET", url, false);
		xmlhttp.send();

	if (xmlhttp.status!==200){
        return '';
    } else {

        var responseJSON=JSON.parse(xmlhttp.responseText)
        if (responseJSON.error){
            return '';
        } else {
            var r =  responseJSON['count'];
        }
    }

    var next = String(r + 1)

    var outstr = next.padStart(3, '0')

    return outstr
}

 

- Josh Carlson
Kendall County GIS
AmandaBeck
Occasional Contributor

Thank you for your reply. I created the survey using the online version of S123. Do I need to use Survey123 Connect? 

Thanks!

0 Kudos
jcarlson
MVP Esteemed Contributor

Yes, this would be something in Survey123 Connect. I don't know that you can accomplish this from the web version.

- Josh Carlson
Kendall County GIS
0 Kudos
DonovanC
New Contributor III

Hi Josh,

Would you be able to share the xls of this. I am looking to do same thing auto increment but with the date in front of incrementing values.

 

Thanks

0 Kudos
jcarlson
MVP Esteemed Contributor

Here you go! The "investigation number" is what auto-increments, using year value from the complaint_year field, outputting text like "22-003".

typenamelabelcalculation
hiddenthe_tokenTokenpulldata("@property", "token")
integercomplaint_yearComplaint Yearint(format-date(now(), '%Y'))
textinvestigation_numberInvestigation Numberpulldata("@javascript", "functions.js", "nextpermit", ${complaint_year}, ${the_token})
- Josh Carlson
Kendall County GIS
DonovanC
New Contributor III

What is the reason behind grabbing the token? My end goal is to implement this function in AGOL. Would the code be very similar to the code posted above?

Thanks again for your help, still new at learning these scripts in S123

0 Kudos
jcarlson
MVP Esteemed Contributor

When the script  makes a request to /server/rest/services/hosted/some-feature-service/0/query, one of the URL parameters that must be supplied is the token. If you're accessing a public layer, you can probably omit it, but if the layer isn't shared publicly, the token is what tells the service you're authorized to see it. I would imagine the code could translate directly to an AGOL environment.

- Josh Carlson
Kendall County GIS
0 Kudos
AmandaBeck
Occasional Contributor

Thank you for  your reply. I am still working on getting this to work. Is there a way I can have it so the year does not show? I want a field to populate that would be 001, 002, 003, etc. that is never-ending. 

Is this doable in S123 Connect and if so,  how would I accomplish it? 

Thanks again, 

0 Kudos
JerrySneary
New Contributor III

Hi @jcarlson ,

Would you mind helping me with this. I've copied the JavaScript and the xls into Survey123 but the investigation number is returning "NaN".

 

Thank you,

Jerry

0 Kudos