Creating 'Ticket Numbers' in Survey123 for ArcGIS
Sometimes the most difficult part of writing a blog is coming up with a good title. I am not sure I quite got the right one this time, but since I have your attention I will describe now in more length a common problem that I see people having in Survey123, and some approaches to it.
The 'Ticket Number' problem: The use of Survey123 for ArcGIS to submit incidents, requests or workorders is quite common. In these cases, it is very likely that you will want to uniquely identify every survey submitted, so it can then be tracked throughout its complete life cycle: from the moment the survey is submitted, to when it is reviewed, assigned, rejected and/or completed. I will generically refer to this as the 'Ticket Number' problem. Say for example you create a survey to help handle the whole life cycle of a 311 call, from the moment you receive a call from a citizen, to the moment it is, validated, prioritized, assigned, acted upon and closed. To follow along the 311 request, you need to have a unique 'Ticket Number' so all parties involved can find the right information about the event. Ideally, this Ticket Number should be somewhat user friendly.
There is not an obvious way in Survey123 to create such 'Ticket Numbers', at least not at first sight. Lets look at some options.
Auto increment fields (and ObjectIDs): In common database jargon, some people solve the problem with what is known as Auto Increment fields. Through an Auto Increment field, a database is able to assign a new and unique identifier (typically a number) to every new record added into a table. This works well, because it does not matter from where you add a new record: when it gets to the database it will be assigned a new identifier... a new 'Ticket Number'.
In ArcGIS, every record submitted to a feature service automatically gets a 'Ticket Number' through an attribute called ObjectID. Every record submitted to ArcGIS, from Survey123 or any other app, will have its own unique ObjectID. Now, there are a handful of consideratons to keep in mind with ObjectIDs:
- ObjetIDs are managed automatically by ArcGIS and as such, you can never change them.
- 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. Think for example that a city employee wants to use Survey123 to record an infraction of the city's code. The person receiving the ticket must know what the Ticket Number is even if it has not yet been officially recorded (submitted) into the City's database (feature service). If the city employee happens to be working on a remote area with no connectivity, there will be no chances of waiting for ArcGIS to give back a Ticket Number...
- ObjectID fields are created new every time you create a new table, so there is really no way you can reuse old ObjectIds from a back-up table, or when you append records from one table to another.
The ObjectID or Auto Incrementing field approach give the most user friendly Ticket Number, but the necessity to be connected and the fact that it is completely managed by the database rule this option out in some scenarios.
UUIDs: A Universally Unique Identifier (UUID) is, as its name indicates, a universally unique identifier. 
They are also known as GUIDs. Unlike auto increment fields and ArcGIS's ObjectIDs, UUIDs can be created even if disconnected. That is, you do not need to wait for a record to be added to a master table before you can get a unique identifier.
In Survey123, you can use the uuid() function within the calculation column of your XLSForm to create a UUID. A UUID looks something like this:
e9858c60-84fe-49c6-b792-ac8cf0cc0cfd
UUIDs are not pretty to look at, and from a practical perspective, impossible to remember. This is the reason why they do not quite fit the bill for the 'Ticket Number' problem either. I wanted to bring this up anyways, because in some cases where you need a unique identifier, even if it is not human friendly, the use of the uuid() function could be handy.
Purely for completeness, you should also know that ArcGIS feature services support the notion of GlobalID and GUID fields where UUIDs can be stored. In the case of GlobalID fields, the UUIDs are created automatically by ArcGIS when the feature is created in the feature layer, and guaranteed to be unique across all records in the table. If working with GUID fields, UUIDs can be assigned and edited at will and no uniqueness is required. That is, you can calculate and set a GUID value using the uuid() XLSForm function.
Hand-made Ticket Numbers: As long as you understand certain limitations, it is possible to create more human friendly 'Ticket Numbers' using functions supported in Survey123.
It would be great if we had some sort of magic auto-incrementing function that would give us unique numbers sequentially. This would allow us to start with an easy number like 0000 and then increase it to 0001, 0002.... easy to remember! Unfortunately, this is not that easy when working disconnected because there is no way for an offline device to know what is the next sequential number available. Even if we were able to create sequential numbers within a device, we cannot guarantee that other devices will not create the same sequential numbers.
Since time is always moving forward, a first approach is to use the current time, say down to the second, to generate a number. This can be accomplished by using the format-date() function, which is described in a previous post on Dates and Time in Survey123.
| Type | Label | Calculation |
|---|---|---|
| text | Ticket Number | format-date(now(), '%y-%m-%d %H:%M:%S') |
The above will generate a number similar to this: 17-09-26 13:22:23 where the first two characters are the year, the next two the month, then the day, hour, minute and second. If you look carefully at the expression within the format-date() function I have added some dashes and spaces a separators, but you can get rid of them if you like.
Using the current time is certainly going to give us a unique number for the device you are on, but this does not mean that it will be unique across all devices capturing data. First of all, there may be people creating new survey at exactly the same second. If working across multiple time zones, the chances of coincident numbers increase.
Not everything is lost, because you can augment your Ticket Number with some additional data to reduce the changes of creating duplicates. You can for example use the username of the currently logged in user. This can be achieved by using the function property('username'). This would leave your Ticket Number expression as:
| Type | Label | Calculation |
|---|---|---|
| text | Ticket Number | concat(format-date(now(), '%y-%m-%d %H:%M:%S'),property('username')) |
Obviously, using the logged-in username is only possible if your survey is secured. This approach will not work with public surveys where users can submit data anonymously.
Depending on your particular scenario, you may be able to use a combination of one or more other attributes in your survey that would make your Ticket Number meaningful and unique.
Technically, a handmade Ticket Number is not guaranteed to be unique although in practice, if you build it correctly, it will do the job just fine.
When working with hand-made Ticket-Numbers or with the uuid() function, you also need to consider certain workflows that could get on your way.
- Hand-made ticket numbers and uuid() are defined through an expression in the calculation column of a survey. Calculations will execute when your form loads for the first time and will populate the Ticket Number accordingly. Once the Ticket Number is set, it will not be calculated again unless the calculated value is altered or removed. This is all good and consistent with how calculations should behave, except if a user decides to create a copy of an existing survey from the Sent folder. In that case, the Ticket Number will not be recalculated so you will end up with a duplicate. Using the Sent box settings in Survey123 Connect, you can prevent this from happening by disabling the Sent folder for your survey.
- Similarly, you will want to decide if you want to make the Ticket Number be a read-only field or not. If users are given the ability to override a Ticket Number manually, you can no longer guarantee uniqueness. You can easily flag the readonly XLSForm column of your question to 'yes' if you want to make your Ticket Number question read-only.
I hope that the above gives you some food for thought, even if you did not find a solution as you would have imagined it first.