Creating 'Ticket Numbers' in Survey123 for ArcGIS

10863
29
11-24-2017 10:15 PM
Esri Frequent Contributor
15 29 10.9K

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.

TypeLabelCalculation
textTicket Numberformat-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:

TypeLabelCalculation
textTicket Numberconcat(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.

29 Comments
Occasional Contributor III

Is it possible to copy the Work Order field from Workforce for ArcGIS to a Ticket Number field in Survey123? That way the number does not need to be populated twice?

Esri Esteemed Contributor

Hi Carl,

Yes, this feature was recently added to Workforce- see https://community.esri.com/groups/workforce-for-arcgis/blog/2017/10/27/workforce-for-arcgis-v1701-is... for more details.

New Contributor III

Great information here, thank you! We are still struggling with this as we need to create unique report numbers for each rare species point collected in our Rare Species Observation Survey (built in Survey123 Connect). These report numbers are used:

- to easily reference a collected record when staff are conversing about it e.g. data staff needs a biologist to get more info for them on an observation they submitted

- to easily be able to navigate spatially to specific rare species observations e.g. one staff member is qc'ing the work of another and so they need to quickly identify which of many observations by a given coworker they need to work on.

- in the name of the 'final copy of record' that we are required to generate for every observation (a report containing a map, all existing photos, plus all attributes)

So using a field with Global ID, or even a manually created ID that is really long (full user name plus date plus time) is not ideal. With the old paper submittals we received, we had an auto incrementing number preceded by a letter indicating Animal, Plant, Vertebrate or Invertebrate (e.g. A1234). I understand this is not possible in Survey123 because there are multiple users submitting records and you could very easily have overlap in the numbers BUT what if the user name preceded the auto incrementing numbers e.g. taraboswell0001?

EDIT: Ugh nevermind...that wouldn't work, probably for many reasons but guessing once a survey is submitted, when you launch the survey to enter another feature, it has no way of knowing what has been entered before it and so it can't increment the number? Guessing it can only do calculations on values that exist in the form currently open on your device?

Esri Frequent Contributor

Hi Tara. That is right. Calculations only can reference values in the currently open Form.

New Contributor II

<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.>

Very interesting blog. 

But, if it is possible to create and Auto increment fields. then we can assign some unique ID to collectors. and then while merging all the data from different devices, we can easily exploite our database. 

In my work, Incrementation is crucial . Actually i have create a manual ID field... but there is always problem with that field, because collector are not really concentrate in what there are doing. so at the eand, I have many individus having the same ID... and is very complicate ........................

New Contributor

Auto increment fields work great for my report number......

except that the report number now changes each time the form is modified because the date function references “now” as the date/time stamp.

format-date(now(), '%y-%m%d-%H%M')

 

Is there a date/time function that references the date the record is created in the database instead of the current “now” date/time?

 

Esri Esteemed Contributor

Hi Eric,

There isn't a way to refer to the database time as a function.  To prevent the now() function from updating after initial population, wrap it in a once() function:

format-date(once(now()), '%y-%m%d-%H%M')

New Contributor

Thanks very much for the info.

Occasional Contributor

I've used survey start time, (type: start, name:start_surey), that way the date never changes.

New Contributor III

I've tried the format-date function as described and it works 100% if using the app, but returns an "Invalid date" string if my survey is opened in the web browser:

 format-date(once(now()), '%Y%m%d%H%M')

Is this expected behaviour? I haven't seen any indication that format-date functions don't work in the browser.

Esri Esteemed Contributor

Hi Dave,

In testing this, I can see the issue you are specifying.  I can get the format-date to function correctly when the once(now()) calculation is in a separate question that is then referred in the calculation to generate the timestamp.

Occasional Contributor III

Is there a way to bring up the Object ID when viewing a survey through the inbox?

Esri Esteemed Contributor

Hi Geethaka,

You can include the objectID field in the form - I would caution you to do so in a read-only field, though.

Occasional Contributor III

Thanks James Tedrick‌, but I can't seem to publish a survey with "objectID" as a name.

I get an "unable to add feature definition" error.

Esri Esteemed Contributor

Hi Geethaka,

You would need to use this with a form based on an existing feature service.  That being said, I can see an issue in that Survey123 Connect does not allow non-editable fields to be in a form.

Occasional Contributor II

Hi James,

I am attempting to generate a unique BATCHID that gets copied in to multiple samples in a repeat.  I'm finding that wrapping with the once() function is not working. Each sample in the repeat has a different value (time) in the BATCHID2 field. 

My calculations:

BATCHID :    concat('BATCH ', format-date(once(now()), '%Y%m%d %H%M%S%3'))

BATCHID2 (in the repeat):    ${BATCHID}

Both are "calculate" field type.

Not sure if this is relevant, but the value that gets recorded for BATCHID matches the BATCHID2 value for the last sample.

Esri Esteemed Contributor

Hi Lynn,

Can you try with the once(now()) as a separate question that you then refer to in the calculation?

Occasional Contributor II

I ended up adding a new field to capture the start date of the survey, and then referenced that in the calculation. And it worked!  So that may have basically had the same effect as your suggestion.                           

Occasional Contributor

 In a future phase of the survey, we will integrate with an existing enterprise application that generates the Audit ID (i.e. Ticket Number) and prevents duplicates. In the meantime, we were able to ensure uniqueness using a backend process utilizing Python.  Years ago, the users had invented their own naming convention that was meaningful to them.  It should be unique based on their methodology.  It's inevitable of course that human error is introduced now and then.  The nightly backend process will increment a duplicate found in the Hosted Feature Layer and send a notification email to the user that it was changed. 

Occasional Contributor II

Hello,

I'm attempting to create a unique timestamp-based SAMPLEID for each sample in a repeat. At least, one that doesn't change every time the survey is re-opened.  In our workflow, we collect samples on one day and save the survey as a Draft. The survey is then reopened the following day and additional fields in the repeat are populated.

 

Here's the calculation:

SAMPLEID :    concat('BA ', format-date(once(now()), '%Y%m%d %H%M%S%3'))

 

I also tried putting the once(now()) function in a separate question and then referring to it in a calculation:

SAMPSTART:    format-date(once(now()), '%Y%m%d %H%M%S%3')

SAMPLEID:  concat('BA ', ${sampstart})

 

Either way, when I reopen the survey, the SAMPLEID re-calculates to "now".

We are saving the survey as a draft, then reopening the next day.

We are not using Inbox.

 

Help!!

Thanks,

Lynn

Esri Esteemed Contributor

Hi Lynn,

Which version of Survey123 are you seeing this occur on?  We recently revised how calculations work - the value should be set to what was saved in drafts as of 3.1

Occasional Contributor II

Well that must be it!  I'm using Connect version 3.0.142 and Field app v 3.0.149.

New Contributor

Have there been any updates that would allow for this behavior now?

Esri Esteemed Contributor

Hi Steven,

I'm not sure what behavior you are asking about?

Occasional Contributor

Ismael Chivite‌ Is there anyway to access the globalid in a survey? I have a survey with a note field that allows me to open another survey using the survey123 url, and I would like to pass the globalid to that "child" survey. There seem to be 2 issues here: (1) is the globalid really generated before form submission? and (2) it seems I can't publish a form with the "globalid" field. 

Occasional Contributor

I was actually able to resolve issue #2 by setting the globalid bind::esri:fieldType = Null. This allows me to see the globalid from the inbox once submitted. But that still leaves me with issue #1. According to the post "UUIDs, GlobalIDs and GUIDs...can be easily generated from Survey123 even when disconnected" but from my testing it seems like the GlobalID (unlike manually managed UUID) is only generated once submitted online. Is that correct?

Esri Esteemed Contributor

Hi Chaim,

You are correct that the globalID is not generated until the submission of the feature to service, though relationships are maintained until that point.

Occasional Contributor

Thank you.

Occasional Contributor

I am able to add the objectid field by adding a "text" type question, with the bind::esri:fieldType set to null.