Automatically fill a column in one table from the data in another table

VF_PF · January 12, 2023, 2:58am

Hi !

I’m new to Baserow, and eventually, I’m trying to move from Google Sheet to Baserow.

I struggle with one particular workflow that works well on G Suite :

I capture lead data with a google form
Each time a new row is created in my “Form” sheet, it is automatically linked with an 8-digit code from another sheet “Code”
The code is sent by email to the prospect

In Baserow, I can’t automatically link a new row in the “Form” Table with the code in the “Coupon” Table.

How can I automatically fill a column in one table from the data in another table?

Thanks for your help!

Have a good day
Leo

frederikdc · January 12, 2023, 1:25pm

Hello Leo,

I think you will need to add a formula field to your “Form” table. In this formula field you can use the lookup function to link the record from one table with another table. Depending on your specific scenario, it is possible that you need to nest multiple functions in your formula.

For example: I want to calculate the average scores of reviews. I have a table with my Products and a table with my Reviews. The average must appear in the Products table:

avg(lookup(‘reviews’,‘Documentation quality’))

mpstaton · October 20, 2024, 1:54am

Hello @frederikdc I’m posting this here as it’s the same basic question, but with a more complicated twist, but my search led me here.

(Use case: VC and Private Equity)

Because of some end goals in reporting, I have a nuanced set of interrelated data.

4 Tables: Investments, Financings, Organizations, Classifiers

Investments are the investments we make, Financings are about any transaction for which we have good data.

The table('Investments') pulls the field('Into-Round') from table('Financings') via Linked field type. This is a “has one” relationship. Example: “Baserow::IPO”
The table('Financings') has the field('For-Organization'), Linked field type, and it pulls the primary-ID field from table('Organizations'). Also a “has one” relationship. Example: “Baserow”
The table('Organizations') has the field('Has-Classifiers'), Linked field type, which pulls in the primary-ID field from table('Classifiers'). This is a “has many” relationship. Example: “Database App”, “Small Data”, “API-First”, “Default Open-Source”. The table(‘Organizations’) should be the source of truth for the ‘Has-Classifiers’ property.

NOW here’s what I want to do:

In the table('Investments') I want to pull in the field('Has-Classifiers') from the table('Organizations') without needing to duplicate the Organization field in the Investments table, nor do I want a separate Linked field to the Classifiers table. Otherwise it creates an extra data entry burden, and also can create different Has-Classifiers values across different tables.

So, this is either some kind of indirect 3-way lookup, or it could also be achieved with some kind of match function I’m not aware of.

So the query logic would follow something like:
On table('Investments') the field('Into-Financing') linked to table('Financings'), fetch field('For-Organization'), go to the Organization row, and fetch the field('Has-Classifiers').

Example: Our Investment into the Baserow::IPO Financing buys shares in the Organization called Baserow, which has Classifiers of “Database App”, “Small Data”, “API-First”, “Default Open-Source”

And might be a function in Baserow like:
lookup(lookup('Into-Financing', 'For-Organization'), 'Has-Classifiers')

In some kind of ORM it might read:
this.Has-Classifiers = this.Into-Financing.For-Organization.Has-Classifiers

Sorry to be obtuse and also to make such complications.

frederikdc · October 21, 2024, 6:59am

Hi,

I tried to simulate your database structure.
If you want to see the classifiers in the Investments table you need to add additional lookup fields:

The table Financings has a link to the Organizations table. Add a lookup field (Organization - Classifiers) to that table.
The table Investments has a link to the Financings table. Add a lookup field to the lookup field Organization - Classifiers

This will show you the classifiers that are tied to the organization that is related to the financing. And it doesn’t require any additional input or management since lookup field are automatically updated when the data changes.