Author: catalinatechnology

Retrieve Catalina Queue FIFO

Retrieve Catalina Queue FIFO

If you use the Catalina Queue Engine for queuing records, you might want to retrieve data in a FIFO (First In First Out) method. This means you want to grab the oldest records first. This is easily accomplished by calling the Catalina Queue API with the parameter sortBy in the query string.

Example, if you are retrieving the queue ORDERS, you would format your URL, for the Queue API, like this:

http://YOURSERVER/ctDynamicsSL/api/queue/ORDER?sortBy=createdDate

This will then sort the queue in Ascending order by createdDate. Which means it will retrieve oldest items first.

Below is a curl example that does the same thing.

curl -X GET \
'http://YourServer/ctDynamicsSL/api/queue/QUEUETYPE?sortBy=createdDate' \
-H 'Accept: application/json' \
-H 'Authorization: Basic YOURAUTHHERE' \
-H 'Cache-Control: no-cache' \
-H 'Content-Type: application/json' \
-H 'CpnyID: YOURCPNYID' \
-H 'SiteID: YOURSITEID' \
-H 'cache-control: no-cache'

Minimum SQL Security Requirements for Catalina API

Minimum:
db_datawriter
db_datareader
db_ddladmin

If you are going to use our userMaintenance web service, to manage SL user logins, it will also need:
db_securityadmin
db_accessadmin

Also note that by default, we normally install our scripts under the dbo schema. So, you also have to give the user, we are connecting as, execute on that schema.

eg. grant  execute on schema :: [dbo] to usernamehere

PH and Temperature Hydroponic Monitor using MQTT

There have been quite a few requests for the code for my MQTT posting IOT project for testing PH and Temperature of my hydroponic towers. This code is several years old and I haven’t looked at it for at least 2. But, I figured I would put it out on GitHub.

The main purpose of this code is to monitor the PH and temperature in a water tank for my hydroponics and then use MQTT to post to io.adafruit for a dashboard.

https://github.com/dafoink/ADS1115

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

Catalina’s Ever Growing List of API’s for Dynamics SL

We at Catalina Technology are always growing our API for Dynamics SL. Below are just a few of the features included in our API suite for Dynamics SL. You can see this same list on our website here:

https://www.catalinatechnology.com/?CTAPI_LIST_OF_SL_SCREENS

List of API Functions as they Relate to SL Screens

ctDynamicsSL.administration.systemManager.security.userMaintenance

Comparable functionality to the SL screen (95.260.00)

ctDynamicsSL.email

Web service for sending emails using the Catalina Email Queueing and Templating API

ctDynamicsSL.fieldService.serviceContracts.maintenance.serviceContractEntry

Comparable functionality to the SL screen (SN.001.00)

ctDynamicsSL.fieldService.serviceDispatch.input.serviceCallEntry

Comparable functionality to the SL screen (SD.200.00)

ctDynamicsSL.fieldService.serviceDispatch.input.serviceCallInvoiceEntry

Comparable functionality to the SL screen (SD.202.00)

ctDynamicsSL.fieldService.serviceDispatch.maintenance.serviceEmployeeMaintenance

Comparable functionality to the SL screen (SD.007.00 and SD.007.01)

ctDynamicsSL.fieldService.serviceDispatch.maintenance.siteMaintenance

Comparable functionality to the SL screen (SD.025.00)

ctDynamicsSL.financial.accountsPayable.input.manualCheckPaymentEntry

Comparable functionality to the SL screen (03.030.00)

ctDynamicsSL.financial.accountsPayable.input.voucherAndAdjustmentEntry

Comparable functionality to the SL screen (03.010.00)

ctDynamicsSL.financial.accountsPayable.maintenance.vendorMaintenance

Comparable functionality to the SL screen (03.270.00)

ctDynamicsSL.financial.accountsReceivable.input.invoiceAndMemo

Comparable functionality to the SL screen (08.010.00)

ctDynamicsSL.financial.accountsReceivable.input.paymentApplication

Comparable functionality to the SL screen (08.030.00)

ctDynamicsSL.financial.accountsReceivable.input.paymentEntry

Comparable functionality to the SL screen (08.050.00)

ctDynamicsSL.financial.accountsReceivable.maintenance.customerMaintenance

Comparable functionality to the SL screen (08.260.00)

ctDynamicsSL.financial.accountsReceivable.maintenance.salesPersonMaintenance

Comparable functionality to the SL screen (08.310.00)

