In today’s data-driven world, businesses require efficient tools to manage and organize their information. Linking rows across tables in a database is a fundamental concept that establishes relationships and enables efficient data retrieval. However, there are scenarios where you may need to link a single row to multiple rows simultaneously.
Baserow is a scalable, secure, and customizable platform that allows users to develop powerful database applications. Baserow combines the functionality of a database with the flexibility of a spreadsheet, making it an ideal choice for businesses, teams, and individuals. Users can store, organize, and collaborate on data in various ways.
One of Baserow’s most powerful features is its ability to link multiple tables together using the link to table field type. With this field type, users can create more complex databases and relationships between data, enabling them to analyze and manipulate data.
Let’s get into how to link multiple Baserow tables with automation.
What we’ll do
This tutorial will guide you through the steps of batch linking across tables in a database. We will cover how to link tables within a Baserow database and how to use automation to streamline your workflows and save time.
We’ll follow these steps:
- Set up a Baserow database
- Import CSV data into Baserow
- Define the linking key in Baserow
- Create a link to table field
- Add credentials on n8n to authenticate nodes
- Add the Baserow node to get rows
- Update corresponding rows with the Baserow node
- Execute and activate the workflow
What we’ll need
We will integrate no-code tools. These are the tools we need:
- A free Baserow account
- Familiarity with Baserow basics (creating databases, tables, adding rows, etc.)
- n8n, a workflow automation platform, to connect Baserow with other apps by setting a trigger and action
How to link multiple tables automatically
1. Set up a Baserow database
First, log into Baserow. Storing data in a Baserow database makes it easier for everyone on the team to access and work with the data.
Create a workspace by clicking the + Create workspace button in the dashboard. Name the workspace, then click the Add workspace button to save the newly-created workspace.
After you’ve created a workspace, create a new database from scratch or add a database from the template library. For this tutorial, we will create a database from scratch.
A default table will be added to the newly-created database when you start a database from scratch.
2. Import CSV data into Baserow
For this tutorial, we will use sample CSV files that are pre-populated with data. If you already have your data in a Baserow database, you can skip this step and proceed to Step 3.
We will work with two datasets: people and organizations. To use these datasets, download the sample CSV files:
- people-100.csv - People CSV with 100 rows.
- organizations-20.csv - Organizations CSV with 20 rows.
The People table contains a list of the organizations to which each person pertains. The CSV headers are on the first line. We will work with the following fields:
| People table | Organizations table |
|---|---|
| Full Name* | Organization Name (Primary Field) |
| First Name | Website |
| Last Name | Country |
| Description | |
| Job Title | Founded |
| Organization Name | Industry |
The Full Name field is a read-only primary field defined by formula:
concat(field('First Name'), " ", field('Last Name'))
Next, we need to import the CSV file into the database by uploading the file with tabular data.
When data is imported into a Baserow database, it can be easily queried and formatted. Use the Baserow import features to import the CSV file by:
We’ll import both tables (People and Organizations) into a single database for the purposes of this tutorial.
To import the CSV files containing our sample data as new tables:
- Click + Create table within the database.
- Select Import a CSV file, then input a name for the table.
- Click the Choose CSV file button to browse and upload a file from your computer.
- Finally, click Add table to import.
The data will be immediately visible as new tables in the database. Rows will be assigned an incremental row ID value that starts at 1 for the first data row.
3. Define the linking key in Baserow
When you link a row to another table, the title card representing the linked row displays the value of the primary field. It is important to generate a unique identifier for each row using a formula field or a unique primary field. The identifier will serve as the linking key.
We will use the common value in the tables - Organization Name - to link both tables. In the Organizations table, the Organization Name field should be the primary field. In the People table, the Full Name field should be the primary field. An ID in both tables with a unique value can also be used to link the tables.
By setting up the linking key in Baserow and defining it in n8n, the unique identifier serves as a reference point, allowing n8n to establish the correct connections between multiple records in bulk.
4. Create a link to table field
To keep our related data organized and connected, we need to connect one row in the People table to another row in the Organizations table.
Baserow link to table field type is useful for creating relationships between tables and organizing your data. The link to table field accepts an array containing the identifiers or primary field text values of the related rows from the linked table.
In the People table, add a link to table field and create a related field in the linked table. This would allow us to link which people work in which organization, and which organization each person is working in.
The value of the link to table field would be based on the row ID linked to the Organizations table. The linked values in the People table will contain IDs that correspond to the primary field in the Organizations table.
Next, we’ll create automation that syncs both tables.
5. Add credentials on n8n to authenticate nodes
n8n allows the building of custom workflows that suit your specific needs. You can connect multiple apps, set up conditional logic, and use various data processing functions to create workflows that automate your business processes.
Log in to n8n. There are different ways to set up n8n depending on how you intend to use it.
Before we integrate Baserow with n8n, we need to create credentials. n8n credentials are private pieces of information issued by apps and services to authenticate you as a user and allow you to connect and share information between apps and the n8n node.
To authenticate the Baserow node, add your Baserow username and password credentials to n8n. Learn how to create the credentials here.
Now, let’s use the added credentials to authenticate the Baserow node in n8n.
6. Add the Baserow node to get rows
Before we add our regular nodes, create a new workflow and add the first node to trigger when this workflow will run.
Next, search and add the Baserow node. Configure the Baserow node parameters to list rows in the People table. To list rows in the People table, a GET request has to be made to the People endpoint:
- Select the Credential for Baserow API configured in Step 5 above.
- Select “Row” as the Resource.
- Select “Get Many” as the Operation to receive many rows. Learn about Baserow operations.
- Select “Link Table Database” from the list as the Database to operate on, or specify an ID using an n8n expression.
- Select “People” from the list as the Table to operate on, or specify an ID using an n8n expression.
- Toggle on “Return All” to return all results, or set a given limit.
- Click the Execute node button to get a response.
The output will display the rows in the People table.
The response is paginated and by default, the first page is returned. The correct page can be fetched by providing the
pageandsizequery parameters in the API database documentation.
Next, we want to link the rows with the row ID or primary field. Each row ID uniquely identifies that row of data. The assigned number is permanently attached to a row and cannot be changed or deleted, even after the row is deleted.
7. Update corresponding rows with the Baserow node
n8n nodes take each item as a single data point, process these items, and output the results. To link the row data, we want to cross-reference the ID to connect a user to an organization.
Add another Baserow node to the workflow then configure the parameters to update the Organization field in the People table:
-
Select the Credential for Baserow API configured in Step 5 above.
-
Select “Row” as the Resource.
-
Select “Update” as the Operation.
-
Select “Link Table Database” from the list as the Database to operate on, or specify an ID using an n8n expression.
-
Select “People” from the list as the Table to operate on, or specify an ID using an n8n expression.
-
Specify the row ID using an n8n expression from the previous Baserow node. In this case:
{{ $node["Baserow - People table"].json["id"] }}. You will see the rendered result within the expression result pane. -
Data to Send: “Define Below for Each Column”.
-
Add the fields in the People table that should be linked to the organization. Select the Field Name from the dropdown list as “Organization” or specify an ID using an expression, then specify the Field Value using an expression.
The array set as the Field Value:
{{ [ $node["Baserow - People table"].json["Organization Name"] ] }}.We want to send the value only when the Organization Name field in the People table matches an organization. You will see the rendered result within the expression result pane.
-
Click the Execute node button to get a response.
A row with the matching value for its main/primary field will be searched in case of a text value, instead of an identifier. This will link the connected rows in the People table to a company in the Organizations table.
The output will display the rows in the table and the updated row will sync with the same row ID.
All identifiers must be provided every time the relations are updated. If an empty array is provided all relations will be deleted.
8. Execute & activate workflow
To test the workflow, trigger the workflow manually by going to the canvas and clicking Execute Workflow. You get a response after executing the workflow.
If we check the People table in Baserow, each person will now be assigned and linked to an organization. If more than one match is found, the first one in the order of the table is selected. This data will be updated in the Organizations table as well.
After the test, the next step is to save and activate the workflow. This workflow will run as defined once you activate it. Save the workflow and then click on the Active toggle at the top of the canvas to activate the workflow. Activating will ensure the workflow will fire automatically each time the trigger condition is met.
Summary
The link to table field can be used to create many-to-many relationships between tables. For instance, you might have a table of projects and a table of team members. You could use a link to table field in each table to link tasks to team members and team members to projects. This would allow you to see which team members are assigned to which tasks, and which projects each team member is working on.
By mastering this technique, you’ll be equipped to handle situations where you need to establish multiple connections between rows efficiently, saving you time and effort in your database management tasks.
Other useful resources
- How to model your Baserow data
- How to generate unique IDs with a Baserow formula
- Automate employee onboarding for remote and teams with Zapier
- Automate custom notifications from Baserow form submissions with Zapier Webhook
If you run into any issues while following this tutorial, feel free to reach out and ask for help in the Baserow community.
What’s your use case? Tell us how you use Baserow
