Home Search

DriveWorks Pro 22
CPQ Data Synchronization

Send Feedback

CPQ Data Synchronization

The CPQ Data Synchronization Project Template works in combination with the Scheduler DriveApp to synchronize data between a Third Party system and the CPQ DriveApp database.

This gives an easy to setup and manage integration method, allowing data from a Third Party system to be used inside DriveWorks.

The template can be used to synchronize CPQ Accounts, Contacts and Items (standard and custom).

Synchronization can be performed on a schedule using the DriveWorks Autopilot Schedule Connector. Data can also be synced back to your Third Party software when a record is either added or edited in the CPQ DriveApp.

Any existing DriveWorks Integration method can be used with CPQ Data Synchronization.

Implementing the CPQ Data Synchronization Project Template

To fully implement the CPQ Data Synchronization Project Template the following should exist in the same Group:

  • A CPQ DriveApp is required.
  • A CPQ Custom Fields Project may be required, when synchronizing fields additional to the default fields included for each record type.
  • A Scheduler DriveApp will be required to schedule the synchronization.

    The record type being synchronized (for example Contacts) should not have any existing records.

    If there is pre-existing data then the synchronization will not work.

  • An understanding of the Third Party database being synchronized is also required.

    This includes knowing:

    • Connection details
    • Security (login) details
    • Table names and Fields within each required Table.

1. Create a CPQ Data Synchronization Project

In DriveWorks Administrator open the Group that contains the CPQ DriveApp, and:

  1. Click the Create Project button (if DriveWorks Today or DriveWorks Help is selected from the Task Explorer).

    Or

    Click the Projects button from the Title Bar and select Create New Project and click Next.

  2. From the Templates section select the CPQ Data Synchronization template, click Next.
  3. Browse to a Location to store the Project.
  4. The Name field will populate with the folder selected above, if this is suitable click Finish.

    Or

    Replace the Name with a suitable name for the CPQ Data Synchronization Project, click Finish.

2. Edit the Calculation Tables in the CPQ Data Synchronization Project

With the CPQ Data Synchronization Project open in DriveWorks Administrator:

  1. Select Stage 3. Data and Rules, Calculation Tables from the Task Explorer.
  2. Select the Calculation Table for the Record Type to be synchronized.
  3. Edit the SyncFieldName column for each row with the value "CPQ" in the CPQFieldType column that requires synchronizing.

    See Calculation Table Field Headers above to understand what value is expected in the SyncFieldName column, for each row.

    Also each Calculation Table has a minimum field requirement in order to create a new record.

    Ensure all Minimum required fields to create a new record have a value.

  4. Add a Row for each Custom Field, that requires to be synchronized.

    Each Calculation Table has two placeholders (Value1 and Value2 in the CPQFieldName column), that can be utilized. These can be removed if not required.

    Edit the following values in each of the new rows:

    • CPQFieldName

      The name of the Custom Field (must match the column name in your Custom Fields database table).

    • CPQFieldType

      Should be set to "Custom".

    • SyncFieldName

      The name of the equivalent field/column in Third Party database being synchronized.

    For example (additional columns not included for clarity):

    CPQFieldNameCPQFieldTypeSyncFieldName
    14"Manufacturer""Custom""Vendor"

About the Calculation Tables in the CPQ Data Synchronization Project

The Calculation Tables hold the data to be synchronized with the Third Party database.

The Table names reflect each Record Type that can be synchronized, use:

  • SyncAccounts to synchronize Accounts
  • SyncContacts to synchronize Contacts
  • SyncItems to synchronize Items

Calculation Table Field Headers

Each Calculation Table contains the following field headers:

  • ID

    Row ID

  • CPQFieldName

    CPQ Database header name. Each subsequent row stores the name of each field in the CPQ DriveApp to be synchronized.

  • CPQValue

    Value passed to the Sync project from CPQ. Each subsequent row contains a rule that retrieves the current value from the CPQ DriveApp.

  • CPQFieldType

    Specifies if a field is a standard CPQ field or a Custom Field.

  • SyncFieldName

    Third Party database header name, or the value to be set in the CPQ DriveApp. Each subsequent row stores the name of each field in the Third Party database to be synchronized.

  • SyncValue

    Value to be sent to the Third Party database. This column allows for the CPQ Value to be manipulated before sending to the Third Party database.

  • SyncReturnValue

    Value passed to the CPQ Data Synchronization project from the Third Party Database.

  • SQLSelect

    Header Values to be used in SQL Select statement.

  • ExcludeFromInsert

    Used to exclude values from being synchronized on Add/Edit.

Minimum required fields to create a new record

Each Calculation Table has its own requirements ***

SyncAccountsSyncContactsSyncItemsDescription
SyncFK SyncFK SyncFK The name of the field, in the Third Party database, that contains the Id of the record to be synchronized.
Enabled Enabled Enabled Must be set to TRUE (enabled) or FALSE (disabled).
TeamNameTeamNameThe Team for the record to be owned by. This ensures the correct people have access to the record. If team names in the CPQ DriveApp are different to the Third Party system Team names build a rule in this cell to accommodate this.
Name Name The Account or Item name. If the field name is the same in both databases you can use a cell reference to the CPQ Field Name (for example [3L]).
RelationshipType This CPQ value can only evaluate to "Customer" or "Distributor". If left blank a default Relationship Type of Customer will be used.
FirstName The first name of the Contact to be synchronized. If the Third Party system uses a full name, you may need to split the first name from the original value.
EmailAddress1 The Contact email address.
SKU The Stock Keeping Unit of the Item. If the Third Party system does not use SKU (or equivalent), create one using DriveWorks rules for use inside the CPQ DriveApp.
Price The end user unit price of the Item to be synchronized. For Custom Items this can be left blank.

