Phocas has a new user documentation site. This site will be retired soon.

Sync

Owned by Helen Gosper
Last updated: Aug 27, 2024 by Denise McGettigan
9 min read

This page is for the Phocas Sync application, which is different from the Sync Sources page in Phocas.

This page describes the Phocas Sync and its basic setup process. It includes links to other pages that provide more information on the various sync options.

Overview of Sync

Phocas Sync, also called Sync Client or simply Sync, is one way to get your data into Phocas. Sync is a Windows-based piece of software that you install on your computer. It connects to various types of your raw data items and sends them to the Phocas cloud over the internet. The data items become available to your administrator in Designer, as sources for database design.

As the name suggests, Sync synchronizes updates to the synced data at scheduled times, such as overnight or weekly. This regular synchronization is important for most Phocas users, as they want to view the latest data whenever they open a database.

See the Additional technical information below for more about how Sync works. See also the technical limitations page.

Sync setup process

The following diagram summarizes the Sync setup process (purple boxes) and shows the output (green box). The same steps are required when you need to reinstall new versions of Sync.

Step 1: Download the Sync Installer

  1. (Applicable if you are reinstalling Sync) Ensure you don’t have Sync open or currently running.

  2. Go to the Phocas downloads page and click the applicable Sync Installer link to download the Phocas Sync Installer to your computer.

Step 2: Install Sync

  1. Locate the downloaded SyncInstaller zip file on your computer.

  2. Unzip the file (right-click the file name and click Extract All), taking note of where the file will be extracted.

    image-20240612-002702.png

  3. Locate the extracted SyncInstaller file, then right-click > Run as Administrator.

  4. Follow the steps in the Sync Installer wizard, taking note of where the Sync application will be installed on your computer. If you are reinstalling Sync, ensure you install the file in the Sync application folder, not the Sync files, or else it will overwrite your existing projects.

Step 3: Configure Sync

