Understanding Datatables

A quick overview of Data Tables

* Did you know? A Data Table can be used by more than one project, allowing you to create different documents based on the same data. Each project keeps a record of the rows it has generated so that it doesn't process a row more than once (unless you generate a row again manually).

Data Table Listing

1. Click Data in the left menu to see all the data tables you have access to.
2. Each data table will be listed on the page, showing its name, description and other values
3. Click New Data Table to create a new data table
4. The Used in Projects column shows which projects are currently using the Data Table
5. The Related Tables column shows if any other data tables are related to this one.
6. Click on the action icon to Edit, Rename, Upload data, Download data, Delete all rows or delete the data table

Creating a Data Table

There are two places where new Data tables can be created: from the Project Listing page when creating a new project or from the Data page, which we will cover in this section.

1. Click on the New Data Table button to open the data table creation form. On this form you have 2 options to create a new data table - using a CSV upload or from scratch. We'll cover both in their own section below.
   
   

Importing a CSV File

1. Choose Import a .CSV File to upload a CSV from your computer. This creates a new data table using the CSV heading row for column names and subsequent rows as data.

1. Enter a Name 
2. Choose a color to differentiate the data table
3. Enter a Description for the data table
4. Upload a CSV file from your computer

Starting From Scratch

1. Choose Start From Scratch to create a new, clean Data Table

1. Enter a Name 
2. Choose a color to differentiate the data table
3. Enter a Description for the data table

Editing a Data Table

Adding a new column

If you created a new Data Table from a CSV file, you should already have some columns. If you created it from scratch, your data table will be empty of columns with a message displaying asking you to add some columns.

1. Click on the action icon to open the menu
2. Click on the Add Column menu item

1. Enter a Name for the new column
2. Choose a Type for the new column

About Column Types

• You can use these columns in the To, CC and BCC fields of email notifications.

• Allows you to place conditional sections in documents based on two numbers. For example, you could show a section of a document if one number is greater than another.

• Allows the use of conditional sections in Document Templates based on two dates. For example, you could show a section of a document if one date is less than another date. 
• Allows the use of the Date Format function in Document Templates.
• For the best compatibility, Dates should be formatted using the ISO date standard (e.g. YYYY-MM-DDTHH:MM:SS+timezone offset  or for a proper date: 2020-04-19T00:01:25+00:00)

• Allows you to place conditional sections in Document Templates based on the columns value. For example, you could show a section of a document if the column equals "True"

Automatic Conversion
When uploading a CSV file to create a Data Table, MergeOS will attempt to automatically set the correct Column Type based on the data in that column.

Data Table Relationships

Two Data Tables can be linked together by adding a Relationship. You relate two tables by choosing a column in each that joins them together, for example, an email address or a unique ID. Doing this essentially says to the system that all the rows in the related table belong to the row in the main table.

The example below is using an Excel workbook with two sheets to illustrate the concept. The sheet on the left has a list of clients, each with a unique ID, the one on the right contains a list of investments that each client has.

Setting this relationship up in MergeOS allows you to access the related table in a project which is using the main data table.

Adding a Relationship to a Data Table

1. Click the action menu button from within the main data table. This is the data table you want to generate documents from.
2. Click on the Add Relationship menu item to open the Add Relationship dialog

1. Enter a Name for the relationship. This is used in other parts of the interface when you need to choose the relationship
2. Choose the Local Column. This should be a column that contains a unique value that identifies the row, such as an ID or Email Address or other unique value.
3. Select the Related Table. This is the data table that has the records you want to relate to the current data table.
4. Choose the Related Table Column. This is the column that relates to the Local Column. It should have the same data as the local column (note: it doesn't need the same name, just the same data), i.e. an ID or Email Address
5. Choose the Relationship Type. For now, only choose One row in this table links to multiple rows in the related table. This is called a One-to-Many relationship.