LookUp with conditions

Feature Ideas
1

This would make it possible to retrieve values from another table with conditions.
Here an example:
Conditional relational fields

2 Likes
2

Hey-hey, this feature request already got some community votes, nice!

We’ll discuss the suggestion on the team call, thanks for your activity @moonday :blue_heart:
As soon as I have some updates on your requests, I’ll post them here.

1 Like
3

@moonday For now you can use the filter formula function to achieve the same effect. For example you could create a formula field with the formula:

filter(
  lookup('Assets in Room', 'Price'), 
  or(
      totext(lookup('Assets in Room', 'Asset Type')) = "Chair", 
      totext(lookup('Assets in Room', 'Asset Type')) = "Desk")
)

Its more complex unfortunately than the filter builder method suggested which we do want to add, but hopefully helps for now. I also invited you to a Baserow workspace I made showing the above formula in action.

One advantage of the formula way is you can then aggregate the results like so:

sum(filter(
  lookup('Assets in Room', 'Price'), 
  or(
      totext(lookup('Assets in Room', 'Asset Type')) = "Chair", 
      totext(lookup('Assets in Room', 'Asset Type')) = "Desk")
))
3 Likes
4

Hi @nigel , thank you very much for sharing this.
& yes, this looks a little “big” & not that ux-friendly - but anyway, for now it works.

5

Hey @moonday! Some updates on your request: we do plan to work on this feature, but it’s not a priority one. For now, you need to use formulas to set up conditions in the lookup as Nigel suggested :slightly_smiling_face:

6

Hi @olgatrykush sure,
As there is a workaround totally understandable.
Maybe you should think of a way to make this solution option more prominent - as it will eventually drop from the easy accessible list of things here in the community. Maybe a dedicated Formula page + this as an addon - info?

7

Hi @nigel! I’m also interested in your formulas :slight_smile: could you invite me to your workspace so that I can have a look to your exemple? Thanks :slight_smile:

8

@nigel could you please invite me as well? I would like to find out, how is the formula workaround made and how it works. I would need some extra filtering functionality in my workspace/database, but not able to do it right now (as baserow does not support it right now) - see my request here:
Lookup data field - limitation if filtering is needed
Thanks!

9

For future readers of this thread i’ve just made two public views showing two connected tables with a column showing a filter formula:

https://baserow.io/public/grid/NPPPolxNb9GAzc_6fxfRsRxsVMfyUIMOiUgkP5_VlpU
https://baserow.io/public/grid/_Slf9075yocwkabHDcJn7HF1QO3rKxrXy5mZc-fIm-0

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
11

One final thought here. Above i’ve discussed a way of filtering down a lookup/link row field “list of values” into a smaller list per cell.

If you instead want to filter rows entirely, like with the normal view filters, but based on some condition to do with a lookup then I suggest writing a formula that calculates your condition as a true/false value from a link row field/lookup reference, and then filtering on the resulting true/false formula field.

For example, say given the above examples, i wanted to only show employees who had 5 or more overdue tasks in my view, i could easily do this by:

  1. Add a formula like count(filter(field('Tasks'), lookup('Tasks', 'Overdue'))) > 5
  2. Now the formula is a true/false column, which you can filter to only show rows with true or false values.