Dynamics SL Quick Query through Catalina’s RESTful API

Catalina Technology has an API that allows you to integrate with Dynamics SL’s Quick Query through both SOAP and REST interfaces. This example shows you how to make a call to Quick Query using RESTful API.

If you want to learn about the SOAP methods, you can see an earlier blog post here:

Example of building a Form Client to use the Catalina Quick Query SOAP Web Service

First, thing you can do is look at your swagger documentation by directing your browser to the swagger docs on your ctDynamicsSL web application.

Example: If you have ctDynamicsSL installed on your server here:

http://myserver/ctDynamicsSL

You would then add swagger to the URL for something like this:

http://myserver/ctDynamicsSL/swagger

You should then be presented with the interactive swagger documentation.

From here, you would enter your API login information, cpnyID, and siteID. After that, you can scroll down to see the quick query API calls

This gives you the ability to bring back all quick query definitions in your SL system:

GET: /api/quickQuery

You could bring back a single quick query definition from your SL system:

GET: /api/quickQuery/{queryViewName}

Or you can run a quick query:

POST: /api/quickQuery/{queryViewName}

Example:

As an example of a quick query call, we are going to call back all Accounts from the system. By pulling up the Quick Query Viewer, you can see how this works in Dynamics SL:

You can further filter by different fields by using the filtering method (below, I am filtering by Account Number = 1030)

We can do the same thing in the Catalina RESTful API for Dynamics SL by calling POST: /api/quickQuery/{queryViewName}

First, I am going to retrieve all Account Records without any filtering:

The CURL would be this:

curl -X POST -H "Content-Type: application/json" -H "Accept: application/json" -H "Authorization: Basic YOURAUTHENTICATIONINFO" -H "SiteID: YOURSITEID" -H "CpnyID: 0060" -H "Cache-Control: no-cache" -d '' "http://yourServer/ctDynamicsSL/api/quickQuery/QQ_Account"

If you use postman to test your API’s, it would look similar to this:

Now, if you want to filter for say a particular account number, would pass an object in the body called filters. This is an array of filter objects that looks like this:

[code lang=”javascript”]
{
“filters”: [{
“name”: “fieldName1”,
“value”: “”,
“comparisonType”: “”
},
{
“name”: “fieldName2”,
“value”: “”,
“comparisonType”: “”
}] } [/code]

Where:

name – This must match the name of a column in the query view)
value – This is the value we are to filter/compare against)
comparisonType – This is any valid SQL comparison operator. E.g.: =, <, >, LIKE, IN, NOT IN

An example of filtering the accounts by a particular Account Number would look like this:

[code lang=”javascript”]
{
“filters”: [{
“name”: “Account Number”,
“value”: “1030”,
“comparisonType”: “=”
}
]
} [/code]

This will then bring back records in the quick query that has “Account Number” = “1030”.

Say you want to retrieve all customers (from a customer details quick query), who is from a certain city (say redondo beach) and has a customer class of Retail.

You would then pass 2 filters to the filters object

[code lang=”javascript”]
{
“filters”: [{
“name”: “City”,
“value”: “Redondo Beach”,
“comparisonType”: “=”
},
{
“name”: “Customer Class ID”,
“value”: “COMM”,
“comparisonType”: “=”
}
]
}
[/code]

If you wanted to look at a CURL statement for this, it would look similar to this (noting that you would want to change for your authentication, siteID, cpnyID, etc.)

curl -X POST -H "Content-Type: application/json" -H "Accept: application/json" -H "Authorization: Basic YOURAUTHENTICATION" -H "SiteID: YOURSITE" -H "CpnyID: YOURCPNYID" -H "Cache-Control: no-cache" -d '{
"filters": [{
"name": "City",
"value": "Redondo Beach",
"comparisonType": "="
},
{
"name": "Customer Class ID",
"value": "COMM",
"comparisonType": "="
}
]
}' "http://catalina/ctDynamicsSL/api/quickQuery/QQ_Customer"

So, as you can see, combining Quick Query with Catalina’s API for Dynamics SL, allows you to be able to extend SL to most any application. We use it all the time for integrating with CRM, Salesforce, helpdesk systems, manifesting systems, timecards, and much more!

bwharton March 2, 2017