Help with a formula: conditional lookup (lookup only for values in another field)

Technical Help
1

Hi,

My database has the following structure:

Contacts

  • Name (primary field, single line text)
  • Organisation (Link to Table: Organisations)
  • Projects (Link to table: Projects)

Organisations

  • Organisation Name English (primary field, single line text)
  • Coordinating Partner (Link to table: Projects)

Projects

  • Project Acronym (primary field, single line text)
  • Coordinating Partner Organisation (Link to table: Organisations)

This is what I would like to achieve:
Create a new field in Contacts that looks up Projects / Project Acronym, but only for projects linked in Contacts / Projects.

The formula needs to be something like that:
If Contacts / Projects is not empty, look up Projects / Coordinating Partner Organisation. If Projects / Coordinating Partner Organisation equals Contacts / Organisation, list Projects / Project Acronym.

Is this something that can be done with a Baserow formula?

2

Hi Mary-Lou,

I think if the Projects field in the Contacts table is empty, then any lookup will return blank.
Therefore that condition is built in, you don’t have to recreate it.

So for the rest, we can do a formula field:

If there is only ever 1 linked project:
if( lookup(‘Projects’,‘Coordinating Partner Organisation’) = field(‘Organisation’) , lookup(‘Projects’,‘Project Acronym’) )

If there could be multiple linked projects:
filter( lookup(‘Projects’,‘Project Acronym’) , lookup(‘Projects’,‘Coordinating Partner Organisation’) = field(‘Organisation’) )

I haven’t tested these, so they might need tweaking.

1 Like
3

@Justin
Thank you so much! Works like a charm! Brilliant.

4

Awesome :slight_smile: And you’re welcome.

5

Coming from Excel, I find Baserow formulas rather unintuitive, unfortunately. I suppose this is because I only need them once every few months.

I am very very grateful for the help provided in this forum.

6

Yeah, I get that.
My journey has been Excel to ClickUp to Baserow. So actually compared to non-spreadsheet implementations of formulas, I think Baserow’s is pretty great.

And combined with the relational function, it can do lookups more reliably than a spreadsheet.

1 Like