[Arcade] How to retrieve all values which has a common ID in another collumn ?

[JasonBOCQUET]

Hi,

I'm working sometimes on Arcade and i'm blocked by a point :

I have a table with some teams of people. Each people have a manager (the ID is MANAG001, MANAG002...).

Each people is represented by a point on a map. 

I want by clicking on one of the people be able to see on the pop-up box all of the other people who have the same manager that the guy who I clicking on. 

If I click on me, i'm able to see my manager is MANAG004 and i can see the other people who are managed by MANAG004 on a list or a table in the popup.

How can I do that with Arcade expression ? 

I've read that Arcade does not support direct interaction with the clicked point’s ID; so maybe i can"t do that ?

thx 🙂

[jcarlson · Accepted solution]

No direct interaction, that's true, but pulling the values into a list and displaying the text is fine.

var fs = FeatureSetByName($map, 'people table', ['name', 'manager_id'], false)

// filter features for matching manager id
var filt_fs = Filter(fs, `manager_id = '${$feature['manager_id']}'`)

// loop through matches, create list
var match_arr = []
for (var f in filt_fs) {
  Push(match_arr, f['name'])
}

return Concatenate(match_arr, '\n')

 This will spit out a list of names, each on their own line of text. Swap in your table / field names as needed, see how it works for you.

[JasonBOCQUET]

Maybe i miss something because it's not working already.

It seems to miss a ")" in your line 4 and i don't understand on the line 4 what does mean "

Filter(fs, `manager_id = '${$feature['manager_id']}'`

 why there is duble ${$ ?

[jcarlson]

Ah, you're right, there should be a closing parenthesis on that line. I'll edit the original post.

The filter statement is using a template literal, a kind of text string where you can insert values into it using ${…}. Anything inside the brackets evaluates as code, and is included in the output text. This expression, for instance:

var x = 'cool'

return `Arcade Expressions are ${Upper(x)}`

Would spit out the  text "Arcade Expressions are COOL".

There's a second "$" because that's how we tell Arcade to get the current $feature to access its attributes.

[JasonBOCQUET]

Ok so I try with my data :

var fs = FeatureSetByName($map, "Effectifs Société", ['$feature.ets_date_creation', '$feature.siren'], false)
var filt_fs = Filter(fs, `$feature.siren = '${$feature['$feature.siren']}'`)

var match_arr = []
for (var f in filt_fs) {
  Push(match_arr, f['$feature.ets_date_creation'])
}
return Concatenate(match_arr, '\n')

but it return nothing again. 

[jcarlson]

The expression in Filter is a SQL statement. The stuff in the ${…} is different because it's evaluated by Arcade first, but the rest of it has to be SQL, where there's no "$feature" object.

`$siren = '${$feature['siren']}'`

Also, you just need the field name in the square brackets. Otherwise, it's going to be looking for a field literally called "$feature.siren" instead of "siren".

[JasonBOCQUET]

Ok, i change all of the content in square brackets :

var fs = FeatureSetByName($map, "Effectifs Société", ['ets_date_creation', 'siren'], false)
var filt_fs = Filter(fs, `siren = '${$feature['siren']}'`)

var match_arr = []
for (var f in filt_fs) {
  Push(match_arr, f['ets_date_creation'])
}
return Concatenate(match_arr, '\n')

but it doesn't seems to change anything because my popup box still empty 

[jcarlson]

After the second line, try adding return Count(filt_fs), what does it show you?

Is the siren field text, or numeric?

[JasonBOCQUET]

Ok I miss something, my field is "ets_siren" not "siren" i'm stupid ^^'

So when I execute it seems to work; but i can't reach to show the result on my popup. Why I can't ?

[JasonBOCQUET]

@jcarlson i find how can i see the results, but it seems to show me only one result. He do not reach to have the others line with the same ID's

var fs = FeatureSetByName($map, "Effectifs Société", ['ets_date_creation', 'ets_siren'], false)
var filt_fs = Filter(fs, `ets_siren = '${$feature['ets_siren']}'`)

var match_arr = []
for (var f in filt_fs) {
  Push(match_arr, f['ets_date_creation'])
}
Concatenate(match_arr, '\n')
var HTML = ""
HTML += "<p style='text-align: center; line-height: 0.1;'><strong><span style='font-size: 30px; font-family: Calibri, sans-serif; color: rgb(1, 42, 132);'>"+match_arr+"</span></strong></p>"


return { 
	type : 'text', 
	text : HTML
}

sometime i got 2 times the name of the people concerned by the popup, but i never seen the other people appears.