Bullhorn CRM Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8803 of the connector.

Get Started

Bullhorn CRM Version Support

The connector leverages the Bullhorn CRM API to enable bidirectional access to Bullhorn CRM.

Establish a Connection

Connect to Bullhorn CRM

Begin by providing your Bullhorn CRM account credentials. Set DataCenterCode to the data center code that responds to your data center. Refer to the list here.

If you are uncertain about your data center code, codes like CLS2, CLS21, etc. are cluster IDs that are contained in a user's browser URL (address bar) once they are logged in.

Example: https://cls21.bullhornstaffing.com/BullhornSTAFFING/MainFrame.jsp?#no-ba... indicates that the logged in user is on CLS21.

Adding or removing any character of this parameter results in an error.

Authenticate to Bullhorn CRM (OAuth)

Bullhorn CRM uses the OAuth 2.0 authentication standard.

In all scenarios, to authenticate using OAuth, you must create and configure a custom OAuth application. See Creating a Custom OAuth App for more information.

Desktop Applications

Follow the steps below to authenticate with the credentials for a custom OAuth application.

Scenario 1: Verifier Code

Making any changes to your OAuth application (such as the CallbackURL) requires reaching out to Bullhorn CRM directly, which can be a time-consuming process. So, we highly recommended that you instead use an OAuthVerifier to connect. To obtain a verifier, you also need an OAuth Authorization URL.

Retrieving an OAuth Verifier Code

Construct your authorization URL as follows:
*https://**<authurlfor your data center>/oauth/authorize?client_id=<your OAuth client id>*&response_type=code

For example, if you had data center "CLS5", the authentication URL is https://auth.bullhornstaffing.com/oauth/authorize?client_id=your_client_id&response_type=code.

Please refer to the list here to determine the authentication URL for your data center.

Next, retrieve the OAuth verifier code:

1. Open your authorization URL in a browser. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
2. Save the value of the verifier code.

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• OAuthVerifier: Set this to the OAuth Verifier code obtained previously.
• DataCenterCode: Set this to the respective data center your account is connected with. Find the list of the data centers here. For example, CLS33.
• OAuthClientId: Set this to the client ID assigned when you registered your application.
• OAuthClientSecret: Set this to the client secret assigned when you registered your application.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values in OAuthSettingsLocation. These stored values persist across connections.

Scenario 2: Custom CallbackURL

