topic Re: [Arcade] How to retrieve all values which has a common ID in another collumn ? in ArcGIS Online Questions

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

JasonBOCQUET — Mon, 15 Jul 2024 13:59:30 GMT

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 🙂

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

jcarlson — Mon, 15 Jul 2024 14:40:31 GMT

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.

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

JasonBOCQUET — Mon, 15 Jul 2024 14:30:20 GMT

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 ${$ ?

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

jcarlson — Mon, 15 Jul 2024 14:40:12 GMT

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.

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

JasonBOCQUET — Mon, 15 Jul 2024 14:48:05 GMT

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.

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

jcarlson — Mon, 15 Jul 2024 14:54:35 GMT

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".

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

JasonBOCQUET — Mon, 15 Jul 2024 15:03:33 GMT

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

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

jcarlson — Mon, 15 Jul 2024 15:16:38 GMT

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

Is the siren field text, or numeric?

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

JasonBOCQUET — Mon, 15 Jul 2024 15:28:12 GMT

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 ?

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

JasonBOCQUET — Mon, 15 Jul 2024 15:37:07 GMT

@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 += ""+match_arr+"" 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.

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

jcarlson — Mon, 15 Jul 2024 15:39:39 GMT

Put "Concatenate(match_arr, '\n')" in the text object at the end. You're putting the entire array into your HTML object, you don't really need to do that.

return {
  type: 'text',
  text: Concatenate(match_arr, '\n')
}

You could also try replacing \n with .

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

JasonBOCQUET — Mon, 15 Jul 2024 15:42:46 GMT

Great, your solution work too.

Thanks for your help. Any idea of why I can"t see other people ? Actually he show me only the people which i click on, it's not very interesting.

I miss something to make the other rows with the same ID in my popup ?

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

jcarlson — Mon, 15 Jul 2024 15:46:25 GMT

I'm not sure I understand. The way it's written, any row with the same ID will be included in the filtered set. Are you testing this against an ID that you know has more than one feature with that value?

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

JasonBOCQUET — Mon, 15 Jul 2024 16:22:15 GMT

90% of the dataset have min. 1 other people who have the same ID.

When i click on point which have another point on the map with the same ID, i always have the name of the current point i've clicked who appears on the pop-up box.

I do not have the other point who have the same ID.

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

JasonBOCQUET — Mon, 15 Jul 2024 16:30:26 GMT

@jcarlson Ok i find why it make a problem. My map is filtered to appears only one type of people (they are classified in 2 under-class).

That's why i do not see other one on the pop-up box, it's because they don't appears on the map.

it's not possible to get they're information (from the second under-class that i don't show on my map) on the pop-up box without show them on the map ?

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

jcarlson — Mon, 15 Jul 2024 16:33:42 GMT

Change your expression to use FeatureSetByPortalItem instead, that will go around the filter.

var fs = FeatureSetByPortalItem(
  Portal('your portal url'),
  'itemID of the layer',
  0, // or whatever the layer index is
  ['ets_siren', 'ets_date_creation'],
  false
)

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

JasonBOCQUET — Mon, 15 Jul 2024 16:47:24 GMT

Oh great, it works very well !

Big thanks for your help.

I'm gonna see now if I can integrate this kind of results in a charts to make it more beautiful in term of visualization.

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

JasonBOCQUET — Tue, 16 Jul 2024 08:29:33 GMT

Hi @jcarlson i want to know if it possible to ameliorate the code.

Because we are using this line

var match_arr = []

The result's rendering is like : ["Agence User","FTO User","Workplace User","IT User"]

it's not very aesthetic, i want to set more code around this to make the result more "friendly user"

In another case I use a code to retrieve a list of transaction provided by a relationship class, here is the code :

var transactionsinv = FeatureSetByRelationshipName($feature,"SDE.TRANSACTIONS_INV") var info3 = '' for (var f in transactionsinv){ info3 += `<div style='text-align: left;'> Date : ${DefaultValue(f.TRA_TRIMESTRE,'')} ${DefaultValue(Text(f.TRA_DATE,'Y'), 'Pas de date de transaction recensée')} Acquéreur : ${iif(isEmpty(f.TRA_ACQUEREUR), "Pas d'acquéreur recensé", f.TRA_ACQUEREUR)} Surface : ${iif(isEmpty(f.TRA_SURFACE),'Pas de surface rencensée', Text(Round(f.TRA_SURFACE,0),'#,###')+" m²")} Montant : ${iif(IsEmpty(f.TRA_MONTANT),'Pas de montant recensé',f.TRA_MONTANT)+ " M€"} </div>` } var newVariable3 = iif(isempty(info3),"Pas de transactions",info3); HTML += ""+newVariable3+""

It is possible to change the line var match_arr = [] to be emancipated from the table side rendering ?

I want to get closer to the rendering that the code above gives me