The CData Power BI Connector for Bullhorn CRM models core Bullhorn CRM entities (for example, candidates, client contacts, and job orders) as relational tables, which enables you to query this data using standard SQL. In addition, stored procedures allow you to perform operations in Bullhorn CRM, and live connectivity ensures that changes made in your Bullhorn CRM account are reflected immediately when working through the connector.

This page details key features of the connector:

• saved searches
• layouts
• custom tables and custom columns
• Associations tables
• supported operators

Saved Searches

The CData Power BI Connector for Bullhorn CRM presents saved searches as separate views. These views are named using the following convention: 'SavedSearch_{SavedSearchName}'.

Layouts

The CData Power BI Connector for Bullhorn CRM uses the ListLayouts property to determine which layouts to present as separate views. This property improves performance for tables with a large number of columns (for example, the Candidate table). You can use a layout view instead of the original table to achieve better performance.

These views are named using the following convention: '{OriginalEntity}{LayoutName}Layout'. For example, CandidateListRowLayout is the view that corresponds to the ListRow layout of the Candidate entity.

Custom Tables and Custom Columns

Bullhorn CRM can include custom tables and custom fields. To list these as tables and columns, use the IncludeCustomTables and IncludeCustomFields properties. By default, IncludeCustomTables is set to 'false', and IncludeCustomFields is set to 'true'.

Note: Setting these properties to 'true' can result in slower performance.

Associations Tables

By default, the connector does not list many-to-many associations. To include the Associations tables, set the ExpandAssociations property to 'True'. The connector then adds one Associations table for each 'To-Many' Associations field that it detects (for example, CandidateToPrimarySkillsAssociations, JobOrderToCategoriesAssociations, or WorkersCompensationToRatesAssociations).

See PrimaryEntityToAssociatedEntityAssociations for details about how to read, create, and delete associations.

Note: Enabling ExpandAssociations results in extra Metadata API calls to discover and list all Associations tables for each detected 'To-Many' Associations field.

Supported Operators

The connector offloads as much filtering as possible to the Bullhorn CRM API to improve performance. Bullhorn CRM supports two query languages: Lucene and JPQL.

Lucene

Only the Candidate and Note tables support the Lucene filtering. The supported operators for these tables are 'AND', 'OR', '=', 'IN', '>', '>=', '<', and '<='. The comparison operators ('>', '>=', '<', '<=') are supported only for datetime columns.

Bullhorn CRM processes the following example queries on the server:

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 tables except Candidate and Note support JPQL filtering. These tables allow the use of te following operators: 'AND', 'OR', 'NOT', '=', '!=', '>', '>=', '<', '<=', 'IS', 'IS NOT', 'IN', and 'NOT IN'.

Bullhorn CRM processes the following example queries on the server:

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, and GETDELETED Statements

You can perform write operations using the INSERT, UPDATE, and DELETE SQL statements, as shown in the following example:

INSERT INTO Task (Subject, Description) VALUES ('Follow up - John Smith', 'Follow up with the candidate John Smith.')
UPDATE Task SET Description = 'Follow up with the candidate John Smith in two weeks.' WHERE Id = 84905
DELETE FROM Task WHERE Id = 84905

GETDELETED Statement

You can also use the GETDELETED statement to retrieve the identifiers (Ids) of deleted records for supported tables, as shown in the following example:

GETDELETED FROM Candidate
GETDELETED FROM Appointment
GETDELETED FROM Task

Note: Not all tables support the retrieval of deleted records.