Arcade Script to join table to point Layer for pie chart ERROR

[ALICEANSTEE]

Hi. I am currently trying to join a table to a point hosted layer to display additional fields in a pie chart in dashboard. My current script ( I used a little of it from GitHub) doesnt show any errors in the actual script but comes up with this error :

Expected "!=", "(", "*", "+", "-", "/", "<", "<=", "<>", "=", ">", ">=", "AND", "OR", [ \t\n\r], or end of input but "5" found.

Does anyone know where the errors lies? I can't locate any wrong syntax so thinking it may be another issue? My script is below (pointfs is the feature set and tablefs is the table I want to join to - based on a common attribute that holds Postcodes (PostcodeSector)) :

(I've removed the portal link at the top for privacy). 

var portal_Link = Portal('xyz');
var pointfs = FeatureSetByPortalItem(portal_Link,'17d25668c1ee4904ac2920558175f291',0,["*"], false);
var tablefs = FeatureSetByPortalItem(portal_Link,'76a135fad8db4da7bc2932ec7c58dd5b',0,["*"], false);

var features = [];
var feat;

for (var t in tablefs){
var tableID = t["user_postsector"]
for (var p in Filter(pointfs, "Postcode_Sector= "+tableID)){
feat = {
attributes: {
organisation: tableID,
PostcodeSectorAv: p["user_postcodesectoraverage"],
PostcodeSectorCount: p["user_postsectorcountvalued"],
GSS_CODE: t["GSS_CODE"]
}
}
Push(features, feat)
}
}

var joinedDict = {
field: [
{name: "organisation", type: "esriFieldTypeString"},
{name: "PostcodeSectorAv,", type: "esriFieldTypeInteger"},
{name: "PostcodeSectorCount", type: "esriFieldTypeInteger"},
{name: "GSS_CODE", type: "esriFieldTypeString"},
],
'geometryType': '',
'features':features
};

return FeatureSet(Text(JoinedDict));

[jcarlson]

That error comes from a SQL statement, which in this expression, is your Filter function.

Generally, functions that use a SQL expression with a dynamic value, like your table ID, should reference the value in one of two ways.

One is to establish the variable, then use @Variable_name in the SQL string:

var tableID = t['user_postsector']
for (var p in Filter(pointfs, "Postcode_Sector=@tableID")) {
  …
}

The other is to use a template literal. I particularly like this approach myself, as you don't need to create a variable just for one thing.

for (var p in Filter(pointfs, `Postcode_Sector=${t['user_postsector']}`)) {
  …
}

See if either of those help.

[ALICEANSTEE]

Hi.

Thanks thats helped move past the SQL error - appreciate the help. I am now getting this error: 

Execution error - Line : 44, 7: Invalid parameter

I've tried to get distinct values in a new subset of the house price estimations per postcode sector to try and make the script quicker - I'm not sure if this is then causing an error or if its because the output is a dictionary? I get the same output when I end the script with : return FeatureSet(Text(joinedDict))

Any additional help would be great! 

var portal_Link = Portal('xyzx');
var pointfs = FeatureSetByPortalItem(portal_Link,'17d25668c1ee4904ac2920558175f291',0,["*"], false);
var tablefs = FeatureSetByPortalItem(portal_Link,'76a135fad8db4da7bc2932ec7c58dd5b',0,["*"], false);

var fields = ["user_postsector", "user_postcodesectoraverage", "user_postsectorcountvalued"];
var DistinctTablefs = Distinct(tablefs, fields)

var features = [];
var feat;

for (var t in DistinctTablefs){
var tableID = t["user_postsector"]
for (var p in Filter(pointfs, "organisation=@tableID")){
feat = {
attributes: {
organisation: tableID,
PostcodeSectorAv: t["user_postcodesectoraverage"],
PostcodeSectorCount: t["user_postsectorcountvalued"],
GSS_CODE: p["GSS_CODE"]
}
}
Push(features, feat)
}
}

var joinedDict = {
field: [
{name: "organisation", type: "esriFieldTypeString"},
{name: "PostcodeSectorAv,", type: "esriFieldTypeInteger"},
{name: "PostcodeSectorCount", type: "esriFieldTypeInteger"},
{name: "GSS_CODE", type: "esriFieldTypeString"},
],
'geometryType': '',
'features':features
};

return FeatureSet(joinedDict);

[ALICEANSTEE]

Update as of 05/07/2024. I've check the return of the features just before the creation of the var joinedDict and it returns the multiple dictionaries with a correct join (fab!). I've now been trying to merge the dictionaries together to create one.

This is one of the individual dictionaries: 

any ideas on how best to do this? I assumed iterating through each would be best?

Thanks!

[jcarlson · Accepted solution]

In your joinedDict object, you have a key "field". That should be "fields".

I can't tell from your post which line of your code is line 44, so I don't know what parameter your expression might be referencing.

[ALICEANSTEE]

Hi,

thanks for responding. Line 40 is the last line 'return FeatureSet(joinedDict);'

Thanks

Alice