Auto Increment ID Field

[AmandaBeck]

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!

[jcarlson · Accepted solution]

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
}

[AmandaBeck]

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

Thanks!

[jcarlson]

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

[DonovanC]

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

[jcarlson · Accepted solution]

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

type	name	label	calculation
hidden	the_token	Token	pulldata("@property", "token")
integer	complaint_year	Complaint Year	int(format-date(now(), '%Y'))
text	investigation_number	Investigation Number	pulldata("@javascript", "functions.js", "nextpermit", ${complaint_year}, ${the_token})

[DonovanC]

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

[jcarlson]

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.

[AmandaBeck]

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, 

[JerrySneary]

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