How to Query all Features and Return the Highest Integer Value used for a Particular Field?

[ScottLehto3]

I have a unique ID field titled "field_id".

I would like my Survey123 Form to scan my feature service records and return the maximum integer value + 1 for a particular field.

There is a formula named max(${}), however the max(${}) formula's scope is limited to the single feature service record existing in the Survey123 Form, and not all the features in the feature service.

[IsmaelChivite · Accepted solution]

Hi. To do this, I think a good approach is to use a custom JavaScript function. You will need Survey123 Connect and a bit of JavaScript and ArcGIS REST API knowledge. Check this post for an intro to using your own custom JS functions: https://community.esri.com/t5/arcgis-survey123-blog/extending-survey123-smart-forms-with-custom-js-f...

There is a section focusing on web services which I think should be a good starting point. Below is a sample JS function to get the max value in a feature layer from a field.  You will need to change the featureLayerURL making sure you specify the layer index at the end. Also, do not forget to pass the token as described in the blog post above.

pulldata("@javascript","myFunctions.js","getMax","ObjectID",pulldata("@property","token"))

    function getMax(field,token){
   

        var featureLayer = "https://services2.arcgis.com/fJJEXNgxjn0dpNsi/ArcGIS/rest/services/service_5015e2219660455a928d2616d85433eb/FeatureServer/0";

        var xmlhttp = new XMLHttpRequest();
        var url = featureLayer + "/query?f=json&where=1=1&outStatistics=[{'statisticType':'max','onStatisticField':'" + field + "', 'outStatisticFieldName':'MaxValue'}]";


        if (token){
            url = url + "&token=" + token;
        }

        xmlhttp.open("GET",url,false);
            xmlhttp.send();

     
        if (xmlhttp.status!==200){
            return (xmlhttp.status);
        } else {
            var responseJSON=JSON.parse(xmlhttp.responseText)
            if (responseJSON.error){
                return (JSON.stringify(responseJSON.error));
            } else {
                if (responseJSON.features[0]){
                    return JSON.stringify(responseJSON.features[0].attributes.MaxValue);
                }
                else{
                    return ("No Features Found");
                }
            }
        }
    }

[ScottLehto3]

Thank you. This is great information.

[BryanWright]

I'm new to JavaScript and have been trying to extend the above code to return a maximum value for a select variable.  For example, say you have a feature layer with CustomerID (string) and Sales (double) and you want to return the maximum sale for a selected customer.  So I see how you would need to add the customer ID variable to the calculation

pulldata("@javascript","myFunctions.js","getMax","ObjectID",pulldata("@property","token"), ${CustomerID})

and then I think you'd have to modify the 'where' clause in the URL variable

/query?f=json&where=1=1&

but I haven't been able to get that to work.  Thanks for any suggestions you can provide!

[MaddieShields2]

In the example above, the "max" value is pulled using the first digit in the number but not the largest value. For example, I ran this code on a feature layer that includes a "name" column varying from 1 to 300 or so. The max number it pulled was 99, since 3 is less than 9, even though 300 > 99. 

My original attribute was a string, so I changed it to an integer and this seemed to resolve the problem.

[EricaNova]

@IsmaelChivite I'm trying to implement your solution but I think I'm missing something simple.

I copied the code above into a script file called myFunctions.js, so there is a function named "getMax" in my scripts folder.

The field I want to get the max value from is "positive_case_no".

I tried to calculate the next "positive_case_no" for the (online-only) survey using this text in the calculation field of the XLS form:

pulldata("@javascript","myFunctions.js","getMax","positive_case_no",pulldata("@property","token")) + 1

But in the preview, the value is incorrect (max should be 15, it's giving me 4001) and when I take the survey, no number is autopopulated.

I also tried it using 

pulldata("@javascript","myFunctions.js","getMax","ObjectID",pulldata("@property","token")) + 1

but that didn't work either.

I actually didn't see a description of "how to pass the token" in the blog post you linked to (https://community.esri.com/t5/arcgis-survey123-blog/extending-survey123-smart-forms-with-custom-js-f...). So maybe I'm missing something there?

I'm not getting an error message - it's just not working. What am I missing?

[EricaNova]

I am getting closer - a couple simple fixes: for one, I changed the URL for the layer it was accessing; I had already done this but hadn't pressed save. I also modified the calculation text to be an integer before adding 1:

int(pulldata("@javascript","myFunctions.js","getMax","positive_case_no",pulldata("@property","token"))) + 1

The preview version of Survey123 Connect is showing me the correct value, but when I go to take the survey, it is just showing the maximum number (rather than max + 1).

[EricaNova]

And I finally have a working solution:

I had to add a "calculate" type question above this question:

type: calculate;

name: pos_case_count;

label: calculate;

calculation: (pulldata("@javascript","myFunctions.js","getMax","positive_case_no",pulldata("@property","token")))

bind::esri:fieldType: null 

Then I used the result of this field in my integer type field "positive_case_no" - calculation column is now:

int(${pos_case_count}) + 1