Check for holidays in submitted dateTime

[AnnaStefanowicz]

I'm working on a permit request form in Survey123 Connect where submitted beginning and ending event dates (dateTime) need to be checked if they fall on a Memorial Day, Independence Day or Labor Day (many years in the future). If they do, dates are accepted but a note needs to be displayed that the selected dates fall on a holiday. Then, the form needs to assign a "special permit" category to the submitted request.

Any ideas, tips, suggestions how to achieve it?

[MobiusSnake]

I think you could do something like this...

Prepare a CSV of Holidays

Create a CSV file called holiday_dates.csv with two columns, datecode and is_holiday, and add it to your survey's media folder.  You'll want to punch in rows like this for your holidays however many years ahead:

datecode	is_holiday
20240527	1
20240704	1
20240902	1

Create a hidden question to create a datecode

In your form, create a hidden text question (with a null field type) that uses this formula to convert your date/time question to the 8-digit format above:

format-date(${your_date_field}, '%Y%m%d')

Create a hidden question for a holiday flag

Create a second hidden question (also null field), this one will use the pulldata function to check your CSV along with an if() to return a 0 if the row isn't found:

if(pulldata('holiday_date', 'is_holiday', 'datecode', ${date_disp}) = 1, 1, 0)

You should be able use this second hidden question as a 0/1 flag to trigger other questions, like a note to users and a special permit flag in another field.

You could probably combine the two hidden questions together into one but I think it'd be easier to debug as two questions if something goes wrong.

Also, you could use pulldata to access a service rather than a CSV, which is more future-proof (you could update the rows without republishing the survey) but as long as your CSV has many years punched in, you're probably fine.

[RobertAnderson3]

This is the way I think you'd have to do it as well, you would need to make the list of dates yourself.

The one thing I will mention is to the last point from Mobius, you can use a CSV and host it in ArcGIS Online and link the content to the survey. This allows you to only update the CSV instead of the form when needed. Unless there is another way that using a service instead of a CSV is better future proofing?

[abureaux]

This is the way.

[AnnaStefanowicz]

Thank you for your helpful tips.

I still have trouble triggering a note using the output of the if() statement. In below, the second row is a note with a statement in the relevant column. But that note doesn't appear in the form when it should be. The relevant statement calls for the text field name where the if() statement is used.

[MobiusSnake]

Is your first question (the one with the pulldata calculation) set to hidden?  If so, could you temporarily make it something like an integer-type question, and check that it's returning the correct 1/0 value based on whether the date is a holiday?

If the first question is working as expected but your note's relevant field still isn't working, maybe you could try setting your note's relevant field to this (using your field name rather than mine):

pulldata('holiday_date', 'is_holiday', 'datecode', ${date_disp}) = 1

If this works, and you don't need the "is_holiday" check anywhere else, you can probably just delete the first question and go with this approach instead.

[AnnaStefanowicz]

The reason for my troubles was that I linked the csv file to the survey, instead of saving it in the media folder. Your original solution works well in the Connect app when the csv file is in the media folder. However, after publishing the survey, I get below error in the browser. I also get the same error when having the csv file linked to the survey, instead of having it in the media folder. Any ideas what's causing this error?