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: { |
| < | 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: { |
| > | 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: { |
| >= | 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: { |
| <= | 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: { |
| != | Not Equal Example, if you don’t want active accounts returned from QQ_Account, you would do the following: { |
| 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: { |
| 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: { |
| 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: { |
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/