ClientSpace Salesforce API Integration v1.2

The Salesforce API Integration is a PEO process that maintains data synchronization between a Client’s ClientSpace installation and their respective Salesforce system. 

During the life cycle of a deal, either Salesforce or ClientSpace is responsible for the data. Typically, Salesforce owns the data in the Lead Discovery cycle. As changes are made in Salesforce, ClientSpace is updated with those changes. Once the deal has reached a certain level, ClientSpace will no longer accept changes from Salesforce but will keep Salesforce up to date with certain data as changes are made in ClientSpace. 

Although this is a PEO process, custom fields in both ClientSpace and Salesforce can be Exported/Imported. Configuration handles the specific fields to be managed. The exchange of data between the systems is highly configurable.  Data elements are mapped at the field level for each system using standard ClientSpace Import Map Headers and Details. In addition to using Import Map Details to describe the relationships between ClientSpace and Salesforce data objects, three Custom stored procedures can be created to customize when data should be exported to Salesforce (csupdateproc), the source of the ClientSpace data to be exported (csworkspacedataproc), and which ClientSpace field changes should cause an export to occur (csavailableupdates).

When ClientSpace dataforms (gen_) are updated from Salesforce, they are saved via Dataform Pipeline Logic to allow business rules to be executed, and template tasks and email templates to be triggered.

ClientSpace Exports data to Salesforce before Importing from Salesforce, unless Export is disabled in the configuration.

Data Relationships

The following diagram shows the relationships between Salesforce and ClientSpace objects. Only one Opportunity can be mapped to ClientSpace, even though there can be multiple Opportunities associated with an Account. Use the Opportunity “RecordType.Name" to identify the correct Opportunity (see ClientSpace Configuration section).

Specifications

The capabilities of the process are as follows:

• Exports to Salesforce affect the following Salesforce Objects:
  • Opportunity (updates existing Opportunities in Salesforce, will not create new Opportunities)
  • Account (updates existing Accounts in Salesforce, will not create new Accounts)
  • Contact (updates existing Contacts or creates new Contacts under an Account)

ClientSpace tables are mapped to Salesforce:

• • CS_Organization => SF_Opportunity => SF_Account
    • An Organization (or Org Other Info) change in ClientSpace will result in an Opportunity and an Account update in Salesforce
    • Only one Opportunity can be related to an Account.  The Salesforce system may contain more than one Opportunity for the Account, but only one Opportunity can be updated.  This is the Client’s responsibility to manage
  • CS_Client Master => SF_Opportunity => SF_Account
    • A ClientMaster change in ClientSpace will result in an Opportunity and an Account update in Salesforce
  • CS_Contact => SF_Contact
    • A Contact (or Contact Other Info) change in ClientSpace will result in a Contact update in Salesforce
• Imports from Salesforce affect the following ClientSpace tables and dataforms.  New Workspaces and records can be created by the process:
  • tblOrganization
  • gen_OrgOtherInfo
  • gen_ClientMaster
  • gen_ClientLocation
  • tblContact
  • gen_ContactOtherInfo
  • any 'gen_' single form (via configuration)

Only Salesforce Opportunities, Accounts, and Contacts data can be imported into ClientSpace.

Salesforce Objects are mapped to ClientSpace:

• SF_Opportunity=>_SF_Account=>SF_Contact to ClientSpace tables
• Opportunity (retrieved from Work Queue) and Account (retrieved from related Opportunity) and Contacts (related to the Account) map to the above ClientSpace tables

• Exporting Data
  • Exports Allowed
    • A flag is used to determine whether or not ClientSpace will export data to Salesforce.  This flag is set via the API Configuration form.  It cannot be changed programmatically
  • Organization
    • update Opportunity and/or Account based on mappings (Org and Org Other Info)
  • Client
    • update Opportunity and/or Account based on mappings (Client Master)
    • set Opportunity.StageName based on ClientMaster.Status
  • Contact
    • update Contact based on mappings (Contact and Contact Other Info)
    • create a new contact based on mappings (Contact and Contact Other Info)
    • create converts the Org Assigned To to a Salesforce User or Salesforce Account Owner

