Group By in Data Expressions?

[GregKeith]

I have a List widget that lists n number of sites from a Survey123 feature layer. Each site has multiple records associated with it. I'd hoped to use DISTINCT or something similar to get all the unique names, but couldn't figure out a way to do that. Instead, made a new layer with just the sites to populate the lists. Klunky, but works as long as I remember to add a new feature whenever a new site is added.

I'd like to add a text box to each list item showing the total number of records for each site. This would have to come from the original S123 layer, not the klunky one. In SQL or Arcade this can be done with 'GROUP BY site', but I can't figure out how to do this in Experience Builder. Is it possible, and if so, how? I found this thread, but when I try that - COUNT({site}) - I get an invalid expression error. Thanks!

[jcarlson · Accepted solution]

Not in Experience Builder, sadly. If your popup or feature label used Arcade, Experience Builder can see that, but it can't evaluate anything itself.

[GregKeith]

😢

Thanks Josh.

[JustinH]

Greg- 

I know this is an old post but there is a way to do this. You just have to create a data view based off of a query.

For example: Site 1 View = Filter -> [Site Field] is 'Site 1' 

Then, when you are in the text box, toggle connect to data and choose your new data view rather than default. You can then use a COUNT expression to get the total number of records per site. 

I do think it is more work/tedious but it becomes pretty helpful when creating the application. I have had to do this for many records in a messy dataset. I believe in you!