Sum of items in a query

App Building
, ,
1

Hi,

So, first of all, I'm using google sheets as a resource.
I have a sheet with people, let's name it People, then I have another sheet with the column name and the column action, lets name it Actions.
I have 2 tables in retool whith 2 queries to get all the info from both sheets to each table
I'd like to create a new column in the People table with the sum of the Actions.
Example:

Table Actions
Mark | Action1
Mark | Action1
Mark | Action2
John | Action2

So the table should be:
Table People
Name | Number of actions 1 | Number of actions 2
Mark | 2 | 1
John | 0 | 1

How can I make those calculations to the new columns?

thanks.

2

Hello, you can do the following

  1. Create a custom column
  2. In mapped value use the following script referencing your Actions table and the ID´s that correspond

(This for each existing Action)

{{
  table3.data.reduce((acc, val) => {
    if (val.action_id === 1 && val.user_id === currentSourceRow.id) { // if action_id is equal 1 (Action 1) and user_id is equal the user id of the current row, add 1
      return acc += 1
    }
    return acc
  }, 0)
}}

image
image1166×521 49.4 KB

2 Likes
3

Thanks! I'll check it!

4

Works fantastic, thanks again.

1 Like