Arcade joined tables: joinKey

Bud — Mon, 07 Mar 2022 16:33:41 GMT

The Arcade developer FAQ says this:

How do I reference data from a joined table?

You can reference attribute data from a joined table using the square bracket syntax for referencing field names: $feature["joinKey.fieldName"].
// data from a joined table
var demVotes2016 = $feature["COUNTY_ID.VOTED_DEM_2016"];
var demVotes2012 = $feature["COUNTY_ID.VOTED_DEM_2012"];

// returns % change in votes from 2012 to 2016
Round( ( ( demVotes2016 - demVotes2012 ) / demVotes2012 ) * 100, 2);

Questions:

1. Where does the joinKey come from? Is it using a relationship class? Or an in-map join in a project file?
2. Does it only work if the two join fields have the same name?
3. How is this related to FeatureSets?
4. Where can we find more info?

Re: Arcade joined tables: joinKey

JohannesLindner — Mon, 07 Mar 2022 15:13:59 GMT

This seems misleading...

If you have a joined table, you have to reference the fields with "TableName.FieldName". This applies to Arcade and to Python (field calculation and labeling). The join field shouldn't play a role in this.

In the documentation's example, they probably have a feature class "Counties" with the primary key "CountyID" and a table "ElectionResults" with the fields "CountyID" (linking the table to the feature class), "VotedDem2012" and "VotedDem2016". They then joined the table to the feature class using "CountyID" as join field. To display the vote change in the layer's popup, you would then use this expression:

var demVotes2016 = $feature["ElectionResults.VotedDem2016"]; // table name, not key var demVotes2012 = $feature["ElectionResults.VotedDem2012"]; // returns % change in votes from 2012 to 2016 Round( ( ( demVotes2016 - demVotes2012 ) / demVotes2012 ) * 100, 2);

The join key shouldn't have anything to do with this. It is used to link two tables to each other and is chosen by the data owner when designing the tables / database. In database lingo they're called primary key and foreign key. In the ArcGIS environments, it can be used for joins, relates, and relationship classes.
see above
This has some impact on feature sets:

//If you have a relationship class between two tables, you can get the related records of a feature using the FeatureSetByRelationshipName() function. var related_features = FeatureSetByRelationshipName($feature, "RelationshipName") //If you don't have a relationship class, but still have the key fields, you can Filter() a feature set for the related records. var related_fs = FeatureSetByName(...) var primary_key = $feature.PrimaryKey var related_features = Filter(related_fs, "ForeignKey = @primary_key") //If you load a joined feature set (e.g. using the $map global or from a feature service), you have to use the bracket notation and you have to fully qualify the field names. var joined_table = FeatureSetByName(...) var f = First(joined_table) // these won't work // f.Field // f["Field"] // This could work f["TableName.Field"] // It could be that you have to _fully_ qualify the field name by using the names of the database and the dataowner: f["DatabaseName.DataOwnerName.TableName.Field"]

Re: Arcade joined tables: joinKey

Bud — Mon, 07 Mar 2022 16:36:46 GMT

Thanks!
I submitted an idea about improving the docs here: Arcade Documentation: Joined Tables

topic Re: Arcade joined tables: joinKey in ArcGIS Pro Questions

Arcade joined tables: joinKey

Re: Arcade joined tables: joinKey

Re: Arcade joined tables: joinKey