Arcade Filter SLQ in Array Issue

[Jake_S]

Hey folks,

Long story long, I have a Many to Many documents table to parcel feature relationshisp. Using the FeatureSetByRelationshipClass function to get all the related Parcel GlobalIDs of the $feature (document) being edited and put them into an array. Then use that array to filter all the parcel features to later get some additional information. When I try to use that array in a Filter statement I get "Error: Invalid arcade expression, Arcade error: invalid where clause (GlobalID in ())".

Any thoughts?

Notes:

Line 17 I have to format the Array to adhere to SLQ.

If you don't you get the following error:
Invalid where clause (GlobalID IN (["{13C371EB-F80F-4B97-9718-D6CBCF37E8A5}","{4898F30A-5AEC-4BB1-A50C-59ED2FBCAF63}"])).

The format should look like (grabbed from ArcGIS Pro Select By Attribute SQL:

GlobalID IN ('{13C371EB-F80F-4B97-9718-D6CBCF37E8A5}', '{4898F30A-5AEC-4BB1-A50C-59ED2FBCAF63}')

// parcel_Documents Fields
var parcelDocumentsGlobalID_field = "GlobalID"

// parcel Fields
var parcelGlobalID_field = "GlobalID"
var parcelType_field = "type"

var relatedRecords = FeatureSetByRelationshipClass($feature, "parcel_Documents", [parcelDocumentsGlobalID_field], false);
var parcelFeatures = FeatureSetByName($datastore, "parcel", [parcelGlobalID_field ,parcelType_field], false)
var globalIDs = []
for (var record in relatedRecords){
    var rg = record.GlobalID;
    Push(globalIDs, rg);
}

var relatedParcelFeatures = Replace(Replace(Replace(globalIDs, '"', "'"),"[",""),"]","")

var filteredParcelFeatures = Filter(parcelFeatures, "GlobalID IN (" + relatedParcelFeatures + ")");

var cnt = Count(filteredParcelFeatures)

return {
    "errorMessage": Text(cnt)
}

Any assistance from the community would be great. Thanks in advance!

[jcarlson]

Looking at your code, it seems like the "replace" step is entirely for getting the array-as-string to be formatted differently. Take a look at using Concatenate instead.

Also try using Console to check on intermediate outputs.

var globalIDs = []

for (var record in relatedRecords){
  Push(globalIDs, record.GlobalID)
}

var sql = `GlobalID IN('${Concatenate(globalIDs, "','")}')`

Console(sql)

var filteredParcelFeatures = Filter(parcelFeatures, sql)

[Jake_S · Accepted solution]

Thanks @jcarlson I also found that the issue is a few things while troubleshooting. 

1. You should always use this syntax when you need to inject variables 

'GlobalID IN @relatedParcelFeatures'

2. I noticed that when using the logic above I get errors with the Replace. If I ignore the replace and just use my array of GlobalIDs I pass validation. Yet get an error on save. Turns out during validation of the expression in the expressions window the code is run on the first object in the $feature. During save there is a "fake" row that is created and run against. So you rule can pass validation because your code is being run against an object that may meet all the code requirements but not save. So you have to do some error handling like checking if the array is empty.

if (Count(globalIDs)<1){
   return
    }

New code:

// parcel_Documents Fields
var parcelDocumentsGlobalID_field = "GlobalID"

// parcel Fields
var parcelGlobalID_field = "GlobalID"
var parcelType_field = "type"

var relatedRecords = FeatureSetByRelationshipClass($feature, "parcel_Documents", [parcelDocumentsGlobalID_field], false);
var parcelFeatures = FeatureSetByName($datastore, "parcel", [parcelGlobalID_field ,parcelType_field], false)
var globalIDs = []
for (var record in relatedRecords){
    var rg = record.GlobalID;
    Push(globalIDs, rg);
}

if (Count(globalIDs)<1){
   return
    }

var filteredLandStatusFeatures = Filter(landStatusFeatures, 'GlobalID IN @globalIDs' ) 

var cnt = Count(filteredParcelFeatures)

return {
    "errorMessage": Text(cnt)
}