Retrieve Bullhorn Opportunities

DBX can read data from local data sources such as MS SQL databases, text files, ODBC compliant databases.  The Bullhorn Data Source Task was built to support retrieving data from Bullhorn data entities which opens up to a whole new set of automated data management workflows.

This article will demonstrate how to retrieve data from the Opportunity entity.

Step 1 – Add a Bullhorn Data Source Task.

Step 2 – Set Task Definition

Simply give the task a name, in this example we simply gave it task description “Get Opportunities”.

 

Step 2 – Set Data Retrieval Settings

2.1 – Select “Opportunities”

2.2 – Set the fieldnames to query setting by clicking on the “…” button.  Select the following fields :

dateLastModified,id,description,customText1,notes[1]

Notice that we specified “notes[1]”.  This will retrieve the latest note entered.

2.3 – Bullhorn entities can be retrieved either using the Query method or the Lucene method.  Opportunity entity can be retrieved using either Query or Lucene method.  Query method is a lot easier to use so select the “Query” method.

The Query expression needed to retrieve opportunities with existing notes and have been modified in the last 216 days should be:

id > 0  AND notes IS NOT EMPTY AND isDeleted=false AND dateLastModified >= ##timevalue

Take note of the “##timevalue” expression.  This is a tag for a calculated value from the Expression Store – we will set that up in the next few steps.

2.4 –  Set the Sort settings to sort by the Id field in ascending order.

 

Step 3 – Set Expression Store for ##timevalue expression

Create a new SQL Expression and use the expression below.

select cast(DATEDIFF(SECOND,{d ‘1970-01-01’}, dateadd(d,-216, getdate())) as bigint) * 1000 as timevalue

The output fieldname from the expression will be “timevalue”.  This output fieldname is referenced by the query expression from the previous step and this is done by prefixing the fieldname with two # characters : ##timevalue

Step 4 – Test Data Retrieval

Go back to the “Data Source” tab and click on the “Preview Expression Result” button.

Step 5 – Save and Close the Bullhorn Data Source Task.

endusersoftware has written 25 articles