topic Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma in ArcGIS Survey123 Questions

HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

Teresa_Blader — Fri, 02 Feb 2024 19:48:57 GMT

Hi! Trying to pulldata but in my concat WHERE clause - it doesn't work for strings that contain inverted commas >> '. How should I structure the WHERE clause if the string may have an inverted comma in it? Do I still use concat? Or should I be doing something else?

donator_name='Kaylie's Donation Center' must just be looking for donator_name='Kaylie' so returns nothing. 😭 If I remove ' , then it works.

pulldata("@layer", "getValue","geometry", "https://services7.arcgis.com/tC18nF87RnYBtzwI/ArcGIS/rest/services/Food_Rescue_View/FeatureServer/2", concat("donator_name =" ,"'",${donator_name},"'"))

pulldata("@layer", "getValue","attributes.donator_type", "https://services7.arcgis.com/tC18nF87RnYBtzwI/ArcGIS/rest/services/Food_Rescue_View/FeatureServer/2", concat("donator_name = '" ,${donator_name},"'"))

Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

CodyPatterson — Fri, 02 Feb 2024 20:09:58 GMT

Hey @Teresa_Blader

The reason it's only choosing Kaylie is due to using three single quotes ('), when a string is surrounded by single quotes, the inside cannot use a single quote without breaking the string. You must surround the string by double quotes to use a single quote, so "Kaylie's Donation Center" would work, but 'Kaylie's Donation Center' would just return Kaylie.

This may work better:

pulldata("@layer", "getValue", "geometry", "https://services7.arcgis.com/tC18nF87RnYBtzwI/ArcGIS/rest/services/Food_Rescue_View/FeatureServer/2", concat("donator_name =", '"', ${donator_name}, '"'))

Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

Teresa_Blader — Fri, 02 Feb 2024 20:15:49 GMT

Thanks for responding... sadly this does not work when I try it.😢

My understanding is that SQL WHERE clauses have to use '' surrounding a string and the only way to get it to return is by adding a secondary ' at the point of the text... which I'm not sure how to handle with a dynamic input - but maybe I misunderstood the blogs.

Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

CodyPatterson — Fri, 02 Feb 2024 20:49:47 GMT

Hey @Teresa_Blader

I'm sorry that didn't work, I was really hoping for it, I've seen using another single quote work, so two next to eachother, but in this case since we'd want to use it dynamically, it wouldn't work for us, we could make use of the replace function perhaps?

Maybe give this a try:

Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

abureaux — Fri, 02 Feb 2024 20:52:54 GMT

Don't know if you can encode those parameters -- not something I've needed to try. But that would likely fix the issue.

Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

Teresa_Blader — Fri, 02 Feb 2024 21:02:20 GMT

Sadly, "replace" is not a Survey123 formula 😭

https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm

Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

Teresa_Blader — Fri, 02 Feb 2024 21:03:10 GMT

I'm not sure what that means, encoding the parameters. What would that look like?

Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

CodyPatterson — Fri, 02 Feb 2024 21:06:04 GMT

Hey @Teresa_Blader

I seem to be on a roll today with less than optimal answers, here's a final attempt using this link here:

https://community.esri.com/t5/arcgis-survey123-questions/find-and-replace-character/td-p/836707

Here is the adjusted code:

pulldata("@layer", "getValue", "geometry", "https://services7.arcgis.com/tC18nF87RnYBtzwI/ArcGIS/rest/services/Food_Rescue_View/FeatureServer/2",
concat("donator_name = '", concat(substring(${donator_name}, 0, position("'", ${donator_name})), "''", substring(${donator_name}, position("'", ${donator_name}) + 1)), "'"))

Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

Teresa_Blader — Fri, 02 Feb 2024 21:29:50 GMT

I see where that is going, thank you!

As it is, it doesn't work 😅... but I'll see what I can finagle on Monday! I'll write an if statement earlier I think for it.

Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

AnneMorrisMC — Fri, 02 Feb 2024 22:24:04 GMT

Hi Teresa, I've read a lot that concat functions and pulldata functions can't be in the same line.

So I've put my concat in one text line and my pulldata in the next text line.

text

perid

person id

hidden

pulldata("@layer", "getRecord", "https://myportal/hosting/rest/services/Hosted/person/FeatureServer/0", ${whereid})

10000

note

whereid

hidden

concat("lan_id ='", ${lan}, "'")

Re: HELP! Survey123 Connect @pulldata WHERE clause with concat not working if strings contains inverted comma

RobynSnookCCB — Fri, 06 Sep 2024 18:17:45 GMT

Ever figure this out? I'm running into the same issue and can't seem to get a work around. Thanks