ctDynamicsSL.financial.eBankingSuite.maintenance.vendorBankingEntry

Requires 3rd party module: eBanking for Dynamics SL
http://www.sksoft.com/products/ebanking.php

ctDynamicsSL.financial.generalLedger.input.journalTransactions

Comparable functionality to the SL screen (01.010.00)

ctDynamicsSL.financial.payroll.maintenance.employeeMaintenance

Comparable functionality to the SL screen (02.250.00)

ctDynamicsSL.foundation.sharedInformation.maintenance.taxCategoryMaintenance

Comparable functionality to the SL screen (21.310.00)

ctDynamicsSL.foundation.sharedInformation.maintenance.taxGroupMaintenance

Comparable functionality to the SL screen (21.340.00)

ctDynamicsSL.foundation.sharedInformation.maintenance.taxMaintenance

Comparable functionality to the SL screen (21.280.00)

ctDynamicsSL.inventory.billOfMaterial.maintenance.billOfMaterialMaintenance

Comparable functionality to the SL screen (11.250.00)

ctDynamicsSL.inventory.billOfMaterial.maintenance.routingMaintenance

Comparable functionality to the SL screen (11.260.00)

ctDynamicsSL.inventory.inventory.input.inventoryIssues

Comparable functionality to the SL screen (10.020.00)

ctDynamicsSL.inventory.inventory.input.inventoryReceipts

Comparable functionality to the SL screen (10.010.00)

ctDynamicsSL.inventory.inventory.input.inventoryTransfers

Comparable functionality to the SL screen (10.040.00)

ctDynamicsSL.inventory.inventory.maintenance.inventoryItems

Comparable functionality to the SL screen (10.250.00)

ctDynamicsSL.inventory.inventory.maintenance.kits

Comparable functionality to the SL screen (10.320.00)

ctDynamicsSL.orderManagement.input.autoCharge

Requires 3rd party module: AutoCharge for Dynamics SL http://www.ehtc.com/AutoCharge

ctDynamicsSL.orderManagement.input.manifestEntry

Create, Read, Update, Delete functionality comparable to the manifestEntry screen (40.115.00)
Process Manager functionality not included.

ctDynamicsSL.orderManagement.input.shippers

Comparable functionality to the SL screen (40.110.00)

ctDynamicsSL.orderManagement.maintenance.customerContacts

Comparable functionality to the SL screen (08.260.00)

ctDynamicsSL.orders

Comparable functionality to the SL screen (40.100.00)

ctDynamicsSL.project.contractManagement.input.subContractPaymentRequestEntry

Comparable functionality to the SL screen (CN.SPR.00)

ctDynamicsSL.project.flexibleBillings.input.projectInvoiceAndAdjustmentMaintenance

Comparable functionality to the SL screen (BI.BAM.00)

ctDynamicsSL.project.projectBudgeting.input.budgetRevisionMaintenance

Comparable functionality to the SL screen (BU.BRM.00)

ctDynamicsSL.project.projectController.maintenance.projectEmployeeMaintenance

Comparable functionality to the SL screen (PA.EMP.00)

ctDynamicsSL.project.projectController.maintenance.projectMaintenance

Comparable functionality to the SL screen (PA.PRJ.00)

ctDynamicsSL.project.projectController.projectChargeEntry

Comparable functionality to the SL screen (PA.CHG.00)

ctDynamicsSL.project.timeAndExpense.input.projectTimecardWithRateAmtEntry

Comparable functionality to the SL screen (TM.TEA.00)

ctDynamicsSL.project.timeAndExpense.input.projectTravelAndExpenseEntry

Comparable functionality to the SL screen (TM.ENT.00)

ctDynamicsSL.project.timeAndExpense.maintenance.projectEmployeePositionRateMaintenance

Comparable functionality to the SL screen (TM.EPJ.00)

ctDynamicsSL.project.timeAndExpense.maintenance.projectExpenseTypeMaintenance

Comparable functionality to the SL screen (TM.ETM.00)

ctDynamicsSL.purchasing.inquiries.itemVendorHistory

Comparable functionality to the SL screen (04.310.00)

ctDynamicsSL.purchasing.inquiries.poReceiptsInquiry

Comparable functionality to the SL screen (04.200.00)

ctDynamicsSL.purchaseOrders

Comparable functionality to the SL screen (04.250.00)

ctDynamicsSL.queue

Service for queue functions for customization/integration purposes

ctDynamicsSL.quickQuery

Comparable functionality to the SL screen (QQ.VIE.00)