Inbox Filter by Date in Query Expression

[MarioFederis1]

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

[JamesTedrick · Accepted solution]

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

[MarioFederis1]

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

[AprilChipman]

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)

[JamesTedrick]

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

[AprilChipman]

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

[AprilChipman]

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.

[ryanEvanczyk]

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!

[JamesTedrick]

Hi Ryan,

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

[ryanEvanczyk]

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!