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.

Note: On enabling ExpandAssociations, the connector will send several additional Metadata API calls to discover and list all Associations tables for each detected 'To-Many' associations field.

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