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.