Account for apostrophes in attribute values in Arcade FeatureSetByRelationshipName function query

604
7
01-27-2023 11:59 AM
FrankMartin1
New Contributor III

We have data that includes values that contain apostrophes. When using the Arcade FeatureSetByRelationshipName function is fails because the sql query where clause that is created is structured like this:

name = 'Billy Bob's Cavern'  

The single apostrophe in the value needs to be escaped by a second apostrophe to be an expectable where clause:

name = 'Billy Bob''s Cavern' 

Can the FeatureSetByRelationshipName be updated to account for this situation?  Is there another way to handle this short of changing the value to remove the apostrophe?

 

 

0 Kudos
7 Replies
JustinReynolds
Occasional Contributor III

Try substituting an Arcade variable in the SQL def.

 

var features = FeatureSet('{"fields":[{"alias":"name","name":"name","type":"esriFieldTypeString"},{"alias":"ELEV_m","name":"ELEV_m","type":"esriFieldTypeInteger"}],"spatialReference":{"wkid":4326},"geometryType":"esriGeometryPoint","features":[{"geometry":{"spatialReference":{"wkid":4326},"x":-151.0063,"y":63.069},"attributes":{"name":"sponge bob' + `'` +  's","ELEV_m":6168}}]}')

var name = "sponge bob's"
var sql = "name=@name";

var ffset = Filter(features, sql)
ffset

 

JustinReynolds_0-1674852705085.png

 

- Justin Reynolds, PE
FrankMartin1
New Contributor III

Thanks @JustinReynolds,

Since this function uses the feature which is immutable as an argument, i don't think the value can be changed.  I can see in the Field Maps log that the query fails because the apostrophe isn't escaped.  Since the apostrophe is a legal character, I was hoping that the Arcade function,  FeatureSetByRelationshipName, would account for this since it uses a string to match the feature.  

Thanks again for your idea.

0 Kudos
JustinReynolds
Occasional Contributor III

Ah.  I see what you mean.  FeatureSetByRelationshipName takes an inputFeature.  And you are passing $feature.  

The docs for this function state that this feature must be coming form a feature service... that isn't very clear to me as to whether or not you must use $feature or if you could getaway with Feature(<some modified feature>).

You could try to handle this by creating a modified Feature from $feature. Loop through your features attributes and remove apostrophes. Then create a Feature object from that modified feature dict.  Then pass that to FeatureSetByRelationshipName.  I'm not convinced that something other than $feature will work, but might be worth a shot.  

Maybe I'm reading your question wrong.  Maybe it won't be possible if you are needing to match on the field that contains apostrophe...

Testing this in the ArcGIS Playground... I can see the payload escaping the apostrophe.

JustinReynolds_0-1675465293480.png

 



By the way, this sounds like a bug.

- Justin Reynolds, PE
0 Kudos
FrankMartin1
New Contributor III

Yes, I agree.  I think it's a bug.  Here is the log from the Field Maps application.   This function is being used in expression to retrieve attributes from a specific feature to prepopulate attributes in an inspection report (related table). You can see that the apostrophe is not escaped in the where clause.   FYI, I replaced some of the information with xxxx for security.

Name: expr/park-or-preserve-name

Error: Error Domain=com.esri.arcgis.runtime.services.error Code=500 "Syntax error [(1=1) AND (caves = 'Pricilla's Well Cave')]." UserInfo={NSURL=https://xxxxx.xxx/maps/rest/services/Hosted/Parks_xxxxx_Caves/FeatureServer/0/query?outSR=3857&retur..., responseJSON={ error = { code = 500; details = ( ); message = "Syntax error [(1=1) AND (caves = 'Pricilla's Well Cave')]."; }; }, AGSCredentialErrorKey=username: xxxxxx

token: GH_VLtRuJDZjSejMCR4klNuXZw_K4UU98HqJ-H-7QzPeMv8p6_D7bNwIXUhuYrSwNckjMkl89iAehiTGe4Xpm-ySRQkHxm30a7z9pgMJtwQrHM2sIVZYl2YaeegJeb4c_ziJMSc42nevHo4Au599YVlLXrwnFUo0DGqiWJO0w6WNnfK_g_0X4gApSOc7dIyDJtut0jJDpDY2LMT6q6HzVMHZAE5giYaZe8VJlCz1dMnIH9N-H3BMmkxdojv6Jy3I8hiVer58Y1JqOyyz0HdzAH7Qg5r7Nwkk5qjj_acxQdQ.

tokenURL: xxxxxxxx

tokenExpiration: 2023-02-03 23:24:10 +0000 , NSLocalizedDescription=Syntax error [(1=1) AND (caves = 'Pricilla's Well Cave')].}

0 Kudos
JustinReynolds
Occasional Contributor III

It is also not escaped in the url encoded request.  You would see ...%27Pricilla%27%27s....

In the browser it is escaped.  In the runtime it is not.  So the bug seems to be in the runtime... which is then being reported via Field Maps.

I think I was running into this before with Filter.  Where SQL escapes correctly in the browser.  So testing it in the authoring app works... but we get an error in Field Maps.  In this case I had the option of removing the single quote with replace in my $feature and removing them by hand from the dataset containing the features I would be fetching to get around this.

Have you submitted a bug for issue in the ArcGIS Runtime?

- Justin Reynolds, PE
0 Kudos
FrankMartin1
New Contributor III

Yes.  I have submitted a case for this issue.  In the meantime, I'll try your approach to create a workaround until this gets addressed.  

Thanks so much for you insights.  I'll post any resolution that I receive from Esri.

Regards,

Frank Martin

FrankMartin1
New Contributor III

@JustinReynolds ,

Just wanted to let you know that Esri has fixed this issue:

Updates have been made to the following defect which you are associated with:
BUG-000157546 - Querying a feature in the ArcGIS Field Maps mobile app fails if there is an apostrophe in the attribute data of the feature called by an Arcade expression.

Status: Fixed (Learn More)
Version Fixed:  23.3.0
Alternate Solution:  

Remove the apostrophes from the features and do not use the Arcade expression since querying works.

Regards,

Frank