Inbox query syntax with username variable

[AndrewMoskaLee]

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

[JamesTedrick]

Hi Andy,

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

[AndrewMoskaLee]

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.

[ChristopherHaupt1]

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 

[JamesTedrick · Accepted solution]

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).

[MikeOnzay]

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.

[josevaldes1]

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.