Creating 'Ticket Numbers' in Survey123 for ArcGIS

30925
41
11-24-2017 10:15 PM
IsmaelChivite
Esri Notable Contributor
19 41 30.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.

41 Comments
CarlHolt1
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?

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

TaraBoswell
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?

IsmaelChivite
Esri Notable Contributor

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

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

EricBenoit
New Contributor II

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?

 

JamesTedrick
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')

EricBenoit
New Contributor II

Thanks very much for the info.

Jean-YvesLandry1
Occasional Contributor

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

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

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

by Anonymous User
Not applicable

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

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

by Anonymous User
Not applicable

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.

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

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

JamesTedrick
Esri Esteemed Contributor

Hi Lynn,

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

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

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

LynnBerni
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

JamesTedrick
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

LynnBerni
Occasional Contributor II

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

StevenNelson1
New Contributor

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

JamesTedrick
Esri Esteemed Contributor

Hi Steven,

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

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

ChaimSchwartz4
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?

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

ChaimSchwartz4
Occasional Contributor

Thank you.

ChaimSchwartz4
Occasional Contributor

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

PegGronemeyer1
New Contributor III

Hello

I've looked, but no luck - so I will ask - has there been any advancement on being able to add an auto-incremented and/or sequential number in Survey123 connect? 

I can create sampleIDs using "concat" fx with site, habitat, and species.  I can add the date, but we are likely to have multiples of the same species on the same date, plus the sampleID is getting quite long.  So it's the old question of how to create a unique ID that isn't 20 characters long.  Ideally I would like to simply add "_01", "_02", etc. to my current concatenation to create a sampleID for each individual that we process.  A uuID()  is way too long.  I tried constraining uuid()  to 1-4 characters but it didn't work.  I think I could use an index to add the last 4 characters of a uniqueID to the rest of my sampleID, but I can't guarantee that would be unique then, right?

I believe this is all related to "Creating 'Ticket Numbers' in Survey123 for ArcGIS".  I apologize if this has been addressed, but I just can't find anything that indicates that the ability to do that has been added.  

Thank you very much for any suggestions. 

AaronBrown3
New Contributor II

All,

I've read through this thread trying to make sense of what was said, and I'm trying to create a ticket number for a work order and have it display on the form. Each new entry, I want to display a new unique value. I typed 'objectID' in calculation, but when I click save i get a reference error that its not defined in expression. Please note I'm using the grid theme. I'm curious as to what fields i need to populate with what information to do so. I'd like the w/o ticket number to start with 10001. @JamesTedrick please advise. 

Thanks

KinleyWinchester
New Contributor III

@JamesTedrick I followed the instructions to get a ticket number (Reg_Num) from the dateTime field after calculating to once(now()).  It was working late Aug/early Sept down to the seconds.  Now it isn't working.  Did the new update 3.13 cause a problem with this calculation?

 

KinleyWinchester_0-1633118710210.png

Edit:

It appears to work in the Connect App, but doesn't shows seconds any longer:

KinleyWinchester_0-1633123623528.png

And then in the web form, where it worked fine before, it gives an error:

KinleyWinchester_1-1633123659253.png

 

KinleyWinchester
New Contributor III

@JamesTedrick Have you had a chance to check my last post from 2 weeks ago?  Thanks!

AndyMcClary
New Contributor III

@JamesTedrick  I've got the same problem as @KinleyWinchester . Seconds are no longer showing up in format-date. Any idea what's going on? I'm switching over to using random() but it definitely produced some chaos before I figured out what was happening.  

m3
by
Occasional Contributor

EDIT: Removed post

m3
by
Occasional Contributor

I'm having a lot of trouble with the format-date() and different devices.

Editing a survey on the web works differently than editing on a mobile device.  The format-date() function does not really seem to change to date format to a string or something is parsing the date as a date instead of a string.

The time stamp, from a mobile device is coming in as a unix time stamp which then gives me an valid looking number in the survey and valid data in the table, but if I open this survey to edit it in the web editor, it gives me invalid date in the ticket number and overwrites the ticket number with "Invalid Date" although I've specified once() in the field

