Do This Not That - Attribute Rule Arcade Tips

02-04-2021 04:15 PM
Esri Contributor
1 0 398

I noticed that lots of customers hand-roll the where clause filter predict manually specially when looking up a set of values. Example..



var fsTable = FeatureSetByName($datastore, "table") 
var objectIds = [1,2,3,4,5]
var whereClause = "objectId in ("
for (var oid in objectIds){
    whereClause += "'" + objectIds[oid] + "',"
whereClause = Left(whereClause, Count(whereClause) - 1)
whereClause += ")"
fsFilter = Filter(fsTable, whereClause);
///rest of code



You can use variables in where clause filter and Arcade will parse the variable and use the correct SQL syntax. Whether its date, string or integer. Here is how to write the same script above but much simpler. Use @ symbol with variable in the where clause.



var fsTable = FeatureSetByName($datastore, "table") 
var objectIds = [1,2,3,4,5]
var whereClause = "objectId in @objectIds";
fsFilter = Filter(fsTable, whereClause);



That will be translated to the following SQL


OBJECTID IN (1,2,3,4,5)


And if the field is string or date , Arcade will add single quotes as necessary and format date function based on the target DBMS.


Note that @ doesn't support expressions so you can only put variables. E.g. you can't do "objectId in @collection[1].list"  



Will keep updating this blog for more tips that helps!


Tags (1)
About the Author
Software Engineer, Author of several GIS books and Software Engineering Content Creator on YouTube and podcast