How to link rows to existing tables automatically

Imagine you’ve got a spreadsheet full of important info, like company contacts. Each one has a unique ID. You want to move all this into Baserow and link new rows you add with data that’s already in another table.

Baserow is like a supercharged database that feels like a spreadsheet. It’s way stronger than regular spreadsheets and can deal with lots more data. If you haven’t yet, check out our 60+ templates. They’ll help you set up databases fast for your website, CRM, apps, or CMS.

What we’ll do

In this tutorial, we’ll walk you through the process of automatically linking data based on the unique IDs of rows. We’ll cover the following steps:

• Set up your Baserow database
• Populate data
• Create a database token
• Add a spreadsheet module to list rows
• Add Baserow module to list rows
• Add Baserow module to create new rows
• Test & schedule the Make scenario

Prerequisites

To complete this tutorial, you’ll need the following:

• A Baserow account with two tables.
  • Table A: Containing data to be linked to and the unique ID as a primary field, e.g list of countries.
  • Table B: Where you want to import external data and link rows to table A.
• Access to a Baserow workspace and table.
• Spreadsheet containing data with an ID column.
• Make, an automation tool that lets you connect different apps and services to automate workflows and repetitive operations.

Let’s dive in!

How to get Baserow to recognize and link data to an existing table

1. Set up your Baserow database

Log in to your existing Baserow account or create a new account if you don’t have one already.

Once you log in, you can access the dashboard to add workspaces and databases. You can work with an existing workspace, database, or table, or set up a new workspace from scratch.

In this tutorial, we will set up a new workspace and create a database from scratch.

1. To create a new workspace, simply click on the + Create workspace button. Then, click on Add workspace.

   If you’re just starting with Baserow, we recommend you read our support article on how to create a workspace and how to add collaborators to a workspace.

2. Now that the workspace is set up, you can either create a database from scratch or choose a template from our template library. Let’s add a database to the workspace.

3. Click on the + Create new button to start creating a new database.

4. Select the “Database” option to create a database from scratch.

5. Next, add a new table or click on a table within the existing database to open it. A default table will be added to the newly created database.

That’s it! we’ve successfully created a new database. Now we can populate, manage, and organize data in Baserow.

2. Populate data

The database will contain the tables we need. In this case, we will create two tables and fields configured:

• Company data: This is the primary table with the unique identifier that you want to link to. This table contains every company we work with and their associated unique ID along with some other general info about the company.
• Contacts data: This is the destination table where you want to import the data. Make sure the table structure matches the fields in your data source.

When you import the data from the spreadsheet, you can use the unique ID as the key. Baserow will recognize the unique IDs in the data in the spreadsheet to match the data in the Baserow table.

3. Create a database token

Within your Baserow user account, you can create multiple unique database tokens that allow Baserow to connect with other apps. You can create and find the database token under your account settings.

1. Go to your profile, then navigate to your Settings page
2. Click on the Database tokens tab
3. Click on the Create token + button
4. Write a descriptive name for the token, and select the existing workspace that you want to tie the token to
5. Click on the Create token button to create a new database token for the selected workspace and for the authorized user.

Database tokens provide similar functionality to login credentials, but they provide additional security and flexibility. A token, like your username and password, should be kept secure and handled with the utmost confidentiality.

Creating database tokens is available to all Baserow users; you don’t need to be on a paid plan.

Now let’s create a Make automation to populate your Baserow “Contacts” table with data from a spreadsheet, linking each customer to their corresponding company in the existing “Company Data” table using unique company IDs.

4. Add a spreadsheet module to list table rows

Next, specify the data source you want to import from. This might involve selecting a file, connecting to a database, or providing a URL. It could be a database, a spreadsheet, a CSV file, or from a web service.

Ensure that your data source is accessible and formatted correctly for importing. If necessary, clean up the data to remove any inconsistencies or errors.

In this case, let’s use an Excel spreadsheet. Once we have our list, we can import these as new rows on Baserow.

