Querying INBOX

2180
21
03-21-2018 10:58 PM
Highlighted
Occasional Contributor III

Hi all,

  1. How could I query empty values for a particular question?
  2. According to this article, I should be able to use Creator={$username} to query all records created by the currently signed-in user. But, I get this error instead.

Any suggestions

Thanks,

Gee

Reply
0 Kudos
21 Replies
Highlighted
Esri Frequent Contributor

Hi Geethaka,

Note there is an error in the current online documentation regarding the syntax used in the example. The $ is in the wrong place, it should follow the same format used in the xlsx form. The correct syntax is:

Creator=${username}

This has already been fixed in our next release of online documentation and will be updated with the 2.7 release.

Regards,

Phil.

Highlighted
Occasional Contributor III

Thanks, that worked

I have a select_one yes_no question, where I want to query all results that aren't yes. (i.e. Not equal to 'yes' OR equal to 'no' or 'empty')

Could you please explain how I could get this query to work in INBOX?

Sorry, I just couldn't get it to work (after multiple attempts)....

Thanks.

Reply
0 Kudos
Highlighted
Occasional Contributor III

^^^ Any suggestions on the above?

Thanks

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

Hi Geethaka,

By default the Inbox query only supports Standardized Queries as per the settings enabled on the organization by your Administrator. In our 2.7 release (end March) we have updated the documentation for the Inbox query. Here is a snippet of that updated documentation:

Prepare for editing existing survey data—Survey123 for ArcGIS | ArcGIS 

-------------------------------------------------

Additional documentation:

These queries also support SQL logical operators, allowing the query to perform more complex searches based on specific criteria. This example will return only results from users whose user names contain the user name of the logged-in user, as well as any text before or after it.

Creator LIKE '%' + ${username} + '%'

The following operators are supported by Survey123.

OperatorDescription

ALL

True if all of the subquery values meet the condition.

AND

True if all the conditions separated by AND are true.

ANY

True if any of the subquery values meet the condition.

BETWEEN

True if the operand is within the range of comparisons.

EXISTS

True if the subquery returns one or more records.

IN

True if the operand is equal to one of a list of expressions.

LIKE

True if the operand matches a pattern.

NOT

Displays a record if the condition or conditions are not true.

OR

True if any of the conditions separated by OR are true.

SOME

True if any of the subquery values meet the condition.

Caution:

To run queries using logical SQL operators, your organization's administrator must disable the Standardized Query security option for your organization to allow nonstandard SQL queries. For more information on the ramifications of disabling this option, see about standardized queries in the ArcGIS Server documentation. For more information on standard and nonstandard queries, see standardized SQL functions in ArcGIS Online

Highlighted
Occasional Contributor III

Hi Philip Wilson‌, James Tedrick‌,

Could you please provide a simple example using the NOT operator (i.e. Not equal to 'yes') that I could use to query INBOX results?

Thanks,

Gee

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

Have you disabled the Standardized Query security option?

Reply
0 Kudos
Highlighted
Occasional Contributor III

No, we wouldn't disable it due to the potential vulnerabilities.

Is there another way to query without disabling the Standardised Query security option?

Thanks

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

Hi Geethaka, unfortunately not, the only way to run queries using logical SQL operators is to disable that security option. This is controlled by AGOL/Portal organization settings and not something we can over-ride in the Survey123 app.

Phil.

Reply
0 Kudos
Highlighted
Occasional Contributor

Hi Philip and others, I'm afraid the example of use Like Operator and ${username} Creator LIKE '%' + ${username} + '%' which you mentioned and also documented at Prepare for editing existing survey data—Survey123 for ArcGIS | ArcGIS didn't work for me at all. Actually I'm not quite sure if the syntax is correct, why the % need be singled quoted? I couldn't find anywhere on REST API or other doc explained why the % need be singled quoted. It works fine if my query is like userid = ${username}, or userid LIKE '%jack%', where userid is the field in my form that captures the Portal user name. However, I got 500 Error (invalid where clause) when tried userid LIKE '%' + ${username} + '%'   ,  I also tried userid LIKE '%${username}%'  it didn't throw error but no result returned either.

Is it mandaroy to disable the Standarzed Query in order to make the LIKE operator and the ${username} varible working? I found the help page didn't clearly explain it.

Reply
0 Kudos