• Importing Data
  • Updates Allowed
    • An internal flag _CSUpdateAllowed is used to determine if Salesforce data is allowed to be imported into ClientSpace. This is a configurable setting that will change programmatically depending on the state of the Client Master Status
  • Work Queue
    • Object must be named "Work_Queue__c"
    • must contain "OpportunityID__c" and "Complete__c" fields
    • "Complete__c" must be set to "false" when the record is created in Salesforce
    • Duplicate Opportunities in multiple Work Queue records will be ignored
    • If the Import is successful, the Work Queue "OpportunityID__c" will be set to Empty and "Complete__c" will be set to "true"
  • Organization
    • Creates new Org or updates an existing Org
    • General Notes marked "Updated/Created via SF API..."
    • Org ImportID will be set to SF Account ID
    • Only updates existing Org data with SF data if _CSUpdateAllowed
    • Org is saved via the database, no biz logic executed
  • Org Other Info
    • Creates new OOI or updates existing OOI
    • All fields and values are retrieved from the current installation (custom fields are available)
    • Only updates existing OOI data with SF data if _CSUpdateAllowed (or when creating a new record)
    • OOI is saved via the database, no biz logic executed
  • Client Master
    • Creates new CM and workspace if associated Org has no workspace (must have 'client' template configured for this to function)
      • Updates Client Team Sales Admin User after workspace is created (to Org Assigned To)
      • The new CM will be saved via dataform pipeline "Default"
    • After the new CM is created (or CM exists)
      • fkParentCompanyID is set to SF Account/ParentId associated CS Organization (if exists)
      • All fields and values are retrieved from the current installation (custom fields are available)
      • Adds Org Node for Biz Logic access (similar to a CM save via the UI)
      • Only updates existing CM data with SF data if _CSUpdateAllowed
      • Updated CM is saved via dataform pipeline "SalesforceAPI"
    • Client Location
      • Creates new Client Location or updates existing Client Location
      • If new, Sets LocationName("MAIN"), luLocationType("Main"), SyncToM("P"), SyncToB("P")
      • All fields and values are retrieved from the current installation (custom fields are available)
      • Only updates existing Client Location data with SF data if _CSUpdateAllowed (or when creating a new record)
      • Client Location is saved via dataform pipeline "SalesforceAPI"
    • Contacts
      • Retrieves all Salesforce Contacts associated with SF Account
      • Looks up each contact by CS ImportID
      • Creates new Contact if ImportID not found
      • Always sets IsActive(True), SyncWithOrganizationAddress(false), ImportID(SF Contact ID)
      • Ignores _CSUpdateAllowed (always syncs SF data)
      • Contact is saved via the database, no biz logic executed
    • Contact Other Info
      • Updates or creates COI for each SF Contact (from Contacts above)
      • All fields and values are retrieved from the current installation (custom fields are available)
      • Ignores _CSUpdateAllowed (always syncs SF data)
      • COI is saved via the database, no biz logic executed
    • Other Dataforms
      • Only single forms are allowed
      • Any Dataform mapped (Import Map Header) will be included
      • Iterates through each mapped Dataform
      • Retrieves existing Dataform from Workspace associated with SF Account (CS Organization)
      • Creates dataform if one does not exist in Workspace
      • Dataform is saved via dataform pipeline "SalesforceAPI"
      • Only updates existing dataform data with SF data if _CSUpdateAllowed (or when creating a new record)

Salesforce Configuration

Salesforce must be configured prior to implementing this process. The Salesforce Partner WSDL is used as it is generic across Salesforce systems. The Client’s Salesforce account must support this API. Request the following from the Client:

• API Username
• API Password
• API Integration key
• Partner WSDL Url
  • Sandbox API Url
  • Production API Url
• Salesforce Web Interface login credentials (for testing and configuration)

The Client must create a “Work_Queue__c” object with at least ‘Id’, ‘Opportunity__c’ and ‘Complete__c’ fields.  This object will be populated with records containing Opportunity IDs for Opportunities that will be imported into ClientSpace.  It is up to the Client to determine when a Work Queue record will be created, typically based on a set of rules in Salesforce.

