Pre-populate auto-increment fields in Suvey123 using pulldata()

Summary

Many users want to produce convenient, unique, identifiers for their Survey123 records, and have them pre-populate a form. 

An increasingly popular workflow within the Survey123 Connect is to use the pulldata(@layer) function, using Excel formulas to calculate a count of total records, then adding 1 to create a "ticket number". ObjectID is a reliable and useful field for this operation. 

The pulldata() query is as follows:

pulldata("@layer", "getValue", "attributes.TotalCount", concat("**INSERT REST URL OF FEATURE LAYER HERE**", '?outStatistics=[{"statisticType": "count","onStatisticField": "objectId","outStatisticFieldName": "TotalCount"}]'))

This calculates the number of records. It is then necessary to create a new integer field in the .xls form, with the following string, which pre-populates a question in the survey with the current ticket/survey number.

${"Name of your total count field"}+1

The values in the .xls form

Note:
Depending on the use case, it is likely best to hide the first question and to make the second read-only.

This has two notable limitations, one of which is easily addressed by appending the query to make it non-duplicative.

Procedure

• Problem: pulldata(@layer) function caches query responses

This is outlined in BUG-000158969. The purpose is to optimize network requests and more efficiently work with the data. 

There are two solutions. The first is simply to close and re-open the survey every time a new record is submitted. 

The second solution is to append the query with a timestamp to make the query non-duplicative. The expression to do this is as follows:

pulldata("@layer", "getValue", "attributes.totalCount", concat("**INSERT FEATURE URL HERE**", '?outStatistics=[{"statisticType": "count","onStatisticField": "objectid","outStatisticFieldName": "totalCount"}]&t=',now()))

This appears as follows in the .xls form:

• Problem: Duplicating values

Note that the information returned is not guaranteed to be non-duplicative. This works with a count still, rather than any unique IDs, or even ObjectIDs themselves.

ObjectIDs only get created when the record has been submitted to the feature service.  This is a problem when the life cycle of the event starts even before there is a chance to add a record into the feature service.  For this reason, this should only really be done when users are sure that the survey will be recorded and uploaded immediately – i.e., when there will definitely be a reliable network connection. It breaks down as soon as there is any asynchronous collection, as soon the workflow makes use of the Survey123 Outbox, or as soon as there are multiple users submitting surveys. Definitely consider all of that before putting this workflow into practice.