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 Harmony 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¶