概要

このセクションでは、利用可能なAPI オブジェクトを示し、Bullhorn CRM API へのSQL の実行について詳しく説明します。

主要機能

• 本製品 は、candidates、client contacts、job orders のようなBullhorn CRM エンティティをリレーショナルテーブルとしてモデル化し、SQL を記述してBullhorn CRM データをクエリできるようにします。
• ストアドプロシージャを使用するとBullhorn CRM の操作を実行できます。
• これらのオブジェクトへのライブ接続により、本製品 を使用するとBullhorn CRM アカウントへのあらゆる変更が即座に反映されます。

保存された検索

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

Layouts

The 本製品 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 本製品 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 本製品 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 本製品 will send several additional Metadata API calls to discover and list all Associations tables for each detected 'To-Many' associations field.

サポートされる演算子

The 本製品 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