Count total records with condition

Baserow Help
1

Hello there.

  1. I need a quite simple calculation.
    Let’s say I have 2 related tables: “Users” and “Activity”.
    Each user have several linked entries in Actvity. How can I count number of records depending on content of one column (status) with type number or select. Lets say if status have value 1 we’ll count this record.
  2. Maybe it is possible to count record, according to a certain filtered view of related table?
2

Hi, You will need a formula that combines a lookup(), filter() and count() function

count(filter(lookup('Users','Status'),lookup('Users','Status') = 1))

The best way to understand the formula is to split it up:

  • lookup(‘Users’,‘Status’) → gets a collection of the status of all the users linked to the activity
  • filter(lookup(‘Users’,‘Status’),lookup(‘Users’,‘Status’) = 1) → only show the ones where status equals 1
  • count(filter(lookup(‘Users’,‘Status’),lookup(‘Users’,‘Status’) = 1)) → count the results instead of showing them
1 Like
3

@frederikdc
Thanks for help. Now, its much more clear how filter works.
Most of my cases are solved. But I need additional help with filters, if possible.

The case:
Lets say, I have four tables: “Goals” “Projects”, “Users”, “Tasks”. Proejcts have link with Tasks. Goals have link with Projects.
For each task I have a column “cost” with type number and Responsible column (link to User). Then in projects:

  1. I created rollup column (max cost) for getting the max value task from Tasks table.
  2. Via filter formula, I got responsible from task with highest cost value. It works as expected.

Now I want to get the ‘max cost’ value and the responsible from all projects linked to certain Goal.
So, with max cost I get no problem. Usual rollup with default max function solve problem. But for responsible I can’t filter columns. It shows me all values from the linked projects. I used a formula like that:
filter(lookup(‘projects’,‘responsible’), lookup(‘projects’,‘max’) = field(‘Record’))

4

Hi,

The problem with the formula is that the field Responsible from the Projects table is a calculated field based on a formula that returns an array (with only one item, but still an array).

You can solve this by changing the formula to get the responsible field in the projects table so that it only returns a single record. You can use the function min() or max(). Both will return the same User since there is only one, but they will return it as a single field instead of an array.

min(filter(lookup('Tasks','Responsible'),lookup('Tasks','Cost') = field('max cost')))

In the goal table, you can get the user with the following formula:

filter(lookup('Projects','Responsible'),lookup('Projects','max cost') = field('highest cost'))
1 Like