The create voucher flag cannot be changed once the document has been changed

When creating a purchase order using Catalina’s API for Dynamics SL and you get the following error:

“The create voucher flag cannot be changed once the document has been changed”

This is getting fired off because of a business rule violation. There is a work around. If you look in your DSLCONFIGFILE.xml, you should look for the following.

<CONFIGITEM ID='DISABLECREATEADONUPDATE' ENCRYPTED='False' COMMENTS=''>TRUE</CONFIGITEM>

Make sure that the value of that CONFIGITEM is “TRUE”. If you don’t have this CONFIGITEM, add it to the site you are using via the SiteID. If you still have the problem after making this change, contact Catalina support to see if there is an update for your version of the API.


Solving Problem with Retrieving Data in REST with a Period in the ID

What happens if you are using Catalina’s API for Dynamics SL and your ID you are searching for (example a CustID, Vendor ID, etc) has a period in it? It will fail with standard installation. This is because the .NET web application is looking for a period in the final parameter so that it can route.

You will get a return that looks something like this with a 404 status and HTML coming back:

This can be solved by changing the web.config.

NOTE: if you make this change, then you wont be able to run SOAP and REST in the same application. SOAP will stop working and you would need to install a separate instance with it’s won web.config to make this work.

If you look in the Web.config, you will see the following line:

<add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="*" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />

NOTE how the path=”*.” Has a period in it.  Remove it and make it look like this:

<add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="*" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />

This will break SOAP.  But should work.  I created a vendor with a vendor ID of B.WHARTON and then ran this:

curl --location --request GET 'http://catalina.local/ctDynamicsSL/api/financial/accountsPayable/vendor/B.WHARTON' \
--header 'Accept: text/html' \
--header 'Authorization: Basic MY_AUTH_HERE' \
--header 'CpnyID: 0060' \
--header 'SiteID: DEFAULT'

This worked fine:


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


Catalina Technology Recommends EVO Merchant Services

  1. EVO merchant services integrates seamlessly to PayFabric + QuickCollect to provide you a secure and convenient PCI compliant service without the need of a 3rd party gateway provider.
  2. Free Cost-Savings Analysis of your merchant services: As your trusted advisor and expert in payment processing, EVO is happy to provide you a complimentary Summary of Savings report.
  3. Simply provide us the last 3 months of your merchant service statements, and we’ll return to you a Summary of Savings report. This enables you to see the cost-savings benefit that you’ll achieve on your credit card payment processing fees.

EVO B2B Merchant Services is the only business payments solution with Interchange Management® technology that automates cost reduction on transactions for the lowest overall cost on payment acceptance – every time. This technology identifies cost reduction opportunities in the lowest interchange rates the card brands (Visa, MasterCard, Discover and American Express) apply to each payment transaction.

  • A major advantage of doing business with EVO B2B is our Level 2 and 3 processing solutions, which ensure the best rate for every transaction whether through terminals or card not present scenario.
  • Our system automates your client’s transactions to qualify for the lowest rate possible based on card type and transaction parameters, providing significant cost-saving on processing fees. We identify payment solutions and process improvements, which result in time savings and cost reduction, increasing cash flow back to your bottom line.
  • EVO removes the need for using a 3rd party gateway, which eliminates these unnecessary fees.

You can get more information about QuickCollect by looking at the category on our blog: http://blog.catalinatechnology.com/category/catalina-technology-applications/quickcollect


Setting Identity for Windows Auth For Catalina API

If you need to set the authentication for Catalina’s API for Windows Auth to communicate to the SQL Server:

Editing the DSLCONFIGFile connection Strings

First you will need to update your connection strings in DSLCONFIGFILE.xml. You can see this from this blog post:

http://blog.catalinatechnology.com/2020/09/catalina-xml-configuration-file-editor-ctconfigeditor/

You will then need to change your sql connection strings

Which Connection StringWhat to Add
​.NET Connection String;Trusted_Connection=True;
​ODBC Connection String;Integrated Security=SSPI;

For both of these strings, you would remove the username and password from the strings and add the trusted_connection=True for the .NET connection string. And you would add the Integrated Security=SSPI for the ODBC connection string.

