Automatically join items during Sync (split view)

User permissions: Administration > Databases and Sync

Sync can automatically join together similar but separate items (files, SQL views) after they have been synced. The benefits of this feature include:

• It saves design time, as it’s quicker to map a single item than it is to map several items.

• It keeps designs simple.

• It helps with incremental data.

After Sync joins the items, both the new joined item and the individual items display in the data sources panel in Designer. You can then add more items or edit the data in the existing items as required. Sync will recognize your changes on sync and rebuild the joined item.

Preparation for auto join

There are three steps to take before Sync can join the items for you.

1. Ensure the items are structured in the same way (columns/headings).

2. Edit the name of the items:

   • The items must have the following naming convention: Split_[JoinedFileName]_[UniqueIdentifier]

   • You can use name you want for the [JoinedFileName] element, as long as it is consistent across all the items you want to join. This part becomes the name of the new, joined item.

   • The [UniqueIdentifier] goes after the second underscore to distinguish the individual files from each other.

3. Sync the items to the same source.

Example 1: Join three transaction files

Suppose you have three transaction files that you want to join together. Each file contains the same columns/headings but has different data. The files are:

• 2015ALL (contains sales data from all of 2015)

• 2016Q1 (contains sales data from the first quarter of 2016)

• 2016Q2 (contains sales data from the second quarter of 2016)

You rename the files using the required naming convention Split_[JoinedFileName]_[OriginaFileName]. As the files contain sales data, a logical prefix is Split_Sales_. You keep the unique identifier, in this case the original file names, for the second part of the name. Therefore, the new file names are:

You then sync the files in the usual way.

The three individual files display in Designer (exactly as you named them), along with another file, which is the joined file. In this case, it is named Sales, which is the name you put between the two underscores.

You can now proceed to use the joined file in your database design.

Example 2: Upload and split an Excel file

Using the above methodology, it's also possible to split one file into multiple individual files. Suppose you have a Microsoft Excel File of sales data and the data is split into separate worksheet tabs, one for each month (Jan 2024, Feb 2024, and Mar 2024).

1. In Excel, rename the Sales file using the required naming convention Split_. In other words, add the split_ prefix to the file name. In this example, the file name is now: split_Sales. The name of each tab (Jan 2024, Feb 2024) will automatically be added as the last part of the split filename, such as split_Sales_Jan 2024.

2. Upload the file.

3. View the uploaded files in Designer. In this example, you have end up with four files, one for the Sales file and three automatically created split views of that file: split_Sales_Jan2024 and split_Sales_Feb 2024, and split_Sales_Feb 2024.