Pricing Currency Precision in the CPQ DriveApp must be correctly set or precision will be lost.

Type This should be set to either "Custom" or "Standard".

3. Edit the Variables in the CPQ Data Synchronization Project

Each record type has a Variables Folder, as in the image below:

Image showing the Variable Categories for each Record Type

Only some of the variables within these folders require to be edited, so the correct data is Selected/Inserted/Updated from the Third Party system being synchronized with the CPQ DriveApp.

For each Variable below replace the prefix [RecordType] with the Record Type being synchronized. This will be one of the following:

  • Accounts
  • Contacts
  • Items

For example:

[RecordType]GetSyncDataFromTimestamp

Will be:

AccountsGetSyncDataFromTimestamp

  1. Select the Variable - [RecordType]GetSyncDataFromTimestamp, click Build.

    This is located in the Schedule sub-folder of the Record Type being synchronized.

    This Variable will pass a timestamp value to the CPQ DriveApp.

    This timestamp value can then be used to select data from the Third Party system that has been updated since the last time the synchronization was run.

    It is used inside the [RecordType]SQLSelect Variable.

  2. Select the Variable - [RecordType]ConnectionString, click Build.

    This is located in the Connection sub-folder of the Record Type being synchronized.

    This Variable contains the connection string details for the Third Party database being synchronized.

    This may not be required dependent on the integration method used.

    The connection string requires a Server and Database name. The default rule will be:

    "Server=;Database=;"

    If the Third Party database is SQL based this should be edited to be:

    "Server=<MachineName\SQLServer>;Database=<ThirdPartyDatabase>"

    To obtain the connection string:

    From the Rule Builder, activate the QueryData wizard, select the browse button at the end of the SQL String field, and run through the options to find and connect to the Server.

    Copy the string from the SQL String field, cancel the QueryData wizard, and paste the string between the " " in the rule editor (overwriting the default rule).

  3. Select the Variable - [RecordType]SQLSelect, click Build.

    This is located in the Schedule sub-folder of the Record Type being synchronized.

    This Variable is used to retrieve data from the Third Party database. If using a different method of integration, modify this Variable accordingly.

  4. Select the Variable - [RecordType]SQLUsername, click Build.

    This is located in the Connection sub-folder of the Record Type being synchronized.

    Enter the user name to the Third Party database. Click OK.

    This may not be required dependent on the integration method used.

  5. Select the Variable - [RecordType]SQLPassword, click Build.

    This is located in the Connection sub-folder of the Record Type being synchronized.

    Enter the password to the Third Party database. Click OK.

    This may not be required dependent on the integration method used.

  6. Select the Variable - [RecordType]AddEditSQLSelect, click Build.

    This is located in the AddEdit sub-folder of the Record Type being synchronized.

    Replace <TABLE> with the name of the Table from the Third Party database.

  7. Select the Variable - [RecordType]AddEditSQLUpdate, click Build.

    This is located in the AddEdit sub-folder of the Record Type being synchronized.

    Replace <TABLE> with the name of the Table from the Third Party database.

  8. Select the Variable - [RecordType]AddEditSQLInsert, click Build.

    This is located in the AddEdit sub-folder of the Record Type being synchronized.

    Replace <TABLE> with the name of the Table from the Third Party database.

4. Apply Sync Settings in the CPQ DriveApp

The CPQ DriveApp needs the name of this Data Synchronization Project passing into the corresponding Record types to be synchronized.

With the Group open in DriveWorks Administrator:

  1. From Stage 3: DriveApps, elect the CPQ DriveApp the Data Synchronization Project is to be applied to.
  2. Click the Settings button (top right of the header).
  3. Select CPQ Settings from the Settings menu, and select the Sync tab.
  4. For each Record type (Accounts, Contacts and Items) that requires synchronizing, select the applicable Data Synchronization Project from the drop down.
  5. Check the box (if applicable) to enable each Record type to be synchronized on Add/Edit.

5. Setup a Schedule to Run the Data Synchronization

Running Data Synchronization on a schedule allows the data to be synchronized at a set time or at a set time interval.

The purpose of this type of synchronization is to import or update the data, from your Third Party system into the CPQ DriveApp, that has been modified since the previous synchronization.

The way this is performed in DriveWorks is from a Scheduler DriveApp running a CPQ Data Sync Job, and storing a Timestamp value.

The Timestamp value is used to retrieve the data that needs to be updated inside CPQ the next time the synchronization is executed.

Add a CPQ Data Sync Job to the Scheduler DriveApp.

This step defines the entities (records) that will be synchronized and allows notifications about the Job to be emailed.

If a Scheduler DriveApp does not exist in the Group, see Add A DriveApp.

  1. Add a Job to the Scheduler DriveApp, see Adding Jobs.

    Select CPQ from the Job Category drop down.

    Select CPQ Data Sync from the Job drop down.

  2. Once the CPQ Data Sync Job is selected, complete the Email Details and Job Inputs sections and click Save

    See CPQ Data Sync for more information.

Add a Schedule Connector to DriveWorks Autopilot.

This step applies the timings for the Data Synchronizing to occur.

From DriveWorks Autopilot, open the Group that should now contain the following:

  • The CPQ DriveApp
  • The Data Synchronization Project(s)
  • The Scheduler DriveApp

Follow the steps below to add and setup the required schedule:

  1. Select Connectors from the Task list and click New

    See Add a New Connector for more information.

  2. From the Schedule Settings apply the frequency to run the synchronization, and apply the Actions.

    See Schedule Connector for more information.

© DriveWorks Ltd 2024 - All rights reserved.
Privacy Policy | Terms & Conditions