Tag: ctapi

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'
Advertisements

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

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)

How to add and edit SOAddress records in SL using Catalina’s API for Dynamics SL

How to add and edit SOAddress records in SL using Catalina’s API for Dynamics SL

Here is an example of how you can create a NEW shipto address, using POST, for a customer (NOTE:  I am doing a post and not passing a ShipToId in the URL.)

Also NOTE:  When doing a post (adding a NEW address), if you explicitly set a ShipToId in the body, it cant be an existing ShipToId for that customer or an error will be thrown.  If you do pass a ShipToId in the body, the system will try to create a new ShipTo Address using that ShipToId.  If you don’t pass a ShipToId, the API will auto generate a new ShipToId for you.

Another NOTE:  There is an issue with the API, where you need to make sure that you put the CustId in the body, when doing a POST for a new ShipTo Address, or it wont save against the CustId.  This is a known issue where the API doesn’t grab the CustId from the query string when it is saving the SOAddress, it grabs from the body.  So, make sure that you are putting CustId in the body (also NOTE the capitalization of CustId. Darn Solomon in its differences in capitalization throughout.)

Creating a new SOAddress for a customer:

Here is an example of how to create a new SOAddress using curl and the action type of POST.  Variables that are in the call:

  • yourserver:  You would replace the actual server you have the Catalina API installed on instead of using “yourserver.”  this is just a placeholder.
  • YOURAUTHCODEHERE:  This should be the encoded basic authentication string for your setup
  • YOURCPNYID:  This should be the CpnyID of the Company you are using from your System database
  • YOURSITEIDHERE:  This should be the SiteID setup in the API configuration
  • CUSTID1:  This is a customer ID (CustID) that I am using in my example.  You would of course use a valid CustID in your SL’s Customer table
  • ADDR1:  I am forcing this to save a new ShipTo Address with the ShipToId of “ADDR1”.  If ADDR1 already exists for this customer, it will throw an error.  NOTE:  You can leave this field out and the API will auto-generate an ID for you
  • Other fields in the body is data that I wanted to save in the SOAddress
Curl -x post \
http://yourserver/ctdynamicssl/api/financial/accountsreceivable/customer/c300/address \
-H 'accept: application/json' \
-H 'authorization: Basic YOURAUTHCODEHERE' \
-H 'Content-Type: application/json' \
-H 'CpnyID: YOURCPNYIDHERE' \
-H 'SiteID: YOURSITEIDHERE' \
-H 'cache-control: no-cache' \
-d '{
"CustId": "CUSTID1",
"ShipToId": "ADDR1",
"Notes": "TEST",
"resultCode": 0,
"Addr1": "222 Smith Street",
"Addr2": "#18",
"Attn": "Marvin"
}'

Updating an existing SOAddress record

Now, lets say you want to edit an existing shipto address.  You would do a patch and make sure that you pass the ShipToId that you want in the URL.  It doesn’t have to be in the body.  In the below example, I am updating the ShipToId of ADDR1 for Customer ID CUSTID1 and only updating the field “Attn” to read “Marvin the Martian.”  PATCH will not update any other fields if they aren’t passed.

Curl -X PATCH \
http://yourserver/ctDynamicsSL/api/financial/accountsReceivable/customer/CUSTID1/address/ADDR1 \
-h 'accept: application/json' \
-h 'authorization: Basic YOURAUTHCODEHERE' \
-H 'Content-Type: application/json' \
-H 'CpnyID: YOURCPNYIDHERE' \
-H 'SiteID: YOURSITEIDHERE' \
-H 'cache-control: no-cache' \
-d '{
"CustId": "CUSTID1",
"Attn": "Marvin the Martian"
}'
How to call a custom SQL call using the Catalina API for Dynamics SL

How to call a custom SQL call using the Catalina API for Dynamics SL

How many times have you wanted to just make a SQL call from an app, website, or other system, but you don’t have direct access to a SQL connection or other easy method to call SQL?

Well the Catalina API for Dynamics SL has a secure way for you to make SQL calls over to your SL database using the Catalina common.asmx web service call.

Below is a quick tutorial on how to do this using .NET and the Catalina API for Dynamics SL (SOAP calls) Continue reading “How to call a custom SQL call using the Catalina API for Dynamics SL”

CTAPI – DEFAULTS & VALIDATIONS

CTAPI – DEFAULTS & VALIDATIONS

Catalina Technologies API for Dynamics SL allows you to create your own custom defaults and validations when sending data into SL through the API.

How to set Custom Defaults and Validations for web services in CTAPI. As of RELEASE builds post 2017/1/1, you are now able to overwrite defaults and validations in two single files. This is now the preferred place to make customizations, as it will avoid overwriting changes with new release builds of CTAPI.

The file for Defaults customizations is named: custom.default.ctDynamicsSL.xml and is located in your DEFAULTCONFIGDIRECTORY. (Path defined in your DSLCONFIGFILE, default: c:\inetpub\xctFiles\config\)
The file for Validations customizations is named: custom.validate.ctDynamicsSL.xml and is located in your VALIDATIONCONFIGDIRECTORY. (Path defined in your DSLCONFIGFILE, default: c:\inetpub\xctFiles\config\)
All customizations for defaults and validations for all CTAPI web services are contained in these two files.
Definition of the custom.default.ctDynamicsSL.xml file:

capture1

ID: (The field to set)
1. inItem – Always used to represent the Table/Object being defaulted. (not the field)
DEFAULTTYPE: (TEXT, PROC, CODE)
1. TEXT – sets the field value to the value listed in this xml element.
2 PROC – sets the field value to the value returned by the stored procedure listed in the xml element value. (optional: PARMS attribute listing stored procedure parameters)
3. CODE – sets the field value to the value returned by performing an eval on the code listed in the xml element value.

PARMS: (An optional, comma-delimitated list of parameters used for PROC Type defaults)
1. Variables from the inItem object that match stored procedure variable names.
e.g.: PARMS=’inItem.Status’
2. Rename an inItem object variable to a different stored procedure variable name.
e.g.: PARMS=’VendStatus=inItem.Status’
3. Hardcoded Stored procedure variable values.
e.g.: PARMS=’Active=1’

Definition of the custom.validate.ctDynamicsSL.xml file:

capture2

ID: (The field to set)
1. inItem – Always used to represent the Table/Object being defaulted. (not the field)
VALIDATETYPE: (LIST, PROC, NUMBERRANGE, DATERANGE, CODE)
1. LIST – a comma delimitated list of text values that are valid.
2. PROC – validates based on returnValue returned by the stored procedure listed in the xml element value. (optional: PARMS attribute listing stored procedure parameters)
3. NUMBERRANGE – a comma delimitated range of doubles.
e.g.: 1,5
4. DATERANGE – a comma delimitated range of dates.
e.g.: 1/1/2016,1/1/2019
5. CODE – validates the Boolean returned by performing an eval on the code listed in the xml element value.

PARMS: (An optional, comma-delimitated list of parameters used for PROC Type validations)
6. Variables from the inItem object that match stored procedure variable names.
e.g.: PARMS=’inItem.Status’
7. Rename an inItem object variable to a different stored procedure variable name.
e.g.: PARMS=’VendStatus=inItem.Status’
8. Hardcoded Stored procedure variable values.
e.g.: PARMS=’Active=1’