Hello,
I have a list with a data expression that correctly returns the count of various sample_types. I'd like to also return two non grouped fields, site_name and sample_date. I don't need statistics on them. They wouldn't be used for display, but only to allow other elements to filter by either of them.
I'm not sure this can be done; it can't in SQL, afaik, due to the underlying set theory. If I add them to the GroupBy clause, I get every unique combination of all three fields, which I don't want. Is there a way to accomplish this somehow? If not, that would be good to know as well. Thanks.
Solved! Go to Solution.
Ah. For that, you need two widgets:
The Table Widget:
The Category Selector Widget:
AFAIK, each field in a GroupBy (be it in Arcade or SQL) is either part of the group or part of the statistics.
If I add them to the GroupBy clause, I get every unique combination of all three fields, which I don't want
Well, that seems logical to me. Say we have the sample types ["S1", "S2", "S3"] and the sites ["A", "B", "C", "D"] (we'll skip the dates)
You can get statistics on the sample types ("There were 123 samples of type S1, 2 samples of type S2, 34 samples of type S3").
You can get statistics on the type and site: "There were 25 S1 samples at A, 50 at B, 25 at C, 23 at D. etc."
If you don't put the sites into the group too, the sample type loses its relationship to the site. So how do you want to relate them? Or do you just want to return distinct values for sites and dates?
Thanks @JohannesLindner . Ideally I want the list to show counts for all sample types for all records (which it does now) but also have the ability to be filtered to just a specific site via the Action section of another list of sites. This requires a site field in the sample count list, but I don't want to group by that.
Current
Sample Type | Sample Count |
Dup | 122 |
Field | 79 |
Filtered for a site
Sample Type | Sample Count | Site (showing site is optional) |
Dup | 14 | Chicago |
Field | 8 | Chicago |
Hope that makes sense
Ah. For that, you need two widgets:
The Table Widget:
The Category Selector Widget: