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 section 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
Before you download and install any files, please read the System requirements page.
(Applicable if you are reinstalling Sync) Ensure you don’t have Sync open or currently running.
Click the applicable Sync Installer link to download the Sync Installer to your computer:
Locate the downloaded SyncInstaller zip file on your computer.
Unzip the file (right-click the file name and click Extract All), taking note of where the file will be extracted.
Locate the extracted SyncInstaller file, then right-click > Run as Administrator.
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.
On your computer, locate and open the Sync app (PhocasSync.exe).
Click the Configuration tab.
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.
Enter your Phocas credentials:
Enter the base URL of your Phocas implementation, including the prefix (https://). For example: https://companyname.phocassoftware.com.
Enter your username and password.
Ignore the Only sync data items used in databases option, as it is not applicable at this stage.
Change the default [30]Days To Retain Logs, if required.
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.
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.
In Sync, click the Data Items tab.
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.
Five main data item 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.
Supported data types
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.
Recommendations for data items
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.
Enter a name and select the path for the item.
Select the applicable options for the item and click Save. The available options depend on the data item:
Edit the column mapping or exclude columns
This option is available for files. It allows you to change the column details of the file before you running the sync process.
You can:
Edit a column, such as rename a heading, change the data type (text, number, date/time, boolean, or unicode) and set a field length.
Exclude (disable) a column to temporarily remove it from the sync process. This option is suitable when you have very large data sets where not every column is required, as it makes sense to only sync the columns that you want to see in Phocas.
If you are adding a new file, click the Edit button in the Add File window. If you have already added the file, double-click the file in the Data Item tab to open the Edit File window, then click the Edit button.
Make your changes, or to disable a column, clear (uncheck) the Enabled checkbox, then click Save.
After you save the file, an asterisk displays in the Edit Columns column in the Data Items tab.
Select your own delimiter when syncing
This option is available for files and directories.
When you are adding a file or directory data item, the Autodetect Delimiter option is selected by default. As the name suggests, this option automatically recognizes common delimiters that might be used to separate your data, such as commas and tabs, and separates the data into columns accordingly.
You might prefer Sync to ignore these delimiters and select a specific delimiter to use instead. This option is useful if your data contains other delimiters, such as ^ symbol. If this is the case, clear (uncheck) the Autodetect Delimiter checkbox and enter a delimiter in the box below it.
Ignore quotes
This options is available for files and directories. It isn't selected by default but you can select the checkbox to ignore quotes when syncing to handle files containing embedded quotes in the data, such as a representation of inches. For example, a 12" ruler.
When the Ignore Quotes checkbox is NOT selected, quotes in data are interpreted as the opening quotes of a single string. This can cause a single cell to be populated with rows of data, until the next set of quotes is found. The difference is shown in the tables below.
If you select the Ignore Quotes checkbox, any embedded quotes are treated as normal characters, and the data is written out correctly.
Disable a data item from syncing
This option is available for all data item types. It's selected by default. As the name suggests, this option enables the item to be synced.
You might prefer to disable an item, so Sync temporarily removes it from the sync process. If this is the case, clear (uncheck) the Enabled checkbox, either when adding or editing the item, or on the Data Items tab.
Disabling an item doesn't delete the item; it just means that no new data for that item will be synced as long as it remains disabled. The Last Successful Sync date will not change.
To re-include that data item in the sync process, select the Enabled box.
Set data items to sync incrementally
This option is available for files and SQL items. It's not selected by default but you can select the checkbox to activate the incremental process.
The benefit of this incremental sync is the prevention of very large sync items when only the recent data has changed. Your historical data is retained while new data is synced. The incremental process typically syncs all data within an offset period, for example, rolling 30 days.
There are two incremental process methods, by date or by key.
Incremental syncs work on purge and append. Phocas works out the earliest date or key in the item you're syncing and deletes all data from that date or key forward from the server/cloud. Then the item is synced in the usual way - all the data is added to the existing data on the server/cloud.
Incremental process - by date
The data item must contain a DateTime column, which is often the transaction date. If the item does not have such a column, this option is unavailable.
Select Incremental Process > By Date, then select the applicable column.
If you know your item has a Date column but this option is still unavailable, click Edit and set the data type to DateTime, then try again. See Edit the column mapping section above for more information.
Incremental process - by key
Any data type column can be selected in this case. As such, this option can be used with automatic columns for text files. During the sync process, the new data is merged into any existing data. New rows (as determined by the selected columns) will be inserted. Existing rows (as determined by the selected columns) will be updated to new values of the other columns.
Select Incremental Process > By Key, then select one or more columns to be used as a uniquely identifying key for your data.
Troubleshooting
After you have synced an item using an incremental process, the structure of the item should remain unchanged for subsequent syncs. Changing the columns will cause subsequent syncs to fail. To avoid this happening, select Incremental Process > None,sync the item, then select the incremental process again.
Include header rows
This option is available in directories and spreadsheets.
Retain snapshots
This option is available for files and SQL items. It's typically used to convert a values as at now type query into over-time analysis.
A popular use case is Stock on Hand, where a snapshot is taken each day. Without enabling this option, the Phocas database would just show the stock from yesterday. By retaining monthly snapshots, you could see what the stock was yesterday, and in any previous months, allowing variance reporting and trend analysis. This historical data is usually not available in an ERP system.
Snapshots are taken for every moment, even when there is no change to source data.
Impact of snapshots on build times
Use caution when retaining snapshots, as they can result in very large volumes of data being stored, which can cause databases to take too long to build. This is especially true for daily snapshots. Snapshots should NOT be used for large data sources, such as sales invoices.
Select whether you want snapshots retained daily, monthly or a custom interval, such as quarterly. For example, if you select Daily, you can sync as many times as you like and Phocas keeps a copy of the data as at the last sync of each day. Similarly, if you select monthly, a copy of the data as at the end of the month is kept.
View snapshots
Snapshots are kept over time in the relevant Sync item table, with a SyncMoment column automatically added. To view the items in Phocas, go to Administration > Sync Sources and select the relevant source to view the data items in more detail.
You will see not only the latest synced data but also data from retained snapshots. For example, in the image below, the most recent sync was on 2018-01-17. You can see this data, and data from the previous sync a day earlier, on 2018-01-16.
Define parameters and iterations (advanced)
This advanced option is available for files, directories and SQL data items. It allows you to specify parameters to use the Sync file for multiple organizations in Phocas. The parameters are passed to Sync and as a result, the information structure is sent to other organizations.
Select the Parameters tab and click Add.
Enter the parameter name. This must be alphanumeric only; you can't use special characters.
Using parameters as values in supported types
Example of use: Connection string
Example of use: SQL
Parameter substitution in scheduled Syncs
You can substitute different values for defined parameters via scheduled Syncs (SyncCLI).
Individual parameters: To substitute a parameter value, the “-p” switch is used, followed by the parameter name and quoted value. The quoted value is only used for the duration of the session, i.e. the original value defined in Sync remains unchanged.
-p:parameterName “newValue”
-p:server ”localhost“
Note if a parameter name contains spaces, it must also be quoted:
The Iterations option is an advanced option we recommend you use in consultation with a Phocas consultant. It’s useful in the case of multiple customer data sources, particularly multiple ERPs, linked to one Phocas site. Typically, these data sources are identical, except for having unique credentials and one or more different parameter values. As the setup is almost the same for each source, you can add iterations to the Sync process. This iterative approach removes the need for separate configuration files and makes the Sync setup quicker and less complex.
Each time Sync is triggered, all items are processed for each iteration, in order, with the default parameters being overridden by the iteration parameters.
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.
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
How Phocas Sync works
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
Note about number columns
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.
SQL script examples
Simple SELECT
select top 200 id from entity_1 order by id
CTE
with customers (customer, city, state, status) as
(
select cat_1, cat_2, cat_3, cat_7 from entity_1
where id < 1000
),
active_customers as
(
select customer, city, state from customers
where status = 'active'
),
active_VA_customers as
(
select customer, city from active_customers
where state = 'VA'
)
select * from active_VA_customers
where city like 'N%'
Execution scripts
declare
@query varchar(max),
@insertQuery varchar(max),
@database varchar(100),
@table1 varchar(100),
@table2 varchar(100),
@period varchar(100)
set @database = 'phocas_testing_sales'
set @table1 = 'Transaction_0_1'
set @table2 = 'QuantaB'
set @period = 'PeriodIndex_0'
select @query = '';
select @insertQuery = 'select StreamsID, PeriodIndex, Branch_1, Scalar_1, Scalar_2, Scalar_3, RecordCount from {database}.dbo.{table1}
union all
select StreamsID, pm.PeriodIndex AS PeriodIndex, Branch_1, sum(Scalar_1) as Scalar_1, sum(Scalar_2) as Scalar_2, sum(Scalar_3) as Scalar_3, count(*) as RecordCount
from {database}.dbo.{table2}
inner join {database}.dbo.{period} pm on (pm.Moment = QuantaB.Moment) group by StreamsID, pm.PeriodIndex, Branch_1'
select @insertQuery = REPLACE(@insertQuery, '{database}', @database)
select @insertQuery = REPLACE(@insertQuery, '{table1}', @table1)
select @insertQuery = REPLACE(@insertQuery, '{table2}', @table2)
select @insertQuery = REPLACE(@insertQuery, '{period}', @period)
exec (@insertQuery)
SyncCLI error codes
0: success.
1: Sync file doesn't exist.
2: incorrect service URL, username or password.
3: all other exceptions.
Enter the number of days you want to retain the snapshots and click Save.
Enter a value. Values can not contain parameters. Double quotes cannot be used.
Multiple parameters: You can substitute several parameters for a range of data items.
