Tips/Tricks on using Catalina’s Quick Query API to get the data you want out of a SQL Server Database

The examples below are for the RESTful API version of the Catalina API for SL. If you want to see how you can do similar things in SOAP, you can see an older post here: https://catalinatechnology.wordpress.com/2017/02/06/example-of-building-a-form-client-to-use-the-catalina-quick-query-soap-web-service/

Paging Data

You can page content by passing a pageSize and a currentPageNumber.  The below example will retrieve page 5 with a page size of 10 items

{
"filters": [{
"name": "pageSize",
"value": "10",
"comparisonType": "="
},
{
"name": "currentPageNumber",
"value": "5",
"comparisonType": "="
}
]
}

What you will see with this is that in the table results, there will be several fields:

  • counter: this is the current record in the returnset
  • totalEntries: This will be the total number of records in the query
  • totalPages: this will be the total number of pages (given the pageSize)

This would then allow you to page through the results and not bring everything down at once.  Below is an example of those fields:

 {
"counter": 41,
"Account Number": "12313 ",
"Account Type": "1A",
"Active": 1,
"Class ID": "ASSETS ",
"Validate ID": " ",
"totalEntries": 338,
"totalPages": 34,
"errorMessage": null
},

Filtering Data

You can also limit records by adding additional filters that you can limit by fields.  Here is an example of bringing back all records with a Class ID = “ASSETS”

{
"filters":[
{
"name": "Class ID",
"value":"ASSETS",
"comparisonType":"="
}
]
}

If you wanted to bring back page 4, with a page size of 10, of items that have a Class ID = ‘ASSETS” and are flagged active, you would do this:

{
"filters": [{
"name": "pageSize",
"value": "10",
"comparisonType": "="
},
{
"name": "currentPageNumber",
"value": "4",
"comparisonType": "="
},
{
"name": "Class ID",
"value": "ASSETS",
"comparisonType": "="
},
{
"name": "Active",
"value": "1",
"comparisonType": "="
}
]
}

Some notes about comparisonType. These are the possible values (NOTE:  You can add as many comparisons as you want in your filters to get the results you need)

Type Description
= Equals
Example, if you wanted to retrieve all items with an Account Type equal to 1A, in QQ_Account, you would do the following:
{
“filters”: [{
“name”: “Account Type”,
“value”: “1A”,
“comparisonType”: “=”
}]
}

Less than
Example, if you wanted to retrieve all items with a created date less than 1/2/2000, in QQ_Account, you would do the following:
{
“filters”: [{
“name”: “Account Type”,
“value”: “1/2/2000”,
“comparisonType”: “<“
}]
}
Greater than
Example, if you wanted to retrieve all items with a created date greater than 1/2/2000, in QQ_Account, you would do the following:
{
“filters”: [{
“name”: “Account Type”,
“value”: “1/2/2000”,
“comparisonType”: “>”
}]
}
>= Greater than or equal
Example, if you wanted to retrieve all items that were updated after or equal to 3/24/200, in QQ_Account, you would do the following:
{
“filters”: [{
“name”: “Last Update Date”,
“value”: “3/24/2000”,
“comparisonType”: “>=”
}]
}
<= Less than or equal
Example, if you wanted to retrieve all items that were updated before or equal to 3/23/200, in QQ_Account, you would do the following:
{
“filters”: [{
“name”: “Last Update Date”,
“value”: “3/23/2000”,
“comparisonType”: “<=”
}]
}
!= Not Equal
Example, if you don’t want active accounts returned from QQ_Account, you would do the following:
{
“filters”: [{
“name”: “Active”,
“value”: “1”,
“comparisonType”: “!=”
}]
}
LIKE Like (or contains) – Uses % as a wildcard
Example: if you want to retrieve all account numbers that start with 103, from QQ_Account, you would do the following:
{
“filters”: [{
“name”: “Account Number”,
“value”: “103%”,
“comparisonType”: “LIKE”
}]
}

IN Inside a comma delimited list
Example: if you want to retrieve just account 1030 and 1031 from QQ_Account, you would do the following:
{
“filters”: [{
“name”: “Account Number”,
“value”: “1030,1031”,
“comparisonType”: “IN”
}]
}
NOT IN Not in a comma delimited list
Example: if you want to exclude accounts 1110 and 1115 from QQ_Account, you would do the following:

{
“filters”: [{
“name”: “Account Number”,
“value”: “1110,1115”,
“comparisonType”: “NOT IN”
}]
}

More information can be found about the RESTful API version of Catalina’s Quick Query API can be found here: https://catalinatechnology.wordpress.com/2017/03/02/dynamics-sl-quick-query-through-catalinas-restful-api/

If you want to see more information about Quick Query (particularly about how you can do it in SOAP), you can view that information here: https://catalinatechnology.wordpress.com/2017/02/06/example-of-building-a-form-client-to-use-the-catalina-quick-query-soap-web-service/

Catalina’s Ever Growing List of API’s for Dynamics SL
PH and Temperature Hydroponic Monitor using MQTT

Leave a Reply

Your email address will not be published / Required fields are marked *