You also have the option of requesting a custom callback URL (for example, http://localhost:33333) from Bullhorn CRM. If you have been provided with a custom CallbackURL, you can set the following to connect to data:

• CallbackURL: Set this to the custom callback URL provided by Bullhorn CRM.
• DataCenterCode: Set this to the respective data center your account is connected with. Find the list of the data centers here. For example, CLS33.
• OAuthClientId: Set this to the client ID assigned when you registered your application.
• OAuthClientSecret: Set this to the client secret assigned when you registered your application.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.

The connector then completes the OAuth process:

1. The process finalizes with the access token returned from the Bullhorn CRM API.
2. Refreshes the access token when it expires.
3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Contact Bullhorn

Considerations when Connecting to Bullhorn CRM

Unlike many other data sources, Bullhorn CRM doesn't allow users to create their own custom OAuth applications or edit their CallbackURL directly.

Instead, the user must contact Bullhorn CRM and request that they create/change the user's OAuth information on Bullhorn CRM's end.

If a user needs to change their CallbackURL, waiting for a new one from Bullhorn CRM may be a time-consuming process.

It is highly recommended that users consider using an OAuth verifier code to connect instead. See "Authenticating to Bullhorn CRM" for more details.

Create a Custom OAuth App

You must create a custom OAuth app to connect to Bullhorn CRM.

The only way to create an app is to contact the Bullhorn CRM support team. They will provide you with the application credentials (clientId and client secret). You can find more information about how to register here.

In addition, the CallbackURL or Redirect URI defaults to a page on bullhorn.com.

In order to set InitiateOAuth to GETANDREFRESH, you must contact Bullhorn CRM staff and request a separate CallbackURL.

If a separate Callback URL cannot be specified, the "code" parameter from the redirect page on Bullhorn CRM must be taken and passed as an input to either:

• The Verifier input of GetOAuthAccessToken if executing it manually.
• The connection property OAuthVerifier when setting InitiateOAuth to GETANDREFRESH.

Fine-Tuning Data Access

Fine Tuning Data Access

You can use the following properties to gain greater control over Bullhorn CRM API features and the strategies the connector uses to surface them:

• IncludeCustomFields: Whether or not custom fields will be included. When off, performance will be higher, but the custom fields cannot be retrieved or modified.
• IncludeCustomTables: Whether or not custom tables will be included. When off, performance will be higher, but the custom tables cannot be retrieved.
• ListLayouts: A list of tables you want to list layouts for. For example: ListLayouts=Candidate,JobOrder;.
• UseDefaultOrderBy: Indicates if a default order by should be applied when none is specified. This will prevent duplicate results, but will make large tables much more expensive to retrieve.
• UseDisplayNames: Set this to true to use field labels for column names.
• MaxThreads: Specifies the number of concurrent requests. Increase it to improve performance, especially when a table has many rows.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Bullhorn CRM connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Bullhorn CRM and then processes the rest of the query in memory (client-side).

User Defined Views

The Bullhorn CRM connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Candidate WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

Overview

This section shows the available API objects and provides more information on executing SQL to Bullhorn CRM APIs.

Key Features

• The connector models Bullhorn CRM entities like candidates, client contacts, and job orders as relational tables, allowing you to write SQL to query Bullhorn CRM data.
• Stored procedures allow you to execute operations to Bullhorn CRM.
• Live connectivity to these objects means any changes to your Bullhorn CRM account are immediately reflected when using the connector.

Saved Searches

The connector exposes saved searches as separate views. These views will be named like 'SavedSearch_{Saved search name}'.

Layouts

The connector uses the ListLayouts property to determine what layouts to expose as separate views. This property is recommended because it will boost the performance on tables with too many columns, like Candidate. So you can use the desired layout view, instead of the original table, for a better performance.

The naming of these views is like '{original entity}{layout name}Layout'. For example, CandidateListRowLayout, is the view that corresponds to the ListRow layout of candidate entity.

Custom tables and custom columns

Bullhorn CRM can have custom entities and custom fields. If you want them to be listed as tables and columns respectively use the IncludeCustomTables and IncludeCustomFields properties. IncludeCustomTables is set to false by default while IncludeCustomFields is set to true.
Note that setting these properties to true may result on a slower performance.

Associations Tables

By default, the connector does not expose the Many-To-Many associations between different entities. In order to expose the Associations Tables, you will need to set the ExpandAssociations connection property to 'True'. This way, the connector exposes one associations table for every discovered 'To-Many' associations field on a Bullhorn CRM entity (ex. CandidateToPrimarySkillsAssociations, JobOrderTocategoriesAssociations, WorkersCompensationToRatesAssociations, etc.).

For a guide on how to Read, Create and Delete Associations check PrimaryEntityToAssociatedEntityAssociations.

Supported operators

The connector offloads as much of the filtering as possible to Bullhorn CRM API, in order to have a better performance. The Bullhorn CRM supports two query languages, Lucene and JPQL.

Lucene
Only Candidate and Note entities support the Lucene filtering for now. The supported operators on these tables are: 'AND', 'OR', '=', 'IN', '>', '>=', '<', '<='. Note that '>', '>=', '<', '<=' are only supported for datetime columns.

Some query examples that are handled server side are:

SELECT * FROM Candidate WHERE Id = '109989'
SELECT * FROM Candidate WHERE Id IN (109989, 110011)
SELECT * FROM Candidate WHERE Id IN (109989, 110011) AND DateAdded > '2018-06-11 11:40:17.577'
SELECT * FROM Candidate WHERE DateAdded < '2019-12-15 14:31:01'
SELECT * FROM Candidate WHERE DateAdded >= '2019-12-16 14:31:01' AND (AddressCity = 'southfield' OR AddressState = 'indiana')

JPQL
All the tables except Candidate and Note support JPQL filtering. The supported operators on these tables are: 'AND', 'OR', 'NOT', '=', '!=', '>', '>=', '<', '<=', 'IS', 'IS NOT', 'IN', 'NOT IN'.

Some query examples that are handled server side are:

SELECT * FROM Appointment WHERE Id IN ('83409', '83404', '83398')
SELECT * FROM Appointment WHERE Id IN ('83409', '83404', '83398') AND communicationMethod = 'phone'
SELECT * FROM Appointment WHERE CandidateId IS NOT NULL AND StartDate > '2019-12-17 12:01:00.000' ORDER BY Id, CandidateId
SELECT * FROM Appointment WHERE (CandidateId IS NOT NULL AND StartDate >= '2019-12-17 12:01:00.000') OR Description IS NOT NULL
SELECT * FROM Appointment WHERE (CandidateId NOT IN (19624,19622,19621,19620,19618) AND StartDate > '2019-12-17 12:01:00.000') OR Location != 'Louisville, KY'

Insert, Update, Delete You can perform write operations using Insert, Update, Delete SQL statements. Some query examples:

INSERT INTO Task (Subject, Description) VALUES ('Follow up - Billy Joel', 'Follow up the candidate Billy Joel.')
UPDATE Task SET Description = 'An updated description here.' WHERE Id = 84905
DELETE FROM Task WHERE ID = 84905

GETDELETED You can get the IDs of the deleted records on several entities (Not all entities support deleted records).

GETDELETED FROM Candidate
GETDELETED FROM Appointment
GETDELETED FROM Task

Tables

The connector models the data in Bullhorn CRM as a list of tables in a relational database that can be queried using standard SQL statements.

Bullhorn CRM Connector Tables

ActivityGoal

Create, update, delete and query data of 'ActivityGoal' entity in BullhornCRM system.

Columns

ActivityGoalConfiguration

Create, update, delete and query data of 'ActivityGoalConfiguration' entity in BullhornCRM system.

Columns

ActivityGoalTarget

Create, update, delete and query data of 'ActivityGoalTarget' entity in BullhornCRM system.

Columns

Appointment

Create, update, delete and query data of 'Appointment' entity in BullhornCRM system.

Columns

AppointmentAttendee

Create, update, delete and query data of 'AppointmentAttendee' entity in BullhornCRM system.

Columns

AppointmentEditHistory

Create, update, delete and query data of 'AppointmentEditHistory' entity in BullhornCRM system.

Columns

AppointmentEditHistoryFieldChange

Create, update, delete and query data of 'AppointmentEditHistoryFieldChange' entity in BullhornCRM system.

Columns

BillableChargeEditHistory

Create, update, delete and query data of 'BillableChargeEditHistory' entity in BullhornCRM system.

Columns

BillableChargeEditHistoryFieldChange

Create, update, delete and query data of 'BillableChargeEditHistoryFieldChange' entity in BullhornCRM system.

Columns

Branch

Create, update, delete and query data of 'Branch' entity in BullhornCRM system.

Columns

BusinessSector

Create, update, delete and query data of 'BusinessSector' entity in BullhornCRM system.

Columns

Candidate

Create, update, delete and query data of 'Candidate' entity in BullhornCRM system.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CandidateCertification

Create, update, delete and query data of 'CandidateCertification' entity in BullhornCRM system.

Columns

CandidateCertificationEditHistory

Create, update, delete and query data of 'CandidateCertificationEditHistory' entity in BullhornCRM system.

Columns

CandidateCertificationEditHistoryFieldChange

Create, update, delete and query data of 'CandidateCertificationEditHistoryFieldChange' entity in BullhornCRM system.

Columns

CandidateCertificationRequirement

Create, update, delete and query data of 'CandidateCertificationRequirement' entity in BullhornCRM system.

Columns

CandidateCertificationRequirementEditHistory

Create, update, delete and query data of 'CandidateCertificationRequirementEditHistory' entity in BullhornCRM system.

Columns

CandidateCertificationRequirementEditHistoryFieldChange

Create, update, delete and query data of 'CandidateCertificationRequirementEditHistoryFieldChange' entity in BullhornCRM system.

Columns

CandidateEducation

Create, update, delete and query data of 'CandidateEducation' entity in BullhornCRM system.

Columns

CandidateFileAttachment

Create, update, delete and query data of 'CandidateFileAttachment' entity in BullhornCRM system.

Columns

CandidateHistory

Create, update, delete and query data of 'CandidateHistory' entity in BullhornCRM system.

Columns

CandidateReference

Create, update, delete and query data of 'CandidateReference' entity in BullhornCRM system.

Columns

CandidateReferenceQuestion

Create, update, delete and query data of 'CandidateReferenceQuestion' entity in BullhornCRM system.

Columns

CandidateReferenceResponse

Create, update, delete and query data of 'CandidateReferenceResponse' entity in BullhornCRM system.

Columns

CandidateSource

Create, update, delete and query data of 'CandidateSource' entity in BullhornCRM system.

Columns

CandidateWorkHistory

Create, update, delete and query data of 'CandidateWorkHistory' entity in BullhornCRM system.

Columns

Category

Create, update, delete and query data of 'Category' entity in BullhornCRM system.

Columns

Certification

Create, update, delete and query data of 'Certification' entity in BullhornCRM system.

Columns

CertificationGroup

Create, update, delete and query data of 'CertificationGroup' entity in BullhornCRM system.

Columns

CertificationRequirement

Create, update, delete and query data of 'CertificationRequirement' entity in BullhornCRM system.

Columns

ClientContact

Create, update, delete and query data of 'ClientContact' entity in BullhornCRM system.

Columns

ClientContact1

Create, update, delete and query data of 'ClientContact1' entity in BullhornCRM system.

Columns

ClientContact2

Create, update, delete and query data of 'ClientContact2' entity in BullhornCRM system.

Columns

ClientContact3

Create, update, delete and query data of 'ClientContact3' entity in BullhornCRM system.

Columns

ClientContact4

Create, update, delete and query data of 'ClientContact4' entity in BullhornCRM system.

Columns

ClientContact5

Create, update, delete and query data of 'ClientContact5' entity in BullhornCRM system.

Columns