Automatically join items in Designer

Owned by Former user (Deleted)
Last updated: Jun 07, 2023 by Denise McGettigan
3 min read

Designer 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 Designer joins the items, both the new ‘joined’ item and the individual items display in the data sources panel. You can then add more items or edit the data in the existing items as required. Designer will recognize your changes on Sync and rebuild the joined item.

Preparation for auto join

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

  1. Ensure the items are structured in the same way (columns/headings) and you know where they are located on your computer.

  2. Edit the name of the items: 

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

    • Each item name must start with the prefix Split_[JoinedFileName]_ where the [JoinedFileName] part differentiates this batch of items from other joined items that also have the Split prefix. You can use any name you want, 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 original item name goes after the second underscore, to distinguish the individual files from each other.

    • For a database item, only tables or views prepended with phocas_split_ will be joined.

  3. Sync the items to the same location.

Example - 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 original file names for the second part of the name. Therefore, the new file names are:

You then sync the files to a single (same) location in the usual manner. 

The three individual files display in Designer (exactly as you named them), along with another file, which is the joined file. 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.