A data sync in Cinchy synchronizes information between a source and a destination. This guide walks you through the process of setting up your data syncs.

Basic workflow

The basic workflow of a data sync in Cinchy is:

1. Install the necessary components.

2. Choose your data sync type.

3. Choose your design pattern.

4. Define and configure your data sync source.

5. Set up your data sync destination.

6. Specify Sync Actions.

Overview

You have two ways to set up a data sync in Cinchy:

1. Use the Connections UI to input and save configuration details. The data will be stored as an XML file in the Data Sync Configurations table.

2. Directly upload an XML config into the Data Sync Configurations table.

Set up a data sync with the Connections UI

To use the Connections UI, open the Connections Experience.

Connections UI workflow

The UI has six tabs. Each tab requires data for your connection setup:

1. Info

2. Source

3. Destination

4. Sync Actions

5. Post Sync (Optional)

6. Jobs (Optional)

Info Tab

The Info Tab has fundamental details about your data sync, such as its name and access controls. You must add a name and select an Admin Group. You can also use variables for advanced functionality.

Source Tab

The Source Tab defines the origin of your data sync. Each data source type, from specific file formats to integrated software systems, requires unique parameters. See the Supported Data Sources list for available options.

Destination Tab

The Destination Tab identifies where your data sync goes. Each destination comes with its own set of parameters. You must map each destination to its source. Consult the Supported Destinations directory for specifics.

Sync Actions Tab

In the Sync Actions Tab, you can choose your preferred data action. Your main options are Full File Sync and Delta Sync. Not sure about the differences? Check out this comparison for more details.

Post Sync Tab (Optional)

In the Post Sync Tab, you can use Cinchy Query Language (CQL) to refine the post-sync data. For example, you could set up a post-sync script to push retrieved data values into a specific Cinchy table. You can find more on this in the Post-sync scripts page.

Jobs Tab (For Batch Data Syncs)

In the Jobs tab, you can start, monitor, and troubleshoot batch jobs. You can also view sync outputs or download detailed logs for analysis. For non-default user operations, ensure you have the right credentials and permissions.

Execution Tab (For Real-time Syncs)

Track potential issues with your real-time syncs. Syncs become operational once the Listener Config is enabled—no need for manual job starts.

Set up a data sync with Config XML

You can also set up a data sync in Cinchy by uploading a formatted XML into the Data Sync Configurations table. This method is only recommended for those with advanced knowledge in data sync operations.

1. Access the Data Sync Config Table: In the Cinchy platform, open the Data Sync Configurations table.

1. Insert Data Sync XML: For a new row, double-click the Config XML column and paste your Data Sync XML.

2. Define Group Permissions: Adjust the required permissions in the appropriate columns.

3. Review the XML Data: After finalizing your Data Sync XML, return to the Data Sync Configurations table.

    The Config XML auto populates columns like Name and Config Version.
    

1. Initiate Sync with the CLI: If you haven't installed the CLI, refer to the CLI installation guide. Otherwise, launch PowerShell and navigate to the Cinchy CLI directory.

2. Run the CLI Command:

.\Cinchy.Connections.CLI.exe syncdata -s "pilot.cinchy.co/Training/Cinchy/" -u "admin" -p "DESuEGqmx55yl2PYxa4ncc+5+bLkoVIFpgs0Lq6hkcU=" -f "Data Sync Name"

More details on CLI commands can be found in the CLI commands list.

Set Up a listener config (real-time syncs)

If you are setting up a real-time sync, you must set up a listener configuration. You must configure your Event Stream Source with your data sync information. You can review an more on the Listener Config here.

1. Navigate to the Listener Config table in Cinchy (Image 12).

1. In a new row, add in your listener config configuration data. See Supported real-time sync sources for more information.

2. Make sure to set the config to Enabled.

Examples

The following pages show basic examples of both batch and real-time data syncs. Use these examples as a reference point for learning more about Cinchy data syncs.

• Batch Data Sync Example

• Real-Time Data Sync Example

Batch syncs

Batch syncs work by processing a group or a ‘batch’ of data all together rather than each piece of data individually. When the data sync triggers, it will compare the contents of the source to the target. The Cinchy Worker will decide to add, delete, or update data. You can run a batch sync as a one-time data load operation, or you can schedule it to run periodically using an external Enterprise Scheduler

Execution flow

At a high level, running a batch data sync operation performs these steps (Image 1):

1. The sync connects to Cinchy and creates a log entry in the Execution Log table with a status of running.

2. It streams the source and target into the CLI. Any malformed records or duplicate sync keys are written to source and target errors CSVs (based on the temp directory)

3. It compares the sync keys to match up source and target records

4. The sync checks if there are changes between the matched records

5. For the records where there are changes, groups them into insert, update, and delete batches.

6. It sends the batches to the target and records failures in the sync errors CSV and Execution Errors table.

7. Once complete, it updates Execution Log entry with final status and execution output.

Real-time data sync

In real-time syncs, the Cinchy Listener picks up changes in the source immediately as they occur. These syncs don't need to be manually triggered or scheduled using an external scheduler. Setting up a real-time sync does require an extra step of defining a listener configuration to execute.

Real-time sync is ideally used in situations where results and responses must be immediate.

For example, a document that's constantly checked and referred to should have the most up-to-date and recent information.

You can use the following sources in real-time syncs:

• Cinchy Event Broker/CDC

• MongoDB Collection (Event Triggered)

• Polling Event

• REST API (Event Triggered)

• Salesforce Platform Event

Execution flow

At a high level, running a real-time data sync operation performs these steps (Image 2):

1. The Listener is successfully subscribed and waiting for events from streaming source

2. The Listener receives a message from a streaming source and pushes it to SQL Server Broker.

3. The Worker picks up message from SQL Server Broker

4. The Worker fetches the matching record from the target based on the sync key

5. If there are changes detected, the worker pushes them to the target system. Logs successes and failures in the worker's log file.

Overview

When configuring a data sync you must set your sync behaviour. You have two options for this: Full File or Delta.

Full File syncs intake both the source and the destination data and reconcile the records by matching up the sync key. This determines any differences and allows it to perform updates, inserts, ignores, or deletes at the destination.

Delta syncs skip the reconciliation process. In batch syncs, it simply grabs records from the source and inserts it into the destination. In real-time syncs, it may act differently depending on the event type. For example, when using the Cinchy Event Broker/CDC with an insert event, a delta sync will insert the data into the destination, an update event will update, etc.

Delta syncs also have the option to provide an "Action Type Column" for REST API destinations. This reads the value of the source record from a specified column. If the value is INSERT, then it inserts the record, UPDATE, then it updates, DELETE, then it deletes.

Full file sync

When using the Full File synchronization strategy there are four distinct sections that must be configured: the Sync Key and the Sync Record Behaviours, which include actions for New, Dropped, and Changed records. (Image 1).

