Catalina’s Queue Engine

Catalina has a simple queuing engine that allows you to track changes on any table in SQL server. There is then an API that allows you to retrieve items that have been queued so that you can take action on them.  This is mostly done when you need to send Dynamics SL data, that has changed, to an outside system.

Example: A customer in Dynamics SL is modified in the SL Customer Maintenance Screen. You want to make sure that the customer terms, class, and other information makes it out to Salesforce.com (or other CRM system).

How is this done?  We add triggers to any table you want to track changes.  Those triggers fill the queue whenever a record, tied to that trigger, is:  updated, added, or  deleted. After records have been queued, you can then make a call to our queue API, retrieving any records that haven’t been retrieved yet.

The Sample Triggers

The sample triggers allow the following actions to be tracked against the Customer table and the SOAddress Table. You can get a copy of the triggers here:  Triggers on GitHub

NOTE:  the sample triggers are not installed by default in Catalina’s API for Dynamics SL.  This is because different installations have different needs.  We provide these sample triggers to get you started.  You will need to install these triggers to make the examples in this post work.

REST example for receiving updated customers

Here is a simple way to retrieve all customer records that have been updated in SL using the REST syntax.  (NOTE:  this is using curl.  You can import this code into your favorite REST tool)

curl -X GET -H “Accept: application/json” -H “Authorization: Basic YOURAUTHORIZATIONHERE” -H “CpnyID: 0060” -H “SiteID: DEFAULT” -H “Cache-Control: no-cache” “http://YourServer.com/ctDynamicsSLWebApi/api/queue/CUSTOMER?status=UPDATE&changeStatus=RECEIVED“

Where:

• YOURAUTHORIZATIONHERE: would be your Base64 encoded basic authentication
• 0060: Replace this with your CpnyID
• DEFAULT: replace this with your configuration SiteID
• YourServer.com/ctDynamicsSLWebAPI:  replace this with the location of your Catalina REST API endpoints.
• CUSTOMER:  This is the record type you want to receive.  In this case, it is for a Customer Record.
• UPDATE: this is the status you want to retrieve
• RECEIVED: this is the status you want to set the queued item to after you retrieve it (this keeps it from getting retrieved a second time)

SOAP Example for receiving inserted customers

If you want to pull all new customers that have been added to the system since last time you called the queue, you would do the below. Note, that I put the ITEMTYPE = CUSTOMER and the STATUS = ADD to retrieve all customer records that have been added in Dynamics SL.  Also note that the CHANGESTATUS = “RECEIVED” will mark the queued item as received so that it won’t be retrieved a second time.

The below code is an example on how we would retrieve data using the Catalina SOAP version of the API for Dynamics SL.

NOTE:  You can get more sample code on how this works here: Catalina Queue Example on GitHub

[code lang=”csharp”]
// set up the parms
List customerParms = new List();

// we are going to be retrieving CUSTOMER records
customerParms.Add(new ctDynamicsSL.queue.nameValuePairs
{
name = "ITEMTYPE",
value = "CUSTOMER"
});

// we are going to be retrieving status of UPDATE. We coould also be dealing with DELETE, ADD, etc.
customerParms.Add(new ctDynamicsSL.queue.nameValuePairs
{
name = "STATUS",
value = "ADD"
});

// all queue item records will be set to RETRIEVED so that they wont be returned again (until the source record is updated again)
customerParms.Add(new ctDynamicsSL.queue.nameValuePairs
{
name = "CHANGESTATUS",
value = "RETRIEVED"
});

try
{
// retrieve all the queue records for the parameters
var customerQueueItems = QueueSvc.getDSLXMLs(0, 0, customerParms.ToArray());
[/code]