Configure the IIS Application Pool for the Identity

Next, you would go into IIS manager and click on Advanced Settings, look at “Identity” and click on the 3 dots button.

Then choose the “Custom Account” radio button and press the “Set” button.

Another popup will be displayed and you are then able to put in the username (replace the example below with your domain\username) and password. After you press OK, the system will tell you whether it is valid or not. NOTE: You will need to make sure that the user has access to the SQL objects in SQL server.


Shopify Integration to Dynamics SL

Shopify Integration with Dynamics SL

Many companies don’t want to go through the effort of building a customer eCommerce system to sell online. There are many cloud based eCommerce systems that they can use to minimize support, hosting, and development time. Catalina has integrated with many of these such as: Shopify, Magento, BigCommerce, WooCommerce, Square, and others.

One of the most popular eCommerce systems is Shopify. Catalina’s Dynamics SL integration to Shopify includes:

  • Order Creation: When an order is created in Shopify, it is automatically transferred to Dynamics SL and created as a Sales order in SL.
  • Order Acknowledgement: When an order is created in Dynamics SL, Catalina will update Shopify with the SL order number and other information.
  • Shipper Notification: When an order has shipped (partial or complete), Catalina will send shipper information to Shopify notifying which items have shipped and any tracking numbers that were created.
  • Order Complete: Once an order is completed, Catalina will notify Shopify that the order is completed so that Shopify can then archive that order.
  • Inventory updating: Catalina can send inventory information to Shopify which will allow Shopify to know quantity on hand, backorders, out of stock, and other information regarding products for sale on the site. Catalina can also add or remove products from the shopping cart site based on inventory levels and custom rules.
  • Custom integration points: There are many integration points in Shopify that can be utilized such as synchronizing of customers, logins, invoices, pricing, and more.

Syncing Tide Data to a Garmin Instinct Watch

We aren’t just business here on the blog. We often work on other projects for fun and to keep our programming skills tight. This one is to help us predict the best time to go surfing.

If anybody has the standard Garmin Instinct watch and wants Tide information on their watch is out of luck since Garmin wants you to spend twice as much to buy the fancy expensive Surf edition watch.

There is kinda a workaround by importing data into a Google Calendar with data from NOAA that will schedule events for the predicted tides. So, I decided to write a .NET Core app that goes out to NOAA and pulls back tide predictions, for a station ID that you specify, and a date range to get the predictions. It will then write that information to a CSV file that you can upload to a google map and then sync to your watch.

The .NET Core app will run on most any machine that has .NET Core installed (Windows, Mac, Linux). It will allow you to pass parameters for beginDate, endDate, station ID (from NOAA) and a filename to output the CSV to. What I did is just run it from 1/1/2021 through 12/31/2021 so that I can populate data for the entire year.

You can get my code on Github here: dafoink/tidesToGoogleAPI (github.com)

You can find out a NOAA stationID closest to you by going here: CO-OPS Map – NOAA Tides & Currents

Example, I live on Cape Hatteras, NC. My Station ID is 8654467


Use PowerScript to Manage API Keys in Catalina’s RESTful API for Dynamics SL

I did a demo on how to manage API Keys, for our RESTful API for Dynamics SL, using PowerScript. We are rolling out PowerScript management and installation tools for our products. Please contact us (sales@catalinatechnology.com) if you are interested in receiving an early release.

Usage:

ApiKeyManager.ps1 -apikey <APIUsername> -sitekey <SiteKey> -xctfiles <xctfilesLocation> -apipass <password> -addapisites <SITE1,SITE2,SITE3> -delapisites <SITE1,SITE2,SITE3> -h -delete -list

Where

  • apikey: API Key to manage
  • sitekey: The encryption key used to encrypt connection strings and configurations
  • xctfiles: Root location of your xctfiles
  • addapisites: Comma Delmited list of sites to add to an API Key. Ex: ‘SITE1,SITE2,SITE3’
  • delapisites: Comma Delmited list of sites to remove from an API Key. Ex: ‘SITE1,SITE2,SITE3’
  • h: Help
  • delete: Will delete the key passed.
  • list: Will list the APIKeys. If a SiteID is passed, it will only list what was passed. If no siteID was passed (ex. ApiKeyManager.ps1 -list) it will list all APIKeys.

