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

[Teresa_Blader]

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},"'"))

[CodyPatterson]

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}, '"'))

[Teresa_Blader]

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.

[CodyPatterson]

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:

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

[Teresa_Blader]

Sadly, "replace" is not a Survey123 formula 😭

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

[CodyPatterson]

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)), "'"))

[Teresa_Blader]

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.

[abureaux]

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

[Teresa_Blader]

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

[AnneMorrisMC]

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

whereid

hidden

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