topic Re: Inbox query syntax with username variable in ArcGIS Survey123 Questions

Inbox query syntax with username variable

AndrewMoskaLee — Tue, 14 Apr 2020 19:33:46 GMT

Was there a change made that affected the use of ${username} in the inbox query statement? I am exactly copying the syntax as it is displayed on the Survey123 help page:

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

The only difference is that instead of "creator", I am using 'crew_usernames' which is one of my fields. So its actually:

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

If I input my actual username instead of ${username} the expression works as expected. So that looks like:

crew_usernames LIKE '%Andrew.MoskaLee@dec.ny.gov_nysdec%'

So there is definitely a problem with the syntax surrounding the username variable. Does anyone know the correct syntax? Or have any other ideas?

Thanks,

-Andy

Re: Inbox query syntax with username variable

JamesTedrick — Tue, 21 Apr 2020 21:54:12 GMT

Hi Andy,

Your syntax looks correct. Can you verify that the username of the person is one present in the crew_assignments values?

Re: Inbox query syntax with username variable

AndrewMoskaLee — Tue, 21 Apr 2020 23:28:18 GMT

Yes the username is definitely correct. If I use crew_usernames=${username} it works as expected. I just have to make sure that there is only one username in the crew_usernames field.

Re: Inbox query syntax with username variable

MikeOnzay — Fri, 24 Apr 2020 04:42:32 GMT

Survey123 v3.7.57

I have the same question. I added username to a survey, re-published and submitted a survey. I queried the table through the service and turned on all fields. The value for username is blank for the survey I submitted.

Re: Inbox query syntax with username variable

josevaldes1 — Fri, 24 Apr 2020 12:18:00 GMT

hi i try to use this example and doenst work. what i do is this:

${username} like '%'||entidad||'%' in the query of the inbox.

for example : have company XY, and i want the users of XY company to see his submitions.

the usernames was created with XY_user1, XY_user2 ect. the in entidad i put XY.

If the username are the email then fill entidad using the subst function to extract and fill with the letters after the @ of the email.

Re: Inbox query syntax with username variable

ChristopherHaupt1 — Mon, 02 Nov 2020 16:28:14 GMT

Hi James Tedrick‌ Philip Wilson‌, Ismael Chivite‌

I would like to follow up on the question posted above.

I have the same troubles to use the Where statement as mentioned above:

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

A static query works as designed but the concatenation of the % and variable values does not work.

Below is the logfile of the Survey123 App. The survey is able to use the ${username} variable but is not resolving the where query correctly. I was not able to produce a correct query when using a variable.

Any Ideas or thoughts?

[AppStudio@11205 category="SurveyFolderPageInbox" function="replaceWhereVars" filename="SurveyFolderPageInbox.qml" line="813"] SurveyFolderPageInbox: replaceWhereVars where: "userlist LIKE '%' +${username} +'%'"
<15>1 2020-11-02T16:20:56Z coben ArcGISSurvey123 18392 - [AppStudio@11205 category="SurveyFolderPageInbox" function="buildQuery" filename="SurveyFolderPageInbox.qml" line="582"] SurveyFolderPageInbox: applySpatialFilter: false isMapActive: false map: null
<15>1 2020-11-02T16:20:56Z coben ArcGISSurvey123 18392 - [AppStudio@11205 category="SurveyFolderPageInbox" function="queryFeatures" filename="SurveyFolderPageInbox.qml" line="493"] SurveyFolderPageInbox: query body: {
"outFields": "OBJECTID,GlobalID,EditDate,Editor,CreationDate,Creator,MPL_Nr,Prod_Team_mpl",
"where": "userlist LIKE '%' +'resolvedUSERNAME' +'%'",
"outSR": 4326,
"returnGeometry": true,
"returnZ": true,
"returnM": false
}

Survey123 Connect: 3.11.123

Survey123 App Windows: 3.11.64 (and current iPhone and Android Version)

Hosted Feature Service in ArcGIS Online.

Thanks a lot and all the best

Chris

Re: Inbox query syntax with username variable

JamesTedrick — Mon, 02 Nov 2020 17:21:44 GMT

Hi Christopher,

The main issue is the use of '+' for string concatenation - that is not allowed in standardized sql feature services. Per Standardized SQL functions in ArcGIS Online—ArcGIS Online Help | Documentation , one of the following options can be used:

- CONCAT - when you want to join strings to gether

- POSITION - when you want to determine if a string occurs in a field. This is the one you likely want for your scenario (given a field of the name user list, I am guessing it contains 0+ usernames a record could be assigned to).