Examples

If you want to get a listing, you would enter: ./ApiKeyManager.ps1 -h

Passing the -list parameter will get you something similar to this

If you want to create a new API Key with the username = “APIKEY1”, password = “Passw0rd1”, give access to 3 sites (“TEST”, “LIVE”, “DEVEL”), your SiteKey (encryption key) is “1234567”, and the location of your xctfiles is c:\inetpub\xctFiles, you would enter the below:

./ApiKeyManager.ps1 -apikey 'APIKEY1' -sitekey '1234567' -apipass 'Passw0rd1' -addapisites 'TEST,LIVE,DEVEL' -xctfiles 'c:\inetpub\xctFiles'

If you wanted to delete the Site “TEST” from user “APIKEY” you would do something like this

./ApiKeyManager.ps1 -apikey 'APIKEY1' -sitekey '1234567' -delapisites 'TEST' -xctfiles 'c:\inetpub\xctFiles'

If you want to delete the API Key ‘APIKEY1’, you would enter something like this:

./ApiKeyManager.ps1 -apikey 'APIKEY1' -sitekey '1234567' -delete -xctfiles 'c:\inetpub\xctFiles'


Connect an Ambient Weather Station to ThingsBoard

At Catalina Technology, we are “Weather Bugs.” Living on Hatteras Island, we get our fair share of weather so we like to keep track of it through various IOT devices and weather stations. Since we collect a lot of data, using a tool like ThingsBoard to retrieve the data and build rules against the data coming in (alarms to tell us to close the windows when it is raining) and dashboards.

This is a demo on how you can connect an Ambient weather station to thingsboard.

First, you need to make sure you have a customer created. You can go to the customers hierarchy and either make sure you have an existing one or create a new one.

Since I have a fresh ThingsBoard site, I am just going to create a new customer called “Test Customer” and save it.

After you have a customer, click on that customer (in the customer hierarchy) and click on the Attributes Tab. What we are going to want to do is store our secret API and Application keys given to us from Ambient.

I am going to create an attribute called AMBIENTAppKey. This will be used later on when we make the API call to Ambient’s RESTful API.

I also did the same for the API key and created an attribute named AMBIENTAPIKey (NOTE: I have blurred out my keys so that someone doesnt gain access to my weather station)

Next, expand your customer and then go into the asset groups and click on All. We are going to add an asset for the weather station.

For lack of a better name, I just named my weather station asset “My Weather Station” and gave it an asset type of WeatherStation.

Now we are going to write the actual rules. Go to “Rule Chains”

We are going to create a new rule chain.

Since this rule chain is going to be retrieving data from Ambient’s RESTful API and then tying it to the asset we created, I am just going to name this “Retrieve Weather Information”

Once the rule is created, click on it and then click on the “Open rule chain” button.

Now we are going to use the “Generator” rule node. Just simply drag it out to the work surface.

What a generator rule node does is allow us to create a timer event against a particular asset or other entity in the system. In our case, we are going to just point to the Asset “My Weather Station”. And we are going to change the guts of the code to just use an empty msg.

Here is the code so that you can cut and paste

var msg = {};
var metadata = { data: 40 };
var msgType = "POST_TELEMETRY_REQUEST";

return { msg: msg, metadata: metadata, msgType: msgType };

Next, we want to get access to the customer attributes (AMBIENTAPIKey and AMBIENTAppKey) so that we can use them. Just drag the customer attributes out

A popup will be shown. Enter the below. What we are doing is taking the source attribute (tied to the customer) and then making it a variable that we can use. You could change the name of the attribute if you want. But it is just easier to keep it the same. So, make the Target attribute names the same as the Source attribute names.

Once you save the above, you will then want to link the generator node to the customer attributes. Just grab the little circle on the right of the generateor node and drag it to the left of the customer attributes node.

It will ask you what the link should be. We want to go to the next step if there is a success.

