Account for apostrophes in attribute values in Arcade FeatureSetByRelationshipName function query

[FrankMartin1]

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?

[JustinReynolds]

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

[FrankMartin1]

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.

[JustinReynolds]

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.

By the way, this sounds like a bug.

[FrankMartin1]

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')].}

[JustinReynolds]

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?

[FrankMartin1]

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