populate values from another table based on a common field using attribute rules

[MaryamRojhan]

I try to use attribute rules to populate values from a standalone table when a common field of 2 tables is the same.

here is the arcade code:

var sourceTable = FeatureSetByName($datastore,"Arten_gesamt", ["Art_wissenschaftlich"], false)
var lookupField = "Kategorie_Gruppe"
var commonField = "Art_wissenschaftlich"
var lookupValue = $feature["Art_wissenschaftlich"]
var matchedFeatures = Filter(sourceTable, commonField + " = " + lookupValue)
var matchedFeature = First(matchedFeatures)
return matchedFeature[lookupField]

It means that when the "Art_wissenschaftlich" is the same in 2 tables, it brings "Kategorie_Gruppe" values. But it doesnt work! i get this error on line 6:  Invalid where clause (Art_wissenschaftlich = )

the source table is the table where the values come.

I appreciate any guidance.

[JohannesLindner · Accepted solution]

Your Artwissenschaftlich field is most probably a text field, containing values like "Homo sapiens sapiens".

Your constructed SQL query looks like this:

Art_wissenschaftlich = Homo sapiens sapiens

But it should look like this:

Art_wissenschaftlich = 'Homo sapiens sapiens'

Arcade automatically cares about the correct syntax if you use the @ notation.

Other notes:

• You're not loading your lookup field
• You're need to check if your Filter() found any related features

var lookupField = "Kategorie_Gruppe"
var commonField = "Art_wissenschaftlich"
var sourceTable = FeatureSetByName($datastore,"Arten_gesamt", [commonField, lookupField], false)

var commonValue = $feature[commonField]
var matchedFeatures = Filter(sourceTable, `${commonField} = @commonValue`)
var matchedFeature = First(matchedFeatures)
return Iif(matchedFeature == null, "not found", matchedFeature[lookupField])

Or shorter, with hard-coded field names:

var sourceTable = FeatureSetByName($datastore,"Arten_gesamt", ["Art_wissenschaftlich", "Kategorie_Gruppe"], false)
var art= $feature.Art_wissenschaftlich
var matchedFeature = First(Filter(sourceTable, "Art_wissenschaftlich = @ART"))
return Iif(matchedFeature == null, "not found", matchedFeature.Kategorie_Gruppe)

[MaryamRojhan]

Thanks Johannes. It works!!! actullay I really dont know arcade that much. but it makes sense! I didnt think about loading the look up field!