LookUp with conditions

Feature Ideas
10

The way I think about the filter function is that:

  1. You are starting with a list of linked rows (a link to table field) and you want to filter down that list per row
  2. So you make a new formula using the filter function
  3. The first argument to the filter function should be a reference to the link row field you want to filter down
  4. The second argument to the filter function is some formula expression that is run per item in the list, if it’s true then the item is kept, if it’s false the item is thrown away.

So in the examples above i have a link row field, linking per task the employees who are assigned to that task.

In the employees table, each employee has a list of their assigned tasks.

I wanted to see, a list of only their tasks which are overdue per employee. So this is a great situation for filter.

So first of all, i’m filtering down the list of tasks per employee row, so my first argument to filter should be the field reference to that list:

filter(field('Tasks'),...)

Ok and now my second argument is going to be some expression that returns true for the tasks in that list i want to keep, and false for tasks i want to throw away. So given I already have a boolean column per task which indicates if it is overdue or not, i can just lookup that column in the formula and use that:

filter(field('Tasks'), lookup('Tasks', 'Overdue'))
1 Like