Sync key

The sync key is a unique key reference you use for data syncs from the data source into your destination. You can use it to match the data between the source and the target for updates on changed records.

To set this using a config XML, use the following guide:

• Elements: <SyncKeyColumnReference>

  • This is used in the <SyncKey> element when specifying which columns in the Target Table to be utilized as a unique key for the syncing process.

• Contained-In: <SyncKey>

• Attributes: name. The name of a column in the destination that you are syncing data into.

Sync Record Behaviour

The Sync Record Behaviour divides into three subsections which defines what action to take on certain records (Image 2).

New Record Behaviour

New Record Behaviour defines what action to take when a new record is found in the sync source. This can be either INSERT or IGNORE.

To set this using a config XML, use the following guide:

Dropped Record Behaviour

Dropped Record Behaviour defines what action to take when a new record isn't found in the sync source, but exists in the target. This can be either DELETE, IGNORE, or EXPIRE.

To set this using a config XML, use the following guide:

2.2.3 Changed Record Behaviour

Changed Record Behaviour defines what action to take when a new record with a sync key is found in the sync source and also exists in the target. This can be either Update or Ignore.

To set this using a config XML, use the following guide:

Delta Sync

When using the Delta synchronization strategy there is one optional configuration that you can expose when running a sync with a REST API destination (Image 3).

The Action Type Column reads the value of the source record from a specified column. If the value is INSERT, then it inserts the record, UPDATE, then it updates, DELETE, then it deletes.

Appendix A

Full File Sync - Conditional Changed Record Behaviour

Added in Cinchy v5.6, the Changed Record Behaviour - Conditional feature allows you to define specific conditions upon which to update your records (Image 4).

• You can add multiple Conditions to a single data sync by using the AND/OR and +Rule buttons.

• You are able to group your Rules into a rule set by using the +Ruleset button.

• If your Condition evaluates to true then it will update your records

• Use the The left-most drop down to select either a source or a target column as defined in your Source and Destination tabs

• Use The centre drop-down to select from the following options:

  • \=

  • !=

  • Contains

  • Is Null

  • Is Not Null

• Use the The right-most drop-down to:

  • Add a plain value (ex: text, numerical, etc.) This will adjust based on the column data type picked in the left-most drop down. For example, if in the source schema the column is a date, then it renders a date picker.

  • Select either a source or a target column as defined in your Source and Destination tabs (when used in conjunction with the Use Columns checkbox)

For example, the below condition would only update records where the target column "Name" is null (Image 5).

Overview

This page outlines some common design patterns. You can use this basic list as a starting point to design your own data syncs.

Syncing changes vs. syncing full datasets

When creating data sync, you need to know if you are synchronizing the full data set or just a subset of the data.

Full data synchronization

Set <DroppedRecordBehaviour type="DELETE" /> so that any records that are no longer in the source are deleted in the target.

Partial data synchronization

Set <DroppedRecordBehaviour type="IGNORE" /> otherwise, it will delete any records that aren't in your partial recordset. You are unable to delete any records during partial data synchronization.

Partitioning your data synchronization

You can create a full data synchronization on a partial dataset by filtering the source and target. For example, if you want to sync transactions from one system to another but there are a lot of transactions, you can run a data sync where you filter by<Filter>[Transaction Date] > 100000 </Filter> In both the source and the target. This way, you can sync a smaller dataset while still being able to perform deletions and ensuring a full synchronization of that partition of the data.

Syncing linked data

When syncing data that's linked to other data the order of the data sync is important. You should order the sync to the data is linked to first.

For example, if you have customers and invoices and the invoices link to each customer, then sync the customer data first. Therefore, when the invoices sync, they will link to the appropriate customer as the customer data is already in the target.

Populating reference data

To create a reference data set (such as a country code) based on a set of shipping labels then you should first run the data against the Country Codes table before the shipping labels sync to the labels table.

In this scenario a supressDuplicateError=”false” should be set up when running the data against the Country Codes table as duplicates are expected, which aren't ‘error’s’ and must be identified.

Creating a reference master data set

Sometimes different reference data values can mean the same thing, but different systems use different codes. In Cinchy’s country code example under ‘Populating Reference Data’:

• System A uses the full country name (ex. Canada, United States),

• System B uses the 2 letter ISO code (ex. CA, US), and

• System C uses the 3 letter ISO code (ex. CAN, USA).

All three of these systems can sync into one shipping label table, with a link to Country, but depending on the system, we use a different link column reference. The same column mapping will look slightly different in each data sync to the Shipping Labels table.

You can change the display column in the Shipping Labels table to switch between the columns if you decide on a different code as the golden standard, without having to modify your data sync configurations.

System A config

<ColumnMapping sourceColumn="Country" targetColumn="Country" linkColumn="ISO 3166 Name" />

System B config

<ColumnMapping sourceColumn="ShippingCountry" targetColumn="Country" linkColumn="ISO 3166-2 Letter Code" />

System C config

<ColumnMapping sourceColumn="Country" targetColumn="Country" linkColumn="ISO 3166-3 Letter Code" />

Multiple sources - separate records

If you sync from different sources into the same data set, you need to add the source of the data as a column to avoid overwriting records from other sources. This column will also be added to your sync key. For contacts you might have:

<CalculatedColumn name="Source System" formula="CONCAT('','Salesforce')" dataType="Text" />
...
<SyncKey>
            <SyncKeyColumnReference name:"Source System" />
            <SyncKeyColumnReference name:"Email Address" />
</SyncKey>

Once all your data from various systems in Cinchy, you can master that dataset and sync the master record back into any source systems. These syncs would filter the source by where the [Master Record] column is set to true, and sync on the unique identifier without the source system. You would also only want to update records already in the source, rather than deleting unmastered data or adding all records from other systems.

<SyncKey>
           <SyncKeyColumnReference name="Email Address" />
</SyncKey>
<NewRecordBehaviour type="IGNORE" />
<ChangedRecordBehaviour type="UPDATE" />
<DroppedRecordBehaviour type="IGNORE"/>

Multiple sources - enriching fields

To use different sources to enrich different fields on the same record,you should set the dropped record behaviour to ignore, and update the columns based on a sync key on the record.

The ability to create new records depends on the source system. Internal systems, such as customer invoices, should be able to create new customer records.

External data sources, such as a published industry report, will only add noise to your table when you attempt to insert new records.

<SyncKey>
        <SyncKeyColumnReference name="Email Address" />
</SyncKey>
<NewRecordBehaviour type="IGNORE" />
<ChangedRecordBehaviour type="UPDATE" />
<DroppedRecordBehaviour type="IGNORE"/>

Post processing

You can add post sync scripts to a data sync configuration that run after the data sync has been completed.

For example, you can run a query to update the assignee on a lead after the leads data sync runs.

For example, a query that only updates where the assignee is empty, except for a high-value lead, where it's reassigned to the most senior person on each team (based on another table in the instance that has the seniority and team of each sales director).

<CinchyTableTarget model="" domain="Revenue" table="Leads" suppressDuplicateErrors="true" >
     <PostSyncScripts>
          <PostSyncScript name="Script1" timeout="60">
               <CQL>INSERT CQL HERE</CQL>
          </PostSyncScript>
     </PostSyncScripts>
</CinchyTableTarget>

No unique identifier

If you have a file source (delimited, csv or excel) and you want to sync data into Cinchy that doesn't have a sync key, you can add a calculated column for the row number of that record.

<CalculatedColumn name="RowNumber" formula="row_number()" dataType="Number" /

You will also want to add a unique calculated column for the file name to be able to re-run the data sync if any failures occur.

Bi-directional sync

To run a bi-directional sync, you need to identify a source system unique identifier and run the following four data syncs for bidirectional syncing.

If one of the systems can't create new records, you can omit the data sync configuration where you create new records in the other system.

1. Sync from external source into Cinchy (new records)

Run a data sync from the source into Cinchy filtering the source by records where the Cinchy ID (a custom field you create in the source) is empty. Insert these records into Cinchy and make sure to populate the source unique identifier column in Cinchy.

2. Sync from Cinchy into external source (new records)

You can also sync data from Cinchy to the external source by inserting any records where a source unique identifier is empty.

3. Sync from external Source into Cinchy (existing records)

Now that all records exist in both the external system and Cinchy, you can sync data based on the target system's unique ID. iIn this case, you are syncing data from the external source into Cinchy based on the Cinchy ID. Filter out records where Cinchy ID is null here to avoid errors; the sync will pick up the new records the next time the sync runs.

4. Sync from Cinchy into external source (existing records)

You can sync any data changes from Cinchy into the external source using the external unique identifier as the sync key, filtering out records in Cinchy where the external identifier is empty.

Caching query data

To run intensive summary queries on the platform, you must create a data sync to cache the data in a Cinchy table. To do so, sync the results of your Cinchy query into a Cinchy table with the same schema. You can then schedule the CLI to run as often as you would like your cache to expire. You can point other queries or reports to the query from this table, rather than the complex query itself.

Configuration changes

Adding fields

To add more fields to sync, make sure that the columns are in the target system first. You can then swap in the new data sync configuration whenever and it will get picked up for future execution.

Removing fields

To remove fields from a data sync, swap out your data sync configuration. You can optionally delete the field in the source or target system afterward.

Changing column mapping

If you aren't adding or deleting fields, swap out your sync configuration. To add or remove fields, follow the guidelines above for when to swap in the config versus making the data model changes (add columns first, swap out config, validate config, delete unneeded columns).

Changing sync key

If you want to change the sync key, swap out your data sync configuration. It's a good idea to check if your new sync key is unique in both your source and target. The CLI worker will sync using the first record it finds in the source to the first record it finds in the target. Checking for duplicate sync keys will allow you to understand whether any unexpected behaviour will occur.

Overview

This page provides information on both Schema Columns (used when configuring Data Sync Sources) and Column Mappings (used when configuring Data sync Destinations).

Schema columns

Schema columns refer to your mapping on your data source. For example, if your source is a CSV with the columns 'Name', 'Age', and 'Company', you would set up three matching schema columns in the Connections UI or data sync XML. These schema columns map to your destination columns for your data sync target, so that the data knows where to go.

The only difference between the setup of schema columns in the Connections UI compared to data sync XML is the addition of the Alias column, which only appears in the Connections UI. The Alias column gives the user an alternative name to the column mapping (usually used for easier readability). The column types are detailed below.

Standard column

Fill in the following attributes for a Standard Column (Image 1):

• Name: The name of your column

• Formula: The formula associated with your calculated column

• Data Type: The return data type of your column, this can be either:

  • Text

  • Date

  • Number

  • Boolean

• Description: Describe your column

• Advanced Settings:

  • You can select if you want this column to be mandatory

  • You can choose whether your data must be validated

• For Text data types, you can choose whether to trim the whitespace.

To add in a Transformation > String Replacement enter the following:

• Pattern for your string replacement

• Replacement

Standard calculated column

Fill in the following attributes for a Standard Calculated Column (Image 2):

• Name: The name of your column

• Formula: The formula associated with your calculated column

• Data Type: The return data type of your column, this can be either:

  • Text

  • Date

  • Number

  • Boolean

• Description: Describe your calculated column

• Advanced Settings:

  • You can select if you want this column to be mandatory.

  • You can choose whether your data must be validated.

Conditional calculated column

Fill in the following attributes for a Conditional Calculated Column (Image 3):

• Name: The name of your column

• Data Type: The return data type of your column, this can be either:

  • Text

  • Date

  • Number

  • Boolean

• Description: Describe your calculated column

• Advanced Settings:

  • You can select if you want this column to be mandatory.

  • You can choose whether your data must be validated.

• Condition:

  • Name:

  • IF: Click Edit to create the "if" for your Conditional Statement (Image 4)

• Then: Click Edit to create the "then" for your Conditional Statement (Image 5)

• Default: Click Edit to create your default expression (Image 6)

JavaScript Calculated Column

Fill in the following attributes for a JavaScript Calculated Column (Image 7):

• Name: The name of your column

• Data Type: The return data type of your column, this can be either:

  • Text

  • Date

  • Number

  • Boolean

• Description: Describe your calculated column

• Advanced Settings:

  • You can select if you want this column to be mandatory.

  • You can choose whether your data must be validated.

• Script: Enter in your JavaScript

Columns in XML

This XML element defines each column and their data type in the data set :

<Column
    name="string"
    dataType="Text"| "Date"| "Number"| "Bool"| "Geometry"| "Geography"
    ordinal="int"                     -- Depends on the data source
    maxLength="int"                   --OPTIONAL
    isMandatory=["true", "false"]     --OPTIONAL
    validateData=["true", "false"]    --OPTIONAL
    trimWhitespace=["true", "false"]  --OPTIONAL
    description="string"              --OPTIONAL
    inputFormat="string"              --OPTIONAL
    >
    ...
</Column>

Attributes descriptions

name

The user defined name for each column. This is used in when you want to indicate the name of the sourceColumn.

dataType

The data type of each column could be Text, Date, Number, Boolean, Geometry, or Geography.

To sync into a Cinchy table with a Geometry or Geography column, those respective data types must be used in the data sync, and the input should be in well-known text (WKT) format.

For example, given line 1 of a .csv file:

Name, Location, Age

The ordinal for Age would be 3.

maxLength

The max length of data in the column.

