This article is for Flatfile's Workspaces. If you'd like to check out the latest version of the Flatfile Data Exchange Platform, click here!

At its core, relational data is a new type of data field designation available when you set up a data template. For more information about setting up data templates, you can see our documentation on Template Configuration. Currently, the relational data feature is only available to Flatfile Workspaces customers.

Relational data allows you to link data uploaded with one data template to data uploaded from another data template. With this feature, a single data template can be linked to as many additional templates as you’d like—expanding on the core Flatfile functionality you’re used to already.

Creating these links is a simple two-step process. First, you set up the links between templates during data template creation. Next, during the data matching and review steps, you will be able to validate that the linked datasets do in fact link together in the way you were expecting. This helps ensure the data you eventually pull into your target system matches the requirements of that system.

Setting up the relational data feature begins in your data template. When setting this up in the UI, select the Link to template field type from the drop-down menu when you are adding a column. Next, you will choose which other data template this template will link to.

In order to link to a template, the template you want to link to must have at least one field marked as Unique. Data templates with no unique fields will not be available to be linked to; at least one unique field is required in order to establish a valid link between templates.

After linking to a template, choose a field from the linked template to act as a preview using the Preview Field drop-down menu. The Preview Field is a human readable representation of the linked data. For example, if you have a data template with both customer names and complicated customer IDs, the customer name may be a good preview field to choose so that users don’t have to interact with the complex customer IDs and can rely on what they know most: the human readable aspects of their data.

If you are deploying your sheets (templates) from the Flatfile Platform SDK, you must have a field that uses the LinkedField type, and on that field you must set the sheet you are wanting to link to. On the sheet you are linking to, you will then need to set a Preview Field. For information on how to do this, please visit our developer documentation.

Take note that you are not selecting a field to link to, you are linking one field in the data template that you are setting up to an entire, separate data template. This means that you do not need to set up multiple fields linked to the same data template. One link will give access to all fields from the template you are linking to. For example, if you create a linked field in your Customer Template to your Company Template, and the Company Template has 10 fields in it, all 10 fields will be available during matching for the user to upload their data against.

Relational data templates can be used to set up one-to-one relationships, one-to-many relationships, or many-to-many relationships. Setting up these different types of data templates starts with the checkboxes under the Additional settings header.

To enforce a one-to-one relationship between the linked data and the data you are linking to, select both the Set field as unique and the Field is required checkboxes. For example, this would ensure that each customer is only linked to one company in the review scene, creating a one-to-one relationship in your data.

This is the default out-of-the-box behavior with how relational data works in Flatfile today. By not selecting the Set field as unique checkbox for the linked field, you will be able to have each linked record link multiple times to the same value. This means that, for example, customers can be linked to the same company multiple times.

Setting up a Workspace to use the relational data feature is nearly identical to setting up a Workspace normally. Give the Workspace a name, select (or create) an organization, and then select the data templates that the Workspace will use. The only difference is that, when you add a linked data template to a Workspace, all data templates that it are linked will also be added to that Workspace.

After setting up your linked data templates and your Workspace, all that’s left to do is upload and review data.

When you upload data using a data template with relational data enabled, you will see a few differences in the matching step. Just like normal, you will see the fields that need to be matched and Flatfile’s suggested matches. However, you also have the option to match fields using any of the fields in your linked data template. You can see these options by opening the matching drop-down menu and then clicking the expandable list of available fields in the linked data marked by an arrow.

During the matching step, you will only be able to match the incoming data to "unique" fields in the linked template. This is to ensure that there is a direct, one-to-one link between the value being imported and the related dataset.

Once these fields are matched, if you delete a linked value in a field, you will see the original value (instead of your selected preview) and the value will be unlinked. A warning will pop up when you try to do this to prevent accidentally unlinking fields. If you dismiss a parent record, that record will also be unlinked, though you should see a warning before you dismiss a parent record that is used in your data.

Flatfile has the ability to upsert data from one upload into a different sheet, no matter which set of data was uploaded or matched first. In short, data that is linked to can be inserted into data sets that are referencing it, meaning that you do not have to worry about the order that you upload or match your data when using the relational feature. By default, Flatfile will upsert data from your child sheet to the parent sheet. This can be disabled with a flag on your linked field, described here.

Relational data may be used as an alternative to Flatfile’s category fields in some cases. If you have more than 100 categories, using Flatfile’s regular category field option is not recommended. In these cases, the matching scene can become cumbersome for users to navigate in order to match each value manually.

Instead, you could use relational data and create a linked field to a template that only contains your category options. By using relational data, there would be no limit on the number categories you can use. For example, you can create a linked field to a template that has all 180+ countries in the world for a field that requires users to pick a country as a category.