Filtering by Date in Autocomplete Search Questions

[GISAdminMLTMUA]

Hello,

I am using Survey123 Connect 3.23 to build a survey with an autocomplete search function. The question pulls from the results of another Survey123 form through a REST endpoint. I have the autocomplete search question working properly, but I need to add a filter to the results to only show entries from the past 7 days.

I have a 'submitted date' field in the 1st Survey123 form that I am trying to use to filter. The way I have tried to filter the results is: submitted date > current date - 604800, after the main text in the 'appearance' field.

I have done a good amount of research and trial/error and I cannot come up with the correct syntax to get this question working properly.

Thank you!

[ChristopherCounsell · Accepted solution]

search(tableName, searchType, searchColumn, searchText, filterColumn, filterText)

The above is the expression you need to use for search()

You can use a SQL Where expression within the tableName parameter to filter the table. e.g. if submitted_date is your field:

"layerName?url=https://yourserver/FeatureServer/0&where=submitted_date > CURRENT_TIMESTAMP - INTERVAL '7' DAY"

You would need to update your other parameters but it would look like:

search("layerName?url=https://yourserver/FeatureServer/0&where=submitted_date > CURRENT_TIMESTAMP - INTERVAL '7' DAY", searchType, searchColumn, searchText, filterColumn, filterText)

https://community.esri.com/t5/arcgis-survey123-blog/dynamic-choice-lists-using-search-appearance/ba-...

https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformsappearance.htm#ESRI_SECTION1_46A7...

[Town_of_Fuquay-Varina_GIS]

<woops can't delete>

[CalvinHarmin]

I'm not sure if this is relevant, but AGOL stores dates as epoch milliseconds. I noticed your math is using 7 days in seconds, 604800. I wonder if you would need to add three zeroes to that, otherwise you are only subtracting 0.007 days?