The configuration of Sync involves connecting to your Phocas site and creating a source. The source is the directory in Phocas to which you will send the data items.

  1. On your computer, locate and open the Sync folder.

  2. Click the Configuration tab.

  3. Enter a name for your Sync source.

    • Use a name that will allow end users to easily identify the data, as it is displayed in two places in Phocas. See View sync sources and items in Phocas.

    • The source name is not the name of the Sync file itself. See Save the Sync file below.

  4. Enter your Phocas credentials:

    1. Enter the base URL of your Phocas implementation, including the prefix (https://). For example: https://companyname.phocassoftware.com.

    2. Enter your username and password. 

  5. Ignore the Only sync data items used in databases option, as it is not applicable at this stage.

  6. Change the default [30] Days To Retain Logs, if required.

    1. Each time the sync process runs, a log file is saved. You can set how many days you want to keep those logs. After the specified number of days is met, files older than that number of days are automatically removed. If you schedule Sync to run every hour, you will quickly accumulate a lot of log files, so you might want to reduce the number of days. You can change this number later.

  7. Proceed to add data items.

Step 4: Add the data items

After you install and configure Sync, the next step is to add (connect to) the data items that you want to sync to your Phocas site. The Sync setup process is not complete until you add at least one data item and run the sync process. You can add more data items later.

  1. In Sync, click the Data Items tab.

  2. Click Add and select the required data item type. Typically, you will either add files or an SQL table or view but several other data items types are available. Expand the section below to learn about the available types.

You can add the following types of data items:

  • File - A flat delimited file, such as a TXT, CSV or PSV file. You need to these files one at a time. Each file appears as a data item.

  • Directory - A folder that contains files. Multiple files are added at the same time. Only files prefixed with 'phocas_' in that directory are synced. Files not following this naming convention are ignored. Each file appears as a data item, and the 'phocas_' part of the file name is replaced with 'name_'. All sub-directories are traversed and all filenames starting 'phocas_' are processed, not just the starting directory specified. 

  • Spreadsheet  A Microsoft Excel workbook file, such as an XLS, XLSX or XLSB file. You need to add these files one at a time. Multiple sheets are supported, and will be synced as individual data items, named 'filename_sheetname'.

  • SQL - A SQL script for an SQL table or view that produces a data item. A Preview button is available to preview results of the script. You need to enter the appropriate connection details and security credentials before previewing or saving the script. See examples of SQL scripts in the Additional technical information section below.                           

  • Database - A SQL, MySQL, Oracle or custom database. Each table will appear as a data item. You need to enter the appropriate connection details and security credentials. Only table names prefixed with 'phocas_' will be synced.

The supported data types are:

  • bit

  • boolean

  • tinyint

  • smallint

  • int

  • bigint (from 7.0.15)

  • int16

  • int32

  • int64

  • integer

  • int identity

  • date  

  • datetime

  • datetime2

  • smalldatetime

  • decimal

  • money

  • numeric

  • char

  • varchar

  • string

  • text

  • double

  • float

  • nchar

  • nvarchar

  • ntext

  • uniqueidentifier

Precision level of decimal(18,6) in SQL is the maximum that can be synced. If decimals with larger precisions are required, convert the decimal to a float. 

  1. Enter a name and select the path for the item.

  2. Select the applicable options for the item. The available options depend on the data item:

    • Column mapping edit - Available for files. Change the column details, such as rename a heading, change the data type (text, number, date/time, boolean, unicode) and set a field length.

    • Exclude columns - Available for files. Disable a column, to temporarily remove it from the sync process.

    • Autodetect Delimiter - Available for files and directories. Selected by default but you can disable it and select your own delimiter.

    • Ignore Quotes - Available for files and directories. Not selected by default but you can select it to ignore quotes when syncing.

    • Enabled - Available for all data item types. Selected by default but you can disable a data item so it is not included in the sync process.

    • Incremental Process - Available for files and SQL items. Not selected by default but you can enable the incremental process by date or key.

    • Header Rows - Available in directories and spreadsheets.

    • Retain Snapshots - Available for files and SQL items. Not selected by default but you can enable snapshots to be retained.

  3. Click Save.

You might also want to define parameters at this stage but that is an advanced option.

Step 5: Save the Sync file

The Sync file is like a package that contains the sync configuration, source and data items. You must save your Sync file to preserve your sync setup. The Sync file name does not appear anywhere in Phocas; it is for reference only, on your computer.

In Sync, click File > Save, locate and select the Phocas > Sync folder and click Save.

Step 6: Sync the data (manually)

The last step in the process is to sync your data to your Phocas site. Even if you intend to schedule the sync process, you still need to run the sync process manually at this point for the source and data items to appear in Phocas.

Select one of these methods:

  • To sync all the data items, click Sync Now.

  • To sync one or more specific data items, select their rows(s) and click Sync Selected Now.

It might take a few minutes for the sync process to complete. The progress is displayed.

Next steps

After you complete the sync setup process, you can take the following actions:

  • View sync sources and items in Phocas - The data items will appear within the source directory in the data sources panel in Designer, ready to be used in database designs. You can also view the source (and access its data items) on the Administration > Sync Sources page.

  • Schedule the sync process to run automatically.

  • Only sync data items used in a database - This option is only applicable after the first sync process. That is, on your first use of Sync, all the data items will be synced, regardless of whether they are used in any Phocas database designs. Afterward, you can choose to only sync the data items that are used in database designs.

Additional technical information

  • Sync reads the data specified by the user - SQL, text file, spreadsheet etc.

  • The data is checked for changes via a MD5 hash match comparison with the previous Sync session 

    • If there has been no change, the file is skipped.

    • If the file has changed, only the changes are written to a .diff file.

  • The data is processed and validated where possible (for data types, consistent columns etc.), then written to .psv files

  • The .psv and .diff files are zipped and sent to the server

  • The server unzips the files:

    • .diff files are referenced and changes applied to the server copy of the .psv files

    • the .psv files are written to the PhocasSync database

  • Limit the dataset size of a single data item to 4GB. Each data item is different - we've seen some 4GB files that contain 5 million rows / 60 columns, but others that contain 25 million rows / 10 columns.

  • Data items that exceed the 4GB limit can be broken into multiple smaller data items, by years.

From version 8.0.5 onwards, Sync recognizes number columns in Excel. This may affect database builds involving some older synced files where number columns were previously treated as text and manipulated via a ‘CAST’ expression.