Automatically merge or split items during Sync (split view)

Learn how to merge multiple files into one, and split one file into multiple files.

User permissions: Administration > Databases and Sync

Some people use the term join instead of merge. However, join has a specific meaning when working with databases, so merge is better term to use in this context.

You can use splits to break up large sets of data so that they are synced in smaller chunks. Phocas automatically merges the data after it's synced to the server. The new merged item displays in the data sources panel in Designer, so you can use it in your database designs. Sync will recognize your changes on sync, and rebuild the merged item.

There are lots of use cases for splitting a sync file. For example, if you have a particularly large transaction set, you could break the queries up into current year, previous year, and so on. Also, as Sync checks for any changes in the data before sending, if older data is only changed infrequently, splits can be used to separate data altered more frequently and if well ordered, Sync will see older data hasn’t been altered and therefore, not sync it.

The benefits of the split-and-merge feature include:

• It reduces the resources on the server used by Sync and can speed up the sync process.

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

• It keeps designs simple.

• It helps with incremental data.

Preparation for auto merge

To split items and get Sync to automatically merge them 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_[MergedFileName]_[UniqueIdentifier]

   • You can use name you want for the [MergedFileName] element, as long as it is consistent across all the items you want to merge. This part becomes the name of the new merged item (view) that's created.

   • 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: Merge three transaction files

Suppose you have three transaction files that you want to merge 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_[MergedFileName]_[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 new merged 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 merged file in your database design.

Example 2: Upload and split an Excel file

By default, when you upload an Excel file with multiple worksheet tabs, those tabs are split into separate files. If that's not the desired outcome, you can use the above methodology to merge the data in the worksheet tabs into one file.

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 resulting merged file in Designer. In this example, you end up with one file called Sales.