When the spreadsheet data is imported, we use the unique company ID as the primary key.

To get started, create a new Make account or log into your existing account. Then create a scenario and add modules to the scenario.

1. Select the app that will trigger your scenario. Click the first + button and select Microsoft 365 Excel as the trigger app.
2. Choose one of the actions from the list. In this case, select the List table rows action.
3. Create a connection or select a previously configured connection from the dropdown options.
4. Click OK to save this module and Run once to test the connection.

You should get a response back that resembles the following:

Now, let’s view the company data in our Baserow table.

5. Add the Baserow module to list rows

We want Baserow to recognize that the unique company ID in the spreadsheet data matches the data already built in the company list and link them to the contact.

1. Select the app that will trigger your scenario. Click the + button and select Baserow as the trigger app.
2. Choose one of the actions from the list. In this case, select the List rows action.
3. Create a connection or select a previously configured connection from the dropdown options. To create a new connection:
   1. Enter your Baserow API URL. Find your Baserow API URL in your API documentation. If you are using SaaS version baserow.io, the default URL is https://api.baserow.io, otherwise, replace the URL with your self-hosted URL.
   2. Enter your database token. Find your Database Token in Account → Settings → Database tokens as directed in step 2 above.
4. Enter the table ID of table with company data. Find the Table ID by clicking on the three dots next to the table. It’s the number between brackets.
5. Click OK to save this module and Run once to test the connection.

You should get a response that resembles the following:

Now, let’s have Baserow read and link the unique company IDs in the new row when we import contact data into the Baserow table.

6. Add the Baserow module to create new rows

Once the data source is connected, we’ll need to map the fields from your data source to the corresponding fields in the destination table. This ensures that the data is imported into the correct columns.

We’ll use the same database as in the first Baserow module. But, the table ID will be the one we want to store the contact details.

1. Click the + button and select Baserow as the app.

2. Select the Create a Row action. This creates new rows.

3. Select the previously configured connection from the dropdown options.

4. Enter the table ID where the row will be created in. Find the Table ID by clicking on the three dots next to the table. It’s the number between brackets.

5. Map the company ID from the Baserow module and map the contact details from the spreadsheet module.

   For the links to table fields, ensure that these links are properly represented in the import process. This will involve specifying the row ID as a reference from the Baserow List rows module.

6. Click OK to save this module and Run once to test the connection.

7. Test and schedule the Make scenario

Our scenario is set up and ready to activate, but we may still need to run a quick test. Run the scenario, Make will load the prompt and store the data in the Baserow database. After the import is complete, review the imported data to ensure that everything looks as expected.

Let’s jump back to Baserow. Check your Baserow table for the responses! The related row will also be populated in the linked table. Pay special attention to any links to table fields to ensure they are functioning correctly.

Specify when and how frequently an activated scenario runs. To do so, go to the scenario detail page and locate the Schedule tab or click on the clock icon to set a preferred schedule.

To set the schedule, adjust the Run scenario to set the time when it should run.

Save and activate the scenario by toggling on the scheduling switch at the bottom left corner.

Make offers advanced options like conditional logic and filters for more complex scenarios. You can add filters to the spreadsheet trigger and Baserow action to only process specific companies or customers. Explore the “Update Rows” action in Baserow if you need more granularity over field updates.

Explore Make’s other features like multi-step modules for advanced automation and define how Make handles errors, like when a company ID doesn’t match in company name. You can choose to skip the row, create a new company, or notify you.

Summary

This tutorial shows you how to match unique IDs in your spreadsheet with rows in a Baserow table using Make. You can tweak it for your own spreadsheets and tables. By following this step-by-step process, you’ll be able to link your data to Baserow automatically. This saves time and helps keep your data correct and consistent.

Other useful resources

The following articles may also be helpful:

• How to link Baserow rows in bulk automatically
• How to model your Baserow data
• Automate custom notifications from Baserow form submissions with Zapier Webhook

If you run into an issue while following this tutorial, feel free to reach out to ask for help in the Baserow community.