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

633
9
02-02-2024 11:48 AM
Teresa_Blader
Occasional Contributor III

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

Teresa_Blader_0-1706902755014.png

 

Teresa Blader
Olmsted County GIS Specialist
0 Kudos
9 Replies
CodyPatterson
Occasional Contributor III

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}, '"'))
0 Kudos
Teresa_Blader
Occasional Contributor III

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.

Teresa Blader
Olmsted County GIS Specialist
0 Kudos
CodyPatterson
Occasional Contributor III

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}, "'", "''"), "'"))
0 Kudos
Teresa_Blader
Occasional Contributor III

Sadly, "replace" is not a Survey123 formula 😭

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

Teresa Blader
Olmsted County GIS Specialist
0 Kudos
CodyPatterson
Occasional Contributor III

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

0 Kudos
Teresa_Blader
Occasional Contributor III

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.

Teresa Blader
Olmsted County GIS Specialist
0 Kudos
abureaux
MVP Regular Contributor

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

0 Kudos
Teresa_Blader
Occasional Contributor III

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

Teresa Blader
Olmsted County GIS Specialist
0 Kudos
AnneMorrisMC
New Contributor II

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. 

 

textperidperson id  hidden    pulldata("@layer", "getRecord",  "https://myportal/hosting/rest/services/Hosted/person/FeatureServer/0", ${whereid})     10000                   
notewhereidwhereid  hidden    concat("lan_id ='", ${lan}, "'")                            
0 Kudos