topic Re: Inbox Filter by Date in Query Expression in ArcGIS Survey123 Questions

Inbox Filter by Date in Query Expression

MarioFederis1 — Mon, 29 Jan 2018 20:57:18 GMT

Is it possible for the inbox to show submitted forms only from the last 5 days? Perhaps through the inbox filter query expression which looks at a date field (example below).

My organization has over 3000 entries in survey123, and whenever a user refreshes their inbox, it takes an extremely long time to load. They only need to edit the most recent submissions

Re: Inbox Filter by Date in Query Expression

JamesTedrick — Mon, 29 Jan 2018 21:40:21 GMT

Hi Mario,

This type of query should be possible, though it currently takes a little debugging to get the syntax correct. In the query expression language, there are the keywords CURRENT_TIMESTAMP and CURRENT_DATE (Standardized SQL functions in ArcGIS Online—ArcGIS Online Help | ArcGIS ). These are used by ArcGIS Online's filter to create a date-bound query. For the example of the last 5 days, the query is:

requestDate BETWEEN CURRENT_TIMESTAMP - 5 AND CURRENT_TIMESTAMP

Re: Inbox Filter by Date in Query Expression

MarioFederis1 — Wed, 31 Jan 2018 12:59:46 GMT

Thanks James! It worked. I assumed it was possible but just didn't know what the calls were.

Re: Inbox Filter by Date in Query Expression

AprilChipman — Wed, 12 Feb 2020 17:17:24 GMT

I'm not having any luck with this expression, and I think it may be due to my field type. My field name is 'date' and it is a esriFieldTypeDate. Can I still filter the Inbox for the last 10 days?

If not, any suggestions on a workaround?

(I'm in Enterprise 10.6.1 and using Survey123 Connect 3.7.62)

Re: Inbox Filter by Date in Query Expression

ryanEvanczyk — Sun, 16 Feb 2020 20:07:02 GMT

I am trying to write a query so that the inbox shows only records from the current day. I have tried querying a date field - event_date=CURRENT_DATE which gives the code 400 invalid parameters. I read in the prepare to edit blog post that said you couldn't use a date field type with this query and it must be formatted yyyy-mm-dd. So I made a hidden type, then set it as a string, and in the calc column formatted the dateTime field above to be yyyy-mm-dd. Then set the query as event_date=CURRENT_DATE again and it doesn't throw the error, but no surveys show up. Not sure if I need to mess with the SQL standard or non-standard queries, or just set a range as one day?

Thanks!

Re: Inbox Filter by Date in Query Expression

JamesTedrick — Tue, 18 Feb 2020 20:12:46 GMT

Hi April,

I'll note that date is normally a reserved keyword - can you confirm the name of the field from the REST API?

edit_date between CURRENT_DATE - 10 AND CURRENT_DATE as a sample query works for me in ArcGIS Enterprise

Re: Inbox Filter by Date in Query Expression

AprilChipman — Tue, 18 Feb 2020 20:18:49 GMT

From what I understand, the field name is indeed "date"

Re: Inbox Filter by Date in Query Expression

JamesTedrick — Tue, 18 Feb 2020 20:20:30 GMT

Hi Ryan,

edit_date >= CURRENT_DATE should be used (remember, the time component of a date-time will cause equalities to fail)

Re: Inbox Filter by Date in Query Expression

AprilChipman — Tue, 18 Feb 2020 20:36:46 GMT

It worked!

Looking through my logs, I was trying to use 'CURRENT_TIMESTAMP' instead of 'CURRENT_DATE'.

date between CURRENT_DATE - 10 AND CURRENT_DATE is the query that works.

Re: Inbox Filter by Date in Query Expression

ryanEvanczyk — Thu, 20 Feb 2020 05:25:54 GMT

I used event_date>=CURRENT_DATE and the inbox didn't pull any sent surveys. It is a dateTime type, with a now() default.

When I add a -1 it pulls the current day and the previous day - event_date>=CURRENT_DATE-1 and functions as it should.

I tried adding a -0 and that didn't give me an error, but still didn't pull any sent surveys. I also tried getting rid of the default and changing it to a date type.

We don't send that many surveys a day, so it's probably fine. Do I just need to add something else at the end? adjust for UTC time?

Thanks for your help!

Re: Inbox Filter by Date in Query Expression

JamesTedrick — Fri, 21 Feb 2020 20:56:10 GMT

Hi Ryan,

<DATE_FIELD> >= CURRENT_DATE worked for my test case; you shouldn't need to adjust for UTC. You may want to double-check Query (Feature Service/Layer)—ArcGIS REST API: Services Directory | ArcGIS for Developers and check that the queries work at the REST API - Survey123 is doing this operation and it's a bit easier to debug from the REST (Service URL) web page.

Re: Inbox Filter by Date in Query Expression

ryanEvanczyk — Tue, 25 Feb 2020 18:55:28 GMT

Still wasn't working (valid query when checked in the rest api, but returned no results), I ended up trying the following with success:

incident_date>CURRENT_DATE-1 AND incident_date<=CURRENT_DATE

thanks for your help!

RyanE

Re: Inbox Filter by Date in Query Expression

BreannaOndich — Mon, 21 Dec 2020 18:25:07 GMT

Hello,

I followed this thread to come up with a date filter for the field name CreationDate as follows:

CreationDate <= CURRENT_DATE AND CreationDate > CURRENT_DATE – 9

I tried with with numbers 1-10 at the end, to find data collected within this time period (1 to 10 days ago) but I am looking for the Inbox to show dates up to one year ago (365 days). For some strange reason, anytime I enter a number higher than 10, the query fails. I do not get any errors, but my Inbox downloads all records gets maxed out with 1,000 records (which is the main issue, but I cannot figure out how to edit my service definition record limit to be over 1,000 because I cannot figure out how to get a token). Why would any number over 10 cause the query to fail?