ClientSpace Configuration

There are several ClientSpace mechanisms that must be configured prior to implementing this process.

• API Configuration
  • API Type: Web Service
  • Application Code:
    • ‘SFDCDEV’ will be the sandbox configuration
    • ‘SFDCPROD’ will be the Production configuration
  • Application Name: “Salesforce Integration” (user choice)
  • Username: the username of the API account
  • Password: the password of the API account AND the integration key
  • Description: (user choice)
  • Endpoint: the Url of the API for the Partner WSDL
  • Secondary ID: (not used)
  • Logging Level:
    • 0 None
    • 1 Only logs that the process ran and the date/time begin and end
    • 2 Logs process details and results (for setup and debugging only)
  • Additional Parameters:

Additional Parameters are semi-colon delimited name=value pairs, such as
param1=value1;param2=value2.  NOTE: spaces between parameters are not allowed (e.g. “;<space>param2=” is invalid)

• exporttosf=x
  • "0" = No Export (CS changes will not be exported to Salesforce) (defaults to “0”)
  • "1" = Export to and Import from Salesforce
• updatestage=x
  • "0" = Opportunity Stage will never be updated when exporting
  • "1" = Opportunity Stage will be updated when exporting (defaults to “1”), based on the following translation:

• csworkspacedataproc=x
  • the name of the proc used to export workspace data (defaults to "peo_salesforce_org_s_workspace_by_org")
• csupdateproc=x
  • the name of the proc used to set _CSUpdateAllowed (defaults to "peo_salesforce_s_allow_cs_update"). This determines when ClientSpace will begin ignoring Salesforce
• csavailableupdates=x
  • the name of the proc used to determine which audit records will be exported (defaults to "peo_salesforce_s_available_cs_updates"). These are the triggers from the Field Change Audit table.
• opprecordtypename=x
  • used when retrieving Opportunities from Salesforce
  • if this is specified you must include an import and export map entry for API Path = “Opportunity/RecordType/Name” otherwise a match will not be found
• contactrecordtypename=x
  • used when retrieving Contacts from Salesforce
  • if this is specified you must include an import and export map entry for API Path = “Contact/RecordType/Name” otherwise a match will not be found
• querysize=1000 (must be this value)
• sessionlength=29 (must be this value)
• querybatchsize=-1 (must be this value)

• User
  • Must configure a Salesforce user in ClientSpace (Login ID = 'sfservice')
  • Username: sfservice
  • First: SalesForce
  • Last: Service User
  • Email: PrismHR admin email
  • User Type: External
  • Authentication Type: ClientSpace
  • After Login Goto Workspace: Admin Workspace
  • No other options are required

• Field Change Audit

The Field Change Audit table is used to determine when and what data will be Exported to Salesforce.  Field changes must be resolved to either tblOrganization, gen_ClientMaster or tblContact tables.

• Configure the Table Metadata for the Forms/Fields that will be monitored for an update
• Export Example 1, tblOrganization related:
  • Org Other Info “z_SomeField” is audited in the FCA table
  • “z_SomeField” value is changed; this will cause an Export to take place
  • The CSUpdateProc will map that field change to a “tblOrganization” trigger
  • All tblOrganization (Import Organization dataform) and Org Other Info mappings will be used to populate the Salesforce Opportunity and Account object
• Export Example 2, gen_ClientMaster related:
  • Client Master “z_SomeOtherField” is audited in the FCA table
  • Client Master “z_SomeOtherField” value is changed
  • The CSUpdateProc will map that field change to a “gen_ClientMaster” trigger
  • All Client Master mappings will be used to populate the Salesforce Opportunity and Account objects
• The ‘csupdateproc=x’ configuration parameter determines the stored procedure that will be used to collect the changes for Export
• PEO Default Configuration
  • Tbd

• Import/Export Mapping

The mapping of data between ClientSpace and Salesforce is defined using Import Map Headers.  Two Import Maps must be created:

• Export Map Header

• Source File: SFORCE_export_mapping*.csv
• Dataform: Import_Organization
• Custom Import Class: not yet implemented
• Import Type: Salesforce Export
• Execution Pipeline: ignored
• Import Action: ignored
• Execute Business Logic: ignored
• Special Import: checked

