More in-depth Lookup capabilities

It would be a bit fragile, but a major function I’m currently missing is the ability to “dig” through multiple relations to fill data.

e.g.

  • Table A has a Link to Table B

  • Table A uses Lookup to fill a Link from Table B to Table C

  • Table A then uses the filled Link to do another Lookup and pull a Text field from Table C

  • Students have a Link to Classes

  • Students uses Lookup to fill the Link from Classes to Teachers

  • Students then use the Teacher link to do another Lookup and pull the Department name from Teachers

Currently, to do this you would have to manually fill links to both Classes and Teachers rather than only manually doing the one link.

Hey Peeves,

Trying to understand exactly your requirements just to make sure we are on the same page. I’ve setup the following database structure in Baserow as an example:

A students table which manually links to classes and also has a lookup of a department lookup field in the classes table:

A Classes table which manually links to Teachers and has a lookup through Teachers to department:

A Teachers Table which has a manual link to Departments:

And Finally a Departments Table:

And heres a Diagram showing the links between the tables in the above structure, is this what you were describing?
image

Students uses Lookup to fill the Link from Classes to Teachers

I’m not exactly sure what you mean when you say use a Lookup to fill a Link. Especially a Link between two other tables. Could you explain how it would look given the above table structure?

Are you perhaps asking for something like VLOOKUP in Excel / google sheets? So instead of me making a manual link between teachers and departments, instead the classes table could say use a VLOOKUP field which links a class row to any department row with the same name? Or am going off on a wild tangent here :D?

I guess fundamentally I don’t understand how could the database know which teacher teaches which class if there was only one manual link between student and class? So I can create a link saying Student A takes Maths class, but if I don’t also create a link from Class to Teacher manually how could the database know that on its own?

Database Example

TEACHERS

CLASSES

STUDENTS
image

CLASSES TAKEN

Here’s some images of what I mean exactly - It’s more like doing multiple JOIN operations than anything in google sheets

Ah ok I understand now, thanks for explaining! You want to lookup through multiple tables in one go.

The good news is I literally made the change adding this feature to the formula lookup function a few days ago. It will hopefully make it into one of the next releases.

With this change you would make a formula field and enter the formula lookup('class', 'teacher', 'name') to go through two link fields to the teacher name field. You can go through as many links as you want using this updated lookup function.

Would it just being available via a formula function be ok for your use case?

1 Like

The way I want to use it doesn’t involve the link fields having more than one link per field, so that’s exactly what I was looking for!

Formula functions are perfectly fine - thanks for your hard work!