Using the web editor gives me m/d/yyyy hh:mm:ss as my time stamp, which then gives me a valid time in my ticket number but gives me an invalid date in one of the placeholder fields.

We have users on iOS, Android and Desktop (web surveys and editing surveys)

I suspect it's the web editor that seems to interpret the unix time stamp as an invalid date instead of just a plain old string like it is (text field)

I really need this to work, we have almost 900 hundred work orders to sort out and we are in a difficult place with this.

Aguirre_WOG
New Contributor II

Good stuff.  Thanks for the info. I'm trying to work out a ticket number solution right now. This is going to be ugly. 🙂

JerrySneary
New Contributor III

Hi @JamesTedrick and @IsmaelChivite 

I know there have been advancements on this if you are using Enterprise and Attribute Rules but have there been any for AGOL? This month alone I have two projects that need auto incremental ID's.

Edit: I found a post that says it's a Solution but currently I am unable to get it to work.
https://community.esri.com/t5/arcgis-survey123-questions/auto-increment-id-field/m-p/1140051#M40242

Edit2: You have to do some tweaking to the code to get it to work, but I also found @IsmaelChivite already has posted the code to accomplish this, Click Here. My question is now can you use JavaScript to do a count of variable type in the attribute table, ie: Apple count or Orange count.

stan_survey
New Contributor

Hello,

Having issues with concatenating a "Hand-Made" ticket based on a select_one type question. Is it possible to have an 'or' statement within a calculation field that can produce 2 different concatenations based on input? This is what I was thinking: 

(concat(${stakeholder_name},"-",${motive},"-",format-date(${engagement_date}, '%Y%m%d'))) or (concat(${employee_name},"-",${motive},"-",format-date(${engagement_date}, '%Y%m%d')))

 

I could always make a read only text field for each condition (stakeholder vs employee selected in the select_one question) and use a relevant statement to display the appropriate value. However, this will not stop a  ticket from being generated for the unselected value, the concatenation would simply lack either ${stakeholder_name} or ${employee_name} portion (hence the need for a conditional calculation). 

Any help is much appreciated! 

Chris  

NickAddison
New Contributor III
 
Has anything been updated since that would impact format-date(once(now())? I'm running Connect 3.19.104
I'm running into an issue where the format-data(once(now()) updates every time the record is viewed or opened for edits through the Inbox. Ultimately this changes the concatenation that is used to assign asset ids. 
TypeNameCalculation
dateTime date_timeformat-date(once(now()), '-%Y%m%d-%H%M')
textasset_idconcat(${fire_district_abrev}, ${date_time})
 
Ultimately, I need a concatenated asset id to be generated based on a pulldata() and a format-date(once(now()), 'xxx') that does not edit the originally assigned asset id when the item is edited through the Inbox.
 
 
Any help would be tremendous!
Nick
 
 
NoahFleishman
New Contributor

@IsmaelChivite I am using the Survey123 web application and figured out a way to create unique ID numbers. Not sure if this was already discussed in this thread.

  1. Date & Time Field: Add a date and time field. Set that date and time field's default value to "submitting date and time." Set the behavior of this field to not visible, read-only, and do not store answer.
  2. Decimal Date and Time Field: Add a new number field. Under the calculations tab section > Edit > Date and Time > select the name of the date and time field you created. This will turn the submitting date into a decimal date. Set the behavior of this field to not visible, read-only, and do not store answer.
  3. Manipulated Decimal Date and Time Field: Add a second number field. Under the calculations tab section > Edit > Number > select the name of the decimal date and time field you created. I then multiplied this number by 100,000 and set the "round to decimal places" to "0". Set the behavior of this field to not visible, read-only, and do not store answer.
  4. Input Mask: Add a single line text field. Under the calculations tab section > Edit > Number > select the name of the manipulated decimal date and time field. Set the "round to decimal places" to "0".  Set the input mask to "999-9999-999". Set the behavior of this field to whatever you would like.
  5. Grouping: For organization, I put all of the four above questions into a new collapsed group and set the behavior of this field to not visible.

I know this is not the most elegant solution, but it seems to have worked for me.

The resulting 10 digit number should be unique to a company as it changes as the submission date and time changes.