• Import Map Header

• Source File: SFORCE_import_mapping*.csv
• Dataform: Import_Organization
• Custom Import Class: not yet implemented
• Import Type: Salesforce Import
• Execution Pipeline: ignored
• Import Action: ignored
• Execute Business Logic: ignored
• Special Import: checked

• Configuring Export and Import Map Details

• File Column
  • ignored, enter text that describes the source of this detail (e.g. OrgName or sfAcctName) for clarification
• Field and Dataform
  • this selection will always be the ClientSpace data, whether configuring an Export or Import. When configuring an Export, this will be where the data originates when sending to Salesforce.  When configuring an Import, this is where the data from Salesforce will be stored
  • select any non-parent field type on any single-form
  • when mapping Organization or Contact, select Import_Organization or Import_Contact project type respectively.
• API Path
  • This entry will always be the Salesforce data, whether configuring an Export or Import. When configuring an Export, this will be where the data will be stored in Salesforce.  When configuring an Import, this is where the data from Salesforce will be retrieved
  • The format of this field is SF_ObjectType/FieldName, e.g. Account/BillingStreet
  • The only valid SF_ObjectType values are Opportunity, Account or Contact. Other values will be ignored
  • Can only contain a SF_ObjectType and up to two sub fields, as in Account/RecordType/Name
  • Cannot contain special characters (other than ‘/’) and no spaces
  • Salesforce fields that are ‘Calculated’ will not be exported by ClientSpace as Salesforce will not allow them to be directly updated
• Data Type:
  • Text
    • Most fields should be mapped as Text (even if the source or destination fields are integers or dates/etc.
  • Lookup
    • Not yet implemented (use Text)
  • Foreign Key
    • Currently, this is only implemented for Imports and locating CS Users
    • It will accept a Salesforce User ID and locate that User in ClientSpace by Email. A call is made to Salesforce using the SF User ID; the SF User Email is located and used to find a matching Email in ClientSpace
  • Default Value
    • A Default Value can be set in this field. In this case, the API Path must be set to “Object/DefaultValue” (without the quotes).  This default value will be set in the appropriate ClientSpace field.  For Imports the Object is not important, the Default Value will be set in the CS field.  For Exports, Object must be Opportunity, Account or Contact
  • Active, Required Value and Required Column
    • Only Active records will be used
    • Required Value and Required Column settings are ignored
  • Minimum Required Fields

Some fields are required in order to create Organization, Workspaces, Contacts, etc.  Those fields must be mapped or failures will be encountered.  Required fields depend on the dataform and the Client’s Installation.  See the Required Fields Mapping Table for details of PEO required fields.

• Business Intelligence Report

A report should be constructed to view records in tblScheduledProcessHistory.  Join to the tblScheduledProcess table (SPH.fk = SP.pk) and include SPH.ProcessStartDate, SPH.LogData and SPH.ProcessEndDate, sort on SPH.ProcessStartDate descending, filter on SP.Method = “SalesForceUpdate”

Required Fields Mapping Table

 The Salesforce Scheduled process

Importing and exporting Salesforce data relies on a scheduled process to keep the data in sync between the two systems. While this is not a real-time update, it should provide adequate connectivity between the two systems. Recommended settings for processing the Salesforce Update can be found below:

Recommended settings:

Name: Salesforce Update

Frequency: 15

Frequency Type: Minute

Next Run Date: Today's date

Next Run Time: Set to a future time when you would like the process to begin

Valid Days: Select the days of the week you would like the scheduled process to run on - this can be business days, or every day, whichever fits your business model.

Description: Brief description - in the example we used "Salesforce Update"

Max Run Minutes: How long the process is allowed to run before being automatically stopped.

Last Run Seconds (read-only): This is a measure of how long the process took the last time it ran.

Active: Only active processes are run by the scheduler.

The configuration above will run the "Salesforce Update" process once every 15 minutes beginning on 7/13/2017 at 9:15 am. The process will run every day of the week and will be automatically aborted if it takes more than 12 minutes to run.