Next, drag out the rest api call node to the work surface so that we can then call Ambient’s API.

A popup will be shown. I just named this “Rest API Call” and then entered the endpoint for the Ambient API. NOTE: you will see how I replaced the actual key with a customer attribute. Also NOTE that this is a “GET” Request method.

Below is what the URL looks like with the attribute key variables embedded. What the Rest API Call node will do when it executes is replace those variables with the values stored under the customer’s attributes with the same name.

https://api.ambientweather.net/v1/devices?applicationKey=${AMBIENTAppKey}&apiKey=${AMBIENTAPIKey}

Now link the get customer attributes node to the Rest API Call node

And link it for a success.

Now we are going to write some transformation script. Search for the script node. Make sure you choose the blue one for transformation.

What we are now going to do is transform the data that comes back from Ambient and form it into an object that makes sense to save as a time series.

Below is the script so that you can cut and paste.

var returnMsg = {
    "dateutc": msg[0].lastData.dateutc,
    "winddir": msg[0].lastData.winddir,
    "windspeedmph": msg[0].lastData.windspeedmph,
    "windgustmph": msg[0].lastData.windgustmph,
    "maxdailygust": msg[0].lastData.maxdailygust,
    "tempf": msg[0].lastData.tempf,
    "hourlyrainin": msg[0].lastData.hourlyrainin,
    "eventrainin": msg[0].lastData.eventrainin,
    "dailyrainin": msg[0].lastData.dailyrainin,
    "weeklyrainin": msg[0].lastData.weeklyrainin,
    "monthlyrainin": msg[0].lastData.monthlyrainin,
    "totalrainin": msg[0].lastData.totalrainin,
    "baromrelin": msg[0].lastData.baromrelin,
    "baromabsin": msg[0].lastData.baromabsin,
    "humidity": msg[0].lastData.humidity,
    "tempinf": msg[0].lastData.tempinf,
    "humidityin": msg[0].lastData.humidityin,
    "uv": msg[0].lastData.uv,
    "solarradiation": msg[0].lastData.solarradiation,
    "feelsLike": msg[0].lastData.feelsLike,
    "dewPoint": msg[0].lastData.dewPoint,
    "feelsLikein": msg[0].lastData.feelsLikein,
    "dewPointin": msg[0].lastData.dewPointin,
    "lastRain": msg[0].lastData.lastRain,
    "tz": msg[0].lastData.tz,
    "date": msg[0].lastData.date
};

return {msg: returnMsg, metadata: metadata, msgType: msgType};

And link the Rest API Call node to the transformation script.

Making sure that we do this on success. What this will then do is take the output of the API call and pass it to the script for transformation.

Now, we just want to simply save the transformed data. It will be saved against the asset we are working on.

And link the transformation script to the time series node for success.

This is what the whole rule chain looks like.

Now we want to show this on a dashboard.

Create a new Dashboard. I am just naming it “My Dashboard”

Once the dashboard is created, open it.

Lets add a widget.

Choose the Cards widget bundle.

I am going to use the timeseries table.

You need to link the widget to data. So, what we are going to do is create a new alias to data since we dont have one already.

We are going to give it an alias name. This is just a name we can now use in this dashboard to refer to data. I am going to use a “single entity” type of filter. And I am going to point it to my Weather Station asset.

After you have pressed add to save the alias, you can then use that alias in your datasource. I am going to point to the Entity alias for “Weather Station” and then choose which data variables I want to show on my table.

I choose several variables for temperature, wind direction and speed, etc. You can drag the variables around to change the order in the table. You can also click on the pencil to edit things like header, color, etc.

After I save that, I can then see the data showing up in my timeseries table on my dashboard.

I want to do another one a bit more graphical. So, hit the plus button to add another widget.

Lets create the new widget.

What I did then was choose Analog gauge and choose the temperature gauge and then pointed the tempf field (temperature F) to be displayed.

And now I have a nice gauge. You can add as many things as you want. As well as bring in other data from other devices and assets. We also use water temperature from an ESP32 device out off of our dock to create a fully comprehensive view of weather and conditions without just getting data from one weather station.