I would like to coalesce date fields in Survey123. Is this possible? Explanation and use case described below.
Note: This was raised as a support case but I was referred here to suggest it as an idea (if coalescing of dates is not already supported).
I've produced a survey form using Connect, published publicly and accessed solely via the web form for submissions. The user is given three options of date entry (reason for this is given below):
Date (d/m/y), Month (m/y), Year (y)
Background calculations are then used to feed those results in to a single RecordDate field using coalesce functions (see below). These are all outside of a repeat (note we have had issues with coalesce functions within a repeat in recent support cases). The bind::esri:fieldType is set to esriFieldTypeDate. The feature is storing the individual date value (date_day, month_year or date_year) but not performing the coalescing function - leaving RecordDate blank. Testing without setting the bind::esri:fieldType to esriFieldTypeDate results in successful coalescing, but stores the output as a string rather than a date. As another workaround, is there a way we could convert this coalesced string to an appropriate date format?
The use case for this is we want a strong understanding of the accuracy of dates submitted with wildlife records. This is important for analysing phenology.
Summary of XLSForm design of what we have tried so far:
| type | name | label | calculation |
|---|
| date | date_day | Date | |
| date | monthyear | Month & Year | |
| date | date_year | Year | |
| hidden | coalescedate1 | Date (coalesced 1) | coalesce(${date_day},${monthyear}) |
| hidden | coalescedate2 | Date (coalesced 2) | coalesce(${date_day},${date_year}) |
| hidden | coalescedate3 | Date (coalesced 3) | coalesce(${coalescedate1},${coalescedate2}) |
| hidden | RecordDate | Record date | format-date(${coalescedate3},'%m/%d/%Y') |
