Regarding Advanced Data Table Filtering and Calculation

There are two data types, “item” and “score,” which are linked together. The “item” data type has fields for “score” and “category,” while the “score” data type has fields for “item” and “point.” I would like to know how to calculate the points within the “score” data that correspond to “items” with specific categories.
スクリーンショット 2023-08-13 210238
スクリーンショット 2023-08-13 210253

For example, the “item” and “score” data tables are as follows:
In this case, what conditions should be set to calculate the sum of scores for “items” with the category “X”?
In the example provided, for the attached image:
Category a: red 7 + blue 9 + red 3 = 19
Category b: green 5 + green 8 = 13
Category c: white 4 + white 1 + yellow 5 = 10
Category d: black 2 + black 4 = 6
I would like to achieve such results. Is this possible?

Hi there, @qh7_abyss… you would need to use an advanced filter to do what you described, and I made an example where a category (option set) is selected from a dropdown, and the sum of the points of the scores where the associated score’s item has the selected category is displayed in a text element. The expression for the text element looks like this…

Note that advanced filters take place on the client (browser) side, so the search (which has no constraints) is going to return all of the scores in your database to the client, and then the filter will be applied. Just something to keep in mind from both a performance and workload unit perspective.

Hope this helps.


Thank you, Mike. This is the answer I was looking for. However, it’s not yet perfect. The task I should be able to accomplish with the application of these concepts is still incomplete.

The “item” data contains fields for “year” and “season.” I want to narrow down the item data to include only those with any given year and season. Then, I want to calculate the sum of scores for each of these matching items and output them to RG in descending order of points. Do you understand?

I designed this based on my understanding, but it doesn’t seem to be working correctly:
① Search for scores: filtered (Advanced: This score’s item contains list Search for item(year, season))
With this condition, I should be able to narrow down the score data to items with any given year and season. This will likely involve the same item appearing multiple times (for example, blue7, blue3, blue8, etc.). Therefore, I assume I can’t specify this condition as a data source for RG, right?

Next, I need to calculate the total points for each of these items. Using the example image from the previous time, the total points for each item would be:
white 5
red 10
blue 9
green 13
yellow 5
black 6
So, I need to specify an expression in RG’s data source to restructure this data, correct?

This seems like quite a complex problem. Could you please assist me again?