How to add a Quick Query in SL without using Quick Query (for Catalina’s API)

Ok, so using Catalina’s API for Dynamics SL makes it easy to get data out of SL through it’s quick query endpoint (/ctDynamicsSL/api/quickQuery). But what if you really don’t actually use QuickQuery in SL. But you still want to use it in Catalina’s API? Easy, just create your view and then add a reference to it in QVCatalog table in your System Database.

Step 1: Creating the View

What I first am going to do is create a view in my Application Database. This will be a simple view that will retrieve customers. And only retrieve the CustID and Customer Name. Below is the SQL code to create my view named QQ_Brian.

/****** Object:  View [dbo].[QQ_Brian]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[QQ_Brian]
AS
SELECT	
     CustId AS [Customer ID],  Name AS [Customer Name]
FROM	Customer with (nolock)

GO

Step 2: Create a Reference to the View in QVCatalog

Next step is to insert a record into the QVCatalog table to reference the view. This table will be in your System Database. Below is what my insert looked like

INSERT INTO QVCatalog
(SQLView,BaseQueryView,QueryViewName,Module,Number,ViewDescription,ViewFilter,ViewSort,ColumnsRemovedMoved,DrillPrograms,VisibilityType,Visibility,SystemDatabase,CompanyColumn,CompanyParms,CreatedBy)
VALUES
(
	'QQ_Brian',
	'QQ_Brian',
	'QQ_Brian',
	'01',
	'QQBRIAN',
	'Your Description Here',
	'<criteria><or /></criteria>',
	'',
	'',
	'',
	0,
	'',
	0,
	'',
	0,
	'[DynamicsSL]'
)

Looking at the above insert statement, you see where I am using the name of my view (QQ_Brian) for SQLView, BaseQueryView, and QueryViewName. I am also using QQBRIAN as my number. You would change these values to what your view name is. I also entered a description (“Your Description Here”). Set that value to something that will allow you to remember what this view does.

Testing it!!!

Now all you have to do is test it. here is some curl code that shows you what I did to test my view in the Catalina’s API for SL, using Postman and our RESTful API for SL.

curl --location --request POST 'http://yourServerHere/ctDynamicsSL/api/quickQuery/QQ_Brian' \
--header 'Accept: application/json' \
--header 'Authorization: Basic YOURAUTHHERE' \
--header 'CpnyID: YOURCPNYHERE' \
--header 'SiteID: YOURSITEIDHERE' \
--header 'Content-Type: text/plain' \
--data-raw '{
    "filters":[
        {
        }
    ]
}'

Because you aren’t adding any filtering, the above curl will bring all records back. You can try this in Postman like below:

If you want to get fancier, you can add some filtering like this to limit the return (you can see more information about filtering and using Catalina’s API here: http://blog.catalinatechnology.com/2019/03/tips-tricks-on-using-catalinas-quick-query-api-to-get-the-data-you-want-out-of-a-sql-database/)

Below is code to show how you can use your custom QuickQuery view and filter it by Customer ID (CustID):

curl --location --request POST 'http://yourServerHere/ctDynamicsSL/api/quickQuery/QQ_Brian' \
--header 'Accept: application/json' \
--header 'Authorization: Basic YOURAUTHHERE' \
--header 'CpnyID: YOURCPNYHERE' \
--header 'SiteID: YOURSITEIDHERE' \
--header 'Content-Type: text/plain' \
--data-raw '{
    "filters":[
        {
            "name": "Customer ID",
            "value": "C300",
            "Comparison": "="
        }
    ]
}'

The above curl code will bring back all records that have the “Customer ID” field equal to “C300” (in my case, There Can be Only One). You can see what it would look like in Postman here:


Take Payments in Dynamics SL for Easy Collection

The easier you make it for your customers to pay, the easier you can collect.

SL QuickCollect is payment solution for Dynamics SL that allows you to email or SMS message out a simple link to your customer as a request for payment.  The customer can then click on the link and are sent to a PCI compliant payment page without any portal login required.  Everything integrated directly with Dynamics SL.

You can see a general demo here:

More information can be found on our blog (including how you can customize and develop applications using SL QuickCollect):

http://blog.catalinatechnology.com/category/catalina-technology-applications/quickcollect


Advanced Rules Based Integration with Catalina API for SL

Catalina’s API for Dynamics SL can be used for many things. We can retrieve all types of data from SL. We can also save transactions to SL. In this demo I did for several folks, I am taking sensor data and pushing it through an advanced rules based engine to create Service Calls in Dynamics SL.

While this demo highlights Dynamics SL, we could just as easily use these same types of rules based development to integrate with other systems like CRM, helpdesk, field service, etc. We can monitor all types of things like temperature, barometric pressure, vibrations (helpful if you an HVAC company and need to monitor your customer’s properties for problems), humidity, ambient light, flooding, and more.



SL Quick Pay tips and tricks

How to Custom Call Stored Procedures with REST that have Customizable XML Parameters

Many of Catalina Technology’s stored procedures accept a field called @parms that is an XML field for configurable parameters to be passed. An example of this is below.

You can see that there is a parameter getting passed called “@parms”. In that is a XML string that contains one or more parameter. In this case, there is a single parameter called “ADDRID” and it is getting passed the value of “C%” so that it can return all addresses that start with a “C”

exec xct_spDSLGetAddresses @currentPageNumber=N'0',@pageSize=N'500',
@parms=N'<nameValuePairs>
    <key name="ADDRID" value="C%"/>
</nameValuePairs>'

To call this RESTfully, you need to HTML escape the XML string so that things like double quotes (“) will not cause the JSON to break.

Below is an example of how we would call the custom stored procedure above, but call it through the Catalina RESTful API.

curl --location --request POST 'http://YOURSERVER/ctDynamicsSL/api/customSQL/xct_spDSLGetAddresses' \
--header 'Content-Type: application/json' \
--header 'Accept: application/json' \
--header 'Authorization: Basic YOURAUTH' \
--header 'SiteID: YOURSITEID' \
--header 'CpnyID: YOURCPNYID' \
--data-raw '{ 
    "parameters": 
    [
        { 
        "name": "currentPageNumber", "value": "0"
        },
        { 
        "name": "pageSize", "value": "0"
        },
        { 
        "name": "parms", "value": "<nameValuePairs><key name=\"ADDRID\" value=\"C%\"/></nameValuePairs>"
        } 
    ]
}'