isMandatory

Boolean value that determines if the field is a mandatory column to create a row entry.

A defined SyncKey column of any data type can be checked for NULL values using isMandatory=true. When validation fails, an error message is displayed in the command line. For other columns when validation fails, the Execution Errors Table is updated with Error Type, Mandatory Rule violation for that column and row that failed.

validateData

Boolean value determining whether to validate the data before insertion. Valid data means to fit all the constraints of the column (dataType, maxLength, isMandatory, inputFormat). If the data isn't valid and validateData is true, then the entry won't be synced into the table. The Execution Errors Table is also updated with the appropriate Error Type (Invalid Format Exception, Max Length Violation, Mandatory Rule Violation, Input Format Exception)

trimWhitespace

Boolean value determining whether to trim white space.

description

Description of the column.

inputFormat

Date fields support the inputFormat which adheres to the C# .NET DateTime.ParseExact format. See here for reference.

Elements:

Column Mappings

Column mappings defines how a single column from the data source maps to a column in a target table. Each <ColumnMapping> has both a source and a target. If the destination is a Cinchy table and the target column is a link, then a third attribute becomes available called linkColumn which you can use to specify the column used to resolve the linked record from the source value. The value of sourceColumn should match name attribute of Source . The value of targetColumn should match that of the target table.

Below is an example of a Column Mapping in the experience followed by the equivalent XML. In the experience, the Source Column attribute is a dropdown of columns configured in the Source Section.

<ColumnMapping
    sourceColumn="string"
    targetColumn="string"
    linkColumn="string"
    >
</ColumnMapping>

Attributes:

sourceColumn

The name of the column in the data source. The name corresponds to the user defined name from the elements in the schema.

targetColumn

The name of the column in the target table. This would be a table that's already created in Cinchy and defined in the Target.

linkColumn

The name of a column from the linked table. If the target column is a linked column from another table, you may input data based on any of the linked table's columns.

Examples

Examples 1 and 2 show calculated columns within the Connections UI and their relevant XML.

Example 3 demonstrates the use of JavaScript in Calculated Columns.

Example 1: XML

The value of this column for each record is whatever the value is of the lob parameter.

XML equivalent

<Parameters>
<Schema>
  <CalculatedColumn name="lob" formula="@lob" dataType="Text" maxLength="100" 
      isMandatory="false" description="" trimWhitespace="true"/>
  <CalculatedColumn name="name" formula="CONCAT(firstname, lastname)" 
      dataType="Text" maxLength="100" isMandatory="false" 
      description="" trimWhitespace="true"/>
 </Schema>

Example 2: XML

The values of two columns are concatenating together.

XML equivalent

<Parameters>
<Schema>
  <CalculatedColumn name="lob" formula="@lob" dataType="Text" maxLength="100" 
      isMandatory="false" description="" trimWhitespace="true"/>
  <CalculatedColumn name="name" formula="CONCAT(firstname, lastname)" 
      dataType="Text" maxLength="100" isMandatory="false" 
      description="" trimWhitespace="true"/>
 </Schema>

Example 3: JavaScript

This example splits a [Name] column with the format "Lastname, Firstname" into two columns: [First Name] and [Last Name].

       <Schema>
            <Column name="Name" dataType="Text"/>
            <CalculatedColumn name="First Name" ordinal="3" dataType="Text">
                <Script>
            function firstName(Name) {
                return Name.substr(Name.indexOf(', ')+2);
            }
                        
            function calc(currentRecord) {
                if (currentRecord['Name'])
                    return firstName(currentRecord['Name']);
                    return '';
            }            
            </Script>
            </CalculatedColumn>
            <CalculatedColumn name="Last Name" dataType="Text">
                <Script>
                    function lastName(Name) {
                        return Name.substr(0,Name.indexOf(', '));
                    }
    
    
                    function calc(currentRecord) {
                        if (currentRecord['Name'])
                        return lastName(currentRecord['Name']);                            
                        return '';
                    }   
                    </Script>
            </CalculatedColumn>
        </Schema>

Attributes

name

The user defined name for each calculated column. This is used in when you want to indicate the name of the sourceColumn.

formula

CQL expression used to define formula. Supported functions:

dataType

The data type of each column could be Text, Date, Number or Bool.

maxLength

The max length of data in the column.

isMandatory

Boolean value that determines if the field is a mandatory column to create a row entry.

validateData

Boolean value determining whether to validate the data before inserting. Valid data means to fit all the constraints of the column (dataType, maxLength, isMandatory, inputFormat). If the data isn't valid and validateData is true, then the entry won't sync into the table. The Execution Errors Table also updates with the appropriate Error Type (Invalid Format Exception, Max Length Violation, Mandatory Rule Violation, Input Format Exception)

description

Description of the column.

trimWhitespace

Boolean value that determines whether to trim white space.

The pages listed under this section aren't required for most data syncs, but they can help you create more robust use cases when applied correctly.

Auth request

When syncing a Data Source, you may have the option to add extra configuration sections, such as an Auth Request, under the "Add a Section" drop down tab in the Connection Experience (Image 1).

1. To add in an Auth Request, fill in the following parameters:

• HTTP Method: Either POST or GET

• Endpoint URL

• From the drop down, you can also add:

  • Request Headers

  • Body

  • Variables to Extract

Request Headers

When configuring a Data Sync, you may have the option to add in extra configuration sections, such as an Request Headers, under the "Add a Section" drop down tab in the Connection Experience (Image 1).

1. To add in Request Headers, fill in the following parameters:

• Header Type:

  • Authorization: You can use an authorization request header to provide credentials that authenticate a user with a server, allowing access to a protected resource. Selecting this header defines the Header Value as a password field.

  • Content-Type

  • Header

• Name: The name of the HTTP Header to add

• Is Encrypted?

• Header Value

Overview

Post sync scripts are written in CQL and can be added to the end of a sync to allow you to do more with your data, such as inserted retrieved values into a Cinchy table.

Example

When you run the batch job, it will check for updates in the source table. These updates trigger the REST API to fetch our defined value, which we can use in a post-sync script. In this example, the script will insert this value into a second table, [Product].[Response Table].

The following steps will walk you through how to use this functionality.

1. Set up your data sync to a REST API. You can review the Source (Image 1), Destination (Image 2), and Sync Behaviour (Image 3) below.

1. Under the Post Sync tab, input the CQL that takes the defined variable from the REST API and insert it into the [Product].[Response Test] table (Image 4).

1. Add in your Permissions and click Save.

2. After you configure the sync, run the job. It will check for new data in the Name column on the [Product].[Names] table (Image 5). When found, it will trigger the REST API to GET the “value” variable. The post sync script will take that value and INSERT it into the [Product].[Response Test] table (Image 6).

Overview

Variables are values that can be dynamically inserted when the sync job is run. The variables you define here can be referenced in fields in other parts of your sync config (using the @ prefix) and when the job is run you can be prompted for their values.

The execution variables are either passed in at the time of execution or calculated through a formula. The value of the name attribute is passed in as command line option, param-values. (Optional, if the path to the source file to load is specified in the path attribute of the source element or calculated column formula don't reference execution variables)

Supported Formulas

You can choose to just use plain text in the Name field of the Variable or you can use a calculated formula.

The following formulas are currently supported by Connections.

• FILENAME(<some-path>, <some-regex>): The FILENAME formula takes in two variables:

  1. A reference to the first parameter (like a file path)

  2. A regular expression that includes a match group. The first match group's value is assigned to the variable. The FILENAME function applies the regex only to the name of the file (excluding the directory structure).

• FILEPATH(<some-path>, <some-regex>): The FILEPATH formula takes in two variables:

  1. A reference to the first parameter (like a file path)

  2. A regular expression that includes a match group. The first match group's value is assigned to the variable. The FILEPATH function executes the regex against the full file path (including the directory structure).

• GUID(): The GUID formula uses a random GUID for that variable's value. Use GUID() to generate a unique identifier to use during the context the sync. For example, use it to track changes made from a particular sync.

• ENV(<place-environment-variable-here>): The ENV formula uses an environment variable available in the connections/worker pods as the value of the variable.

Examples

Below are the three Variable examples shown in the Connections experience, followed by the relevant XML:

Example 1

A name attribute reference an execution variable (Image 2). You can use this when pulling in a local file for an Excel sync and specify the path to your file on your machine.

Example 2

The FILEPATH function takes in two variables:

1. A reference to the first variable, such as a file path.

2. A regular expression that includes a match group (Image 3). The first match group's value is assigned to the variable. FILEPATH function executes regex against the full file path (including the directory structure). For the full formula, see the XML example below.

Example 3

The FILENAME function takes in two variables:

1. A reference to the first variable, such as a file path.

2. A regular expression that includes a match group (Image 4). The first match group's value is what gets assigned to the variable. FILENAME function applies the regex only to the name of the file (excluding the directory structure).

Example 4

The ENV formula uses an environment variable available in the connections/worker pods as the value of the variable (Image 5). An example use case for this would be a situation where the URLs used in a REST API sync is different across environments -- instead of manually updating the syncs with the various URLs, you can use this formula to automatically calculated it from your pod configuration files.

XML examples

This secret can then be used in the REST Header (Image 7).

XML examples

Appendix A

This section details how to configure your platform to use Environment Variables.

Configure environment variables in IIS

To create or change environment variables on Windows:

1. On the Windows taskbar, right-click the Windows Icon > System.

2. In the Settings window, click Related Settings > Advanced System Settings > Environment Variables (Image 8).

1. Under System Variables, click New to create your new environment variable (Image 9).

Configure environment variables in Kubernetes

To configure an environment variable in Kubernetes, do the following:

Add the variable to your cluster

1. Navigate to your cinchy.kubernetes\environment\_kustomizations\_template\instance\_template\connections\kustomization.yaml file.

2. Under patchesJson6902 > patch, add your environment variable as shown in the code snippet below. Input your own namespace and variable name where indicated.

Add the variable to your pod

1. Navigate to your platform_components/connections/connections-app.yaml file.

2. Under Spec > Template > Spec > Containers > ENV, add in your environment variable. This addition depends on what value you are using as an environment variable. The below code snippet shows a basic example:

1. Make the same change to the platform_components/worker/worker-app.yaml file.

2. Push and merge your changes.

Overview

You can use filters in your source and target configurations to define specific subsets of data that you want to use in your syncs.

Source filters

When syncing a Data Source, you may have the option to add additional configuration sections, such as a Filter, under the "Add a Section" drop down tab in the Connection Experience (Image 1).

A filter on your source is optional. It relies on a source specific syntax for filtering out records from your source target. The filter can reference execution parameters.

This is only available if using a table, not a query. For queries, include the filter in the query itself.

Conditional filters

For REST API, SOAP 1.2, Kafka Topic, Platform Event, and Parquet sources, there is a "Conditional" option for source filters in the Connections UI.

Once selected you will be able to define the conditions upon which data is pulled into your source via the filter. After data is pulled from the source, new conditional UI filters down the set of returned records to ones that match the defined conditions.

• Multiple Conditions can be added to a single data sync by using the AND/OR and +Rule buttons.

• You are able to group your Rules into a rule set by using the +Ruleset button.

• The left-most drop down is used to select either a source or a target column as defined in your Source and Destination tabs.

• The centre drop-down is used to select from the following options:

  • =

  • !=

  • Contains

  • Is Null

  • Is Not Null

• The right-most drop-down can either be used for a plain value (ex: text, numerical, etc.) This will adjust based on the column data type picked in the left-most drop down. For example, if in the source schema the column is a date, then it renders a date picker.

For example, the below condition would only bring in records where the source column Employee Status isn't null (Image 2).

Source filter examples

Example 1: Use a filter to sync only source records with [net worth] > 10000 (Image 3).

Example 2: Use a filter to sync only source records with a status like "Complete" (Image 4).

Target filters

A target destination filter is optional. It relies on a source specific syntax for filtering out records from your target. The filter can reference execution parameters.

Filter examples

Example 1: Filter only target records where the Assignee is Null (Image 5).

Example 2:Filter only target records where the Override ID isn't Null (Image 6).

Example 3:Filter only target records from a specific source (Image 7).

Overview

You can use any task/job scheduling application to automatically run data syncs on a schedule. This page describes how to do this using Windows Task Scheduler.

Set up a task in Windows Task Scheduler

You can set Scheduler Data synchronization commands to run automatically based on your data synchronization requirements, such as certain date/times/intervals. You can save the CLI command used to execute the data synchronization as a script in PowerShell, Batch or a Command file.

Here's an example of how to schedule the CLI with Windows Task Scheduler:

1. Launch Windows Task Scheduler

2. Create a folder to contain the CLI jobs (options)

3. Right click on CLI job folder

4. Select Create Task

5. On the General tab, enter the Name of the job you want to schedule

6. Click on the Trigger Tab

7. Click New and set your schedule preferences

8. Click the Action Tab

9. Click New button

10. Click Browse and navigate to the folder that contains the data sync scheduling script for execution

11. Copy and Paste the Start In (optional) filed the path for your Cinchy CLI folder

Sample action script

SET server="URL" SET user="UserID" SET password="EncryptedPassword" SET tempdir="Error Log Folder" SET file="File Path for Source"

dotnet Cinchy.Connections.CLI.dll syncdata -s %server% -u %user% -p %password% -m "ModelName" -d %tempdir% -f "Feed" -v "filePath":%file%

Overview

This example will take you through the creation and execution of a batch data sync where data will be loaded into the Cinchy via a CSV. In this example, we will be loading information into the People table in Cinchy. This is a self-contained example you can recreate in any Cinchy environment without dependencies.

Use Case: You have historically maintained a record of all your employees in a spreadsheet. Knowing that this significantly hinders your data and data management capabilities, you want to sync your file into Cinchy. Once synced, you can manage your employee information through the Cinchy data browser, instead of through a data silo.

Sample files and code

This section contains:

• The People Table XML schema.

• A sample source CSV data file to load into Cinchy.

Cinchy Table XML

To create the People table used in this example, you can use the below is the XML. You can also create the table manually, as shown in the section below.

<?xml version="1.0" encoding="utf-16"?>
<Model xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="People Model" version="1.0.0" xmlns="http://www.cinchy.co">
  <Schema>
    <Tables>
      <Table name="People" domain="Sandbox" guid="d5e6409c-471e-4a19-ba9c-df6ff2b606aa" icon="fa fa-table" iconColour="#002060">
        <Columns>
          <Text name="Name" guid="d37935e7-0ebb-4267-ad10-758067221951" allowLinking="false" />
          <Text name="Title" guid="afebfe66-1d59-445d-9373-0f8bc2a5e804" allowLinking="false" />
          <Text name="Company" guid="aaa5aeb5-4be2-4dc7-89a6-2237ae5b9c74" allowLinking="false" />
        </Columns>
        <UniqueConstraints />
      </Table>
    </Tables>
  </Schema>
</Model>

Manual table creation

1. Log in to your Cinchy platform.

2. From under My Network, click the create button.

3. Select Table.

4. Select From Scratch.

5. Create a table with the following properties (Image 1):

1. Select Columns in the left hand navigation to create the columns for the table.

2. Select the "Click Here to Add" button and add the following columns:

1. Select Save to save your table.

Data file

You can download the sample CSV file used in this example below.

The source file contains the following information which will sync into the target Cinchy table (Image 2).

As we can see, the file has the following columns:

• First Name

• Last Name

• Email Address

• Title

• Company

The People table created only has the following columns:

• Name

• Title

• Company

When syncing the data from the source (CSV file) to the target (Cinchy People table), the batch data sync must consider the following:

• The first and last name from the source must merge into one column in the target (Name).

• The email address from the sources isn't a column in the target, so this column won't sync into the target.

• The title column will be an exact match from source to target.

• The company column will also be an exact match from source to target.

Create the data sync

You have two options when you create a data sync in Cinchy:

1. You can input all of your necessary information through the intuitive Connections UI. Once saved, all of this data is uploaded as an XML into the Data Sync configurations table.

2. You can bypass the UI and upload your XML config directly into the Data Sync configuration table.

This example will walk you through option one.

Use the Connections UI

1. Within your Cinchy platform, navigate to the Connections Experience (Image 3).

1. In the Info tab, input the name of your data sync. This example uses "Contact Import" (Image 4).

1. Since this is a local file upload, we also need to set a Parameter. This value will be referenced in the "path" value of the Load Metadata box in step 5. For this example, we will set it to filepath (Image 5).

1. Navigate to the Source tab. This example uses the .CSV file you downloaded at the beginning of this example as our source.

2. Under Select a Source, select Delimited File (Image 6).

1. The "Load Metadata" box will appear; this is where you will define some important values about your source needed for the data sync to execute. Using the below table as your guide, fill in your metadata parameters (Image 7):

1. Click Load.

2. In the Available Columns pop-up, select all of the columns that you want to import from the CSV. For this example, we will select them all (noting, however, that we will only map a few of them later) (Image 8).

1. Click Load.

2. Once you load your source, the schema section of the page will auto populate with the columns that you selected in step 7 (Image 9). Review the schema to ensure it has the correct Name and Data Type. You may also choose to set any Aliases or add a Description.

1. Navigate to the Destination tab and select Cinchy Table from the drop down.

2. In the Load Metadata pop-up, input the Domain and Table name for your destination. This example uses the Sandbox domain and the People table (Image 10).

3. Select Load Metadata.

1. Select the columns that you wish to use in your data sync (Image 11). These will be the columns that your source syncs to. This example uses the Name, Title, and Company columns. Note that you will have many Cinchy system table available to use as well. Click Load.

1. The Connections experience will attempt to automatically map your source and destination columns based on matching names. In the below screenshot, it matched the "Company" and "Title" columns (Image 12). The "Name" target column isn't an exact match for any of the source columns, so you must match that one manually.

1. Select "First Name" from the Source Column drop down to finish mapping our data sync (Image 13).

1. Navigate to the Sync Actions tab. Sync actions have two options: Full File and Delta. In this example, select Full File.

1. Set the following parameters (Image 14):

1. Navigate to the Permissions tab. Here you will define your group access controls for your data sync (Image 15). You can set this how you like. This example gives all users access to Execute, Write, and Read our sync.

1. Navigate to the Jobs tab. Here you will see a record of all successful or failed jobs for this data sync.

2. Select "Start a Job" (Image 16).

1. Load your sample .CSV file in the pop-up window (Image 17).

1. The job will commence. The Execution window that pops up will help you to verify that your data sync is progressing (Image 18).

1. Navigate to your destination table to ensure that your data populated correctly (Image 19).

Use a data sync XML

Instead of the Connections UI, you can also set up a data sync by uploading a formatted XML into the Data Sync Configs table within Cinchy.

We recommend only doing so once you have an understanding of how data syncs work. Not all sources/targets follow the same XML pattern.

The example below is the completed batch data sync configuration. Review the XML and then refer to the filled XML example.

Blank XML example

The below XML shows a blank data sync for a Delimited File source to a Cinchy Table target.

<?xml version="1.0" encoding="utf-16"?>
<BatchDataSyncConfig name="" version="1.0.0" xmlns="http://www.cinchy.co">
    <Parameters>
        <Parameter name=""/>
    </Parameters>
    <DelimitedDataSource source="" path="" delimiter="" textQualifier="" headerRowsToIgnore="" encoding="" useHeaderRecord="">
        <Schema>
            <Column name="" dataType="" trimWhitespace="true" isMandatory="false" validateData="false"/>
        </Schema>
    </DelimitedDataSource>
    <CinchyTableTarget reconcileData="true" domain="" table="" suppressDuplicateErrors="false" degreeOfParallelism="1">
        <ColumnMappings>
            <ColumnMapping sourceColumn="" targetColumn=""/>
        </ColumnMappings>
        <SyncKey readonly="false">
            <SyncKeyColumnReference name=""/>
        </SyncKey>
        <NewRecordBehaviour type=""/>
        <DroppedRecordBehaviour type=""/>
        <ChangedRecordBehaviour type=""/>
        <PostSyncScripts/>
    </CinchyTableTarget>
</BatchDataSyncConfig>

Filled XML example

The below filled XML example matches the Connections UI configuration made in Use the Connections UI. You can review the parameters used in the table below.

<?xml version="1.0" encoding="utf-16"?>
<BatchDataSyncConfig name="Contact Import" version="1.0.0" xmlns="http://www.cinchy.co">
    <Parameters>
        <Parameter name="parameter"/>
    </Parameters>
    <DelimitedDataSource source="PATH" path="@parameter" delimiter="," textQualifier="&quot;" headerRowsToIgnore="1" encoding="UTF8" useHeaderRecord="false">
        <Schema>
            <Column name="First Name" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Last Name" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Email Address" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Title" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Company" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
        </Schema>
    </DelimitedDataSource>
    <CinchyTableTarget reconcileData="true" domain="sandbox" table="People" suppressDuplicateErrors="false" degreeOfParallelism="1">
        <ColumnMappings>
            <ColumnMapping sourceColumn="Company" targetColumn="Company"/>
            <ColumnMapping sourceColumn="Title" targetColumn="Title"/>
            <ColumnMapping sourceColumn="First Name" targetColumn="Name"/>
        </ColumnMappings>
        <SyncKey readonly="false">
            <SyncKeyColumnReference name="Name"/>
        </SyncKey>
        <NewRecordBehaviour type="INSERT"/>
        <DroppedRecordBehaviour type="DELETE"/>
        <ChangedRecordBehaviour type="UPDATE"/>
        <PostSyncScripts/>
    </CinchyTableTarget>
</BatchDataSyncConfig>

Using the data sync XML

1. Once you have completed your Data Sync XML, navigate to the Data Sync Configurations table in Cinchy (Image 20).

1. In a new row, paste the Data Sync XML into the Config XML column (Image 21).

2. Define your group permissions in the applicable columns. This example gives all Users the Admin Access*.*

1. To execute your Data Sync you will use the CLI. If you don't have this downloaded, refer to the CLI commands list page.

2. In this example we will be using the following Data Sync Commands, however, for the full list of commands click here.
3. Launch PowerShell and navigate to the Cinchy CLI directory.

4. Enter and execute the following into PowerShell:

.\Cinchy.CLI.exe syncdata -s "pilot.cinchy.co/Training/Cinchy/" -u "admin" -p "DESuEGqmx55yl2PYxa4ncc+5+bLkoVIFpgs0Lq6hkcU=" -f "Contact Import"

1. Once executed, navigate to your destination table to validate that your data synced correctly (Image 22).

Appendix

Password encryption

To encrypt a password using PowerShell, complete the following:

1. Launch PowerShell and navigate to the Cinchy CLI directory (note, you can always type PowerShell in the windows explore path for the Cinchy CLI directory)

2. Enter the following into PowerShell .\Cinchy.CLI.exe encrypt -t "password"

3. Hit enter to execute the command

4. Copy the password so it's accessible at batch execution time

Execution Log table

The Execution Log table is a system table in Cinchy that logs the outputs of all data syncs (Image 23). You can always review the entries in this table for information on the progression of your syncs.

Execution Error table

The Execution Errors table is a system table in Cinchy that logs any errors that may occur in a data sync (Image 24). Any data sync errors log to the temp directory outlined in the data sync execution command. For example, -d "C:\Cinchy\temp".

Overview

This example will take you through the creation and execution of a real-time data sync where data will be synced between two Cinchy tables based on real-time changes.

Use case

Your People table captures a view of various personnel information. Any time a new hire is added to the table, you want that information to be immediately synced into the New Employees table. We can solve this use case using the Cinchy Change Data Capture (CDC) function on our tables. This helps you to better keep track of all incoming people within your company.

Sample files and code

This section has steps on how to:

• Create the People table.

• Create the New Employees table.

People table creation

1. Login to your Cinchy platform.

2. From under My Network, click the Create > Standard Table > From Scratch.

3. Create a table with the following properties (Image 1):

   Table Details	Values

1. Click Columns in the left hand navigation to create the columns for the table.

2. Click the "Click Here to Add" button and add the following columns:
3. Select Change Notifications in the left hand navigation and select Publish Change Notifications.

4. Select Save to save your table.

New Employees table creation

1. Within the Cinchy platform, from under My Network, select Create

2. Select Table

3. Select From Scratch

4. Create a table with the following properties (Image 2):

1. Select Columns in the left hand navigation to create the columns for the table.

2. Select the "Click Here to Add" button and add the following columns:

1. Click the Save button to save your table.

Create the data sync

You have two options when you create a data sync in Cinchy.

1. You can input all the necessary information through the Connections UI. Once saved, this data uploads as an XML file into the Data Sync configurations table.

2. You can bypass the UI and upload your XML config directly into the Data Sync configuration table yourself.

This example will walk you through both options.

Use the Connections UI

1. Within your Cinchy platform, navigate to the Connections Experience (Image 3).

1. In the Info tab, input the name of your data sync. This example uses "New Hires" (Image 4).

Configure the source

1. Navigate to the Source tab.

2. Under Select a Source, select Cinchy Event Broker (Image 5).

3. Under Listener, select the People table.

Configure the destination

1. Navigate to the Destination tab and select Cinchy Table from the drop down (Image 6).

2. In the Connection section, input the Domain and Table name for your destination. This example uses the Sandbox domain and the People table.

3. Click Load.

1. Select the columns that you wish to use in your data sync (Image 8). These will be the columns that your source syncs to your target. This example uses the Name and Title columns. You also have many Cinchy system table available to use.

2. Click Load.

1. The Connections experience will try to automatically map your source and destination columns based on matching names. In the below screenshot, it has been able to correctly match the Name and Title columns (Image 8).

Sync actions

1. Navigate to the Sync Actions tab. Two options are available for data syncs: Full File and Delta. For this example, select Full File.

Full load processing means that the entire amount of data is imported iteratively the first time a data source is loaded into the data studio. Delta processing means loading the data incrementally (loading the source data at specific pre-established intervals).

1. Set the following parameters (Image 9):
2. Navigate to the Permissions tab. Here you will define your group access controls for your data sync. You can set this how you like. This example gives all users access to Execute, Write, and Read our sync (Image 10).

Any groups given Admin Access will have the ability to Execute, Write, and Read the data sync.

<figure><img src="../../.gitbook/assets/image (346).png" alt=""><figcaption><p>Image 10: Sync Permissions</p></figcaption></figure>

1. Click Save.

2. Navigate to the Cinchy Listener Config table and validate your configuration. Make sure it's set to Enabled. Your real-time data sync should now be listening to your People table ready to push updates to your New Employees table.

3. Test your data sync by adding a new row to your People table. Ensure that the data is then updated across to the New Employees table (Images 11 & 12).

Use a data sync XML

Instead of using the Connections UI, you can also set up a data sync by uploading a correctly formatted XML into the Data Sync Configs table within Cinchy.

We only recommend doing so once you have a good understanding of how data syncs work. Not all sources and targets follow the same XML pattern.

Blank XML example

The below XML shows what a blank data sync could look like for our Cinchy Event Broker/CDC to Cinchy Table real-time sync with full file synchronization.

<?xml version="1.0" encoding="utf-16"?>
<BatchDataSyncConfig name="" version="1.0.0" xmlns="http://www.cinchy.co">
    <CinchyEventBrokerDataSource runQuery="false">
        <Schema>
            <Column name="" dataType="" trimWhitespace="true" isMandatory="false" validateData="false"/>
        </Schema>
    </CinchyEventBrokerDataSource>
    <CinchyTableTarget reconcileData="true" domain="" table="" suppressDuplicateErrors="false" degreeOfParallelism="1">
        <ColumnMappings>
            <ColumnMapping sourceColumn="" targetColumn=""/>
        </ColumnMappings>
        <SyncKey readonly="false">
            <SyncKeyColumnReference name=""/>
        </SyncKey>
        <NewRecordBehaviour type=""/>
        <DroppedRecordBehaviour type=""/>
        <ChangedRecordBehaviour type=""/>
        <PostSyncScripts/>
    </CinchyTableTarget>
</BatchDataSyncConfig>

Filled XML example

The below filled XML example matches the Connections UI configuration made in Use the Connections UI . You can review the parameters used in the table below.

<?xml version="1.0" encoding="utf-16"?>
<BatchDataSyncConfig name="New Hires" version="1.0.0" xmlns="http://www.cinchy.co">
    <CinchyEventBrokerDataSource runQuery="false">
        <Schema>
            <Column name="Name" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
            <Column name="Title" dataType="Text" trimWhitespace="true" isMandatory="false" validateData="false"/>
        </Schema>
    </CinchyEventBrokerDataSource>
    <CinchyTableTarget reconcileData="true" domain="sandbox" table="New Employees" suppressDuplicateErrors="false" degreeOfParallelism="1">
        <ColumnMappings>
            <ColumnMapping sourceColumn="Name" targetColumn="Name"/>
            <ColumnMapping sourceColumn="Title" targetColumn="Title"/>
        </ColumnMappings>
        <SyncKey readonly="false">
            <SyncKeyColumnReference name="Name"/>
        </SyncKey>
        <NewRecordBehaviour type="INSERT"/>
        <DroppedRecordBehaviour type="DELETE"/>
        <ChangedRecordBehaviour type="UPDATE"/>
        <PostSyncScripts/>
    </CinchyTableTarget>
</BatchDataSyncConfig>

Use the data sync XML

1. Once you have completed your Data Sync XML, navigate to the Data Sync Configurations table in Cinchy (Image 13).

1. In a new row, paste the Data Sync XML into the Config XML column.

2. Define your group permissions in the applicable columns. This example gives All Users the Admin Access.

1. Navigate to the Cinchy Listener Config table and set up your configuration. Ensure it's set to Enabled. Your real-time data sync should now be listening to your People table ready to push updates to your New Employees table.

2. To execute your Data Sync you will use the CLI. If you don't have this downloaded, refer to the CLI commands list page

3. In this example we will be using the following Data Sync Commands, however, for the full list of commands click here.

1. Launch PowerShell and navigate to the Cinchy CLI directory.

2. Enter and execute the following into PowerShell:

.\Cinchy.CLI.exe syncdata -s "pilot.cinchy.co/Training/Cinchy/" -u "admin" -p "DESuEGqmx55yl2PYxa4ncc+5+bLkoVIFpgs0Lq6hkcU=" -f "Contact Import"

1. Test your data sync by adding a new row to your People table. Ensure that the data is then updated across to the New Employees table (Images 14 & 15).

Appendix

Manually set the listener config

This section provides information on how to manually set up the listener config using the Listener Config table. While this example shows how to configure the sync using the Cinchy Event Broker/CDC, Cinchy also supports other Event Stream Sources. For more information, see the supported real-time sync stream sources.

1. Navigate to the Listener Config table in Cinchy (Image 16).

1. In a new row, add in your listener config data using the below table as a guide:

                                                                                |

Topic JSON example

{
  "tableGuid": "3daba5da-5e07-4d35-8d7c-d451a2c9068e",
  "fields": [
    {
      "column": "Name"
    },
    {
      "column": "Title"
    }
  ]
}

Password Encryption

Before executing the data sync command, encrypt the password using PowerShell.

To encrypt a password using PowerShell, complete the following:

1. Launch PowerShell and navigate to the Cinchy CLI directory (note, you can always type PowerShell in the windows explore path for the Cinchy CLI directory).

2. Enter the following into PowerShell .\Cinchy.CLI.exe encrypt -t "password".

3. Hit enter to execute the command.

4. Copy the password so you have it accessible at batch execution time.

Execution logs

The Execution Log table is a system table in Cinchy that logs the outputs of all data syncs. You can always review the entries in this table for information on the progression of your syncs (Image 17).

Execution errors

The Execution Errors table is a system table in Cinchy that logs any errors that may occur in a data sync (Image 18). Any data sync errors will also be logged in the temp directory outlined in the data sync execution command (-d "C:\Cinchy\temp")

Pagination

When syncing a Data Source, you may have the option to add in extra configuration sections, such as an Pagination, under the "Add a Section" drop down tab in the Connection Experience (Image 1).

Cinchy has two types of pagination available (Image 2):

• Cursor: The cursor is a key parameter in this type of pagination. You receive a variable named Cursor along with the response. It's a pointer that points at a particular item that you must send with a request. The server then uses the cursor to seek the other set of items. Cinchy recommends using cursor-based pagination when dealing with a real-time data set.

• Offset: Offset-based pagination is for parameters with a specific limit (the number of results) and offset (the number of records you need to skip). Cinchy recommends using offset-based pagination for static data.

Cursor pagination

To set up cursor pagination, fill in the following parameters (Image 3):

• Type: Select Cursor

• Next Page URL JSON Path: This is the JSON Path within the response to the URL of the next page

• Cursor Key: This is the key used in the query string to specify the cursor value. This is only required if the cursor returned isn't a fully qualified URL.

Offset pagination

To set up offset pagination, fill in the following parameters (Image 4):

• Type: Select Offset

• Limit Key: The key used in the query string to specify the limit

• Limit: The desired page size

• Offset By: The offset type that the API uses for pagination. This will be either Record Number or Page Number.

• Offset Key: The key used in the query string to specify the offset

• Initial Offset: The starting offset