Earlier today, I was asked if I could come up with a query that would allow you to bring back line items for a sales order. But create a numbering scheme for the line items that re-start for each new order number. So, example:
You can see below, that ORD1234 has 3 items, indexed 1,2,3. and ORD5555 has 2 line items indexed 1,2
OrdNbr
InvtID
LineIndex
ORD1234
IN82344
1
ORD1234
IN82341
2
ORD1234
IN82344
3
ORD5555
IN089723
1
ORD5555
IN0123998
2
I accomplished this by using the DENSE_RANK().
Here is sample code:
[code language=”sql”]
SELECT DENSE_RANK() OVER (PARTITION BY OrdNbr ORDER BY OrdNbr, LineRef) * 10 AS LineIndex
, OrdNbr
, LineRef
, InvtID
, QtyOrd
FROM SOLine
ORDER BY OrdNbr, LineIndex
[/code]
What I wanted to do in this was to show the order number, lineRef, invtID, and qtyOrd. But I wanted to show the LineIndex in increments of 10 that restarted for every time the OrdNbr changed. I have one of the orders that have multiple line items circled below.
Over the weekend, I connected our home hydroponics system to Catalina Technologies SMS Central messaging system. This system allows you to receive notifications from your sensors as well as even control devices just using SMS messaging. No apps needed. You can even control devices and get notifications from an old school flip phone.
Here is a video I took of doing simple control of lighting and hydroponics pumps just using SMS messaging.
Catalina’s SMS Central allows you to easily dispatch your field techs, salespeople, and others straight from your Dynamics SL without any special mobile apps. All using SMS messaging. Nothing to deploy, works on any phone, and easy to maintain.
Your people in the field can send updates as replies that will get automatically updated in your back office. They can even send pictures, GPS coordinates, and other attachments which will automatically be attached to the service call, quote, invoice, timecard, or any other screen in Dynamics SL.
1. Create a service call, quote, invoice, or any other type of record in Dynamics SL. SMS Central will send an SMS message to the appropriate person in the field.
2. Your person in the field receives the message and can respond. Those responses will automatically update Dynamics SL.
3. The field person can also take pictures, GPS coordinates, or other mobile files and attach them to the service call, quote, invoice, or other screen in Dynamics SL using just an SMS message.
4. That photo, or other attachment, automatically attaches to the appropriate record in Dynamics SL
5. All attachments and SMS communication are also stored in a web portal, allowing your field people to review history without needing any special app. Any browser on any type of device will work!
Most of the time, you have to write code that drops data into an Azure Service Bus Queue. Catalina Technologies Integration Toolkit can simplify this process by allowing you to easily create a webhook that can read data, transform it, and then send it to any message queue you have a valid shared access key for.
The toolkit can also automate reading data from other API’s and then bridge that data to Azure Message Queues as well. It is very customizable and configurable.
Below is a quick demo showing how you can create a webhook, assign security to it, and then transform received data before pushing it to an Azure Service Bus Queue.
So many times, as a programmer, I get a 3rd party solution that asks me to provide them a quick “webhook” so that they can post back data. Normally this requires me to write some type of web API to receive this data.
Catalina’s Integrator Toolkit allows you to quickly create webhooks with a custom endpoint path without having to do any programming.
I did a quick demo on this here
Basically, What we can do is create a custom endpoint and define the path that it will go to. The payload passed can then be pushed through a script and redirected to other API’s, databases, etc. without doing any major programming.
Plus, if you just need to get something up quick, all you have to do is create it in our toolkit and it will just pass back the original data that was posted to it. Great for quick testing of your callback functionality of whatever is calling the webhook.
There are many times where someone out in the field doesn’t have access to a field service app. Or doesn’t need one. SMS is one of the easiest ways for relaying information.
Catalina Technology’s SMS Central now automates communication between systems like help desks, dispatch, CRM, service, and others by giving two-way communication between the technician or sales guy, in the field, and your back-office system.
This means that you can send dispatch SMS notifications and the person receiving those SMS messages can then respond to them by simply replying to the text message. SMS Central will update the back-office system based on the mobile user’s SMS reply. All without any special app.
1. Create a service call in your dispatch or other system
Create a service call, sales opportunity, customer service case, etc. in your back-office system system like you always do.
Assign it to one or more people.
SMS Central will automatically pick this up, from your system, and send as an SMS Text Message to the assigned people.
2. SMS Central then automatically sends an SMS message, from your back-office system, to the assigned person
Send to any phone with SMS (even including old flip phones).
Can be done hands free with Siri, Cortana, or Google Assistant to do voice response of texts!
Customize the messages that are sent out.
Your field personnel can actually respond to the SMS message with customizable acceptable responses like “Yes”, “No”, “In route”, “can’t make it”, or any other response you want.
If the person doesn’t respond to the message, SMS central has routing and escalation capability to send to others.
Responses can even include pictures, files, video, GPS coordinates, or other attachments. SMS Central will save this for you.
3. SMS Replies are automatically routed back to your system!
Your system is automatically updated based on the response that was sent back.
Any additional replies to this SMS text thread will be logged as notes or history in your system.
Attachments such as pictures, files, video, voice, and GPS coordinates will be stored and tied to the service call.
Often times, I want to send an MQTT message to a device so that it can automatically do something on a timed basis. Example, say I want to turn on my greenhouse lights for my seedlings in the morning, at a certain time, and then turn them off in the evening at a certain time. If you are using Adafruit, it has a trigger that wakes up every so many minutes, hours, days, weeks. But, I want to have things to trigger at the same time every day.
I could use cron on one of my linux boxes. But, in my case, I wanted to use a windows box to do the time triggering. An easy way to do this is to use Windows Task Scheduler that comes with Windows.
Here is a quick project that I did to control lighting for our seedlings. I wanted something that could be controlled via MQTT and then managed in Thingsboard. On this initial prototype, I am using Adafruit’s IOT http://io.adafruit.com. It works pretty well, but is limited in some of the features that I want (like scheduling events on a timed basis).
The components of the project are:
NodeMCU ESP8266 module. I am developing in c++
Visual Micro plugin for Visual Studio. Much easier for debugging and features than just the straight Arduino IDE
IOT Relay (See on Amazon): This is an enclosed relay for both AC and DC switching. And it has a simple port that allows you to control from a microcontroller.
There are many times where someone out in the field doesn’t have access to a field service app. Or doesn’t need one. SMS is one of the easiest ways for relaying information.
Catalina Technology’s SMS Central now automates communication between systems like help desks, dispatch, CRM, service, and others by giving two-way communication between the technician or sales guy, in the field, and your back-office system.
This means that you can send dispatch SMS notifications and the person receiving those SMS messages can then respond to them by simply replying to the text message. SMS Central will update the back-office system based on the mobile user’s SMS reply. All without any special app.
1. Create a service call in your dispatch or other system
Create a service call, sales opportunity, customer service case, etc. in your back-office system system like you always do.
Assign it to one or more people.
SMS Central will automatically pick this up, from your system, and send as an SMS Text Message to the assigned people.
2. SMS Central then automatically sends an SMS message, from your back-office system, to the assigned person
Send to any phone with SMS (even including old flip phones).
Can be done hands free with Siri, Cortana, or Google Assistant to do voice response of texts!
Customize the messages that are sent out.
Your field personnel can actually respond to the SMS message with customizable acceptable responses like “Yes”, “No”, “In route”, “can’t make it”, or any other response you want.
If the person doesn’t respond to the message, SMS central has routing and escalation capability to send to others.
Responses can even include pictures, files, video, GPS coordinates, or other attachments. SMS Central will save this for you.
3. SMS Replies are automatically routed back to your system!
Your system is automatically updated based on the response that was sent back.
Any additional replies to this SMS text thread will be logged as notes or history in your system.
Attachments such as pictures, files, video, voice, and GPS coordinates will be stored and tied to the service call.
Hurricane Irma may have put a damper on things for the DSLUG conference in Tampa. But Catalina did their Deep Dive presentation ONLINE. This is a recording of that deep dive in case you missed it.
So many times, as a programmer, I get a 3rd party solution that asks me to provide them a quick “webhook” so that they can post back data. Normally this requires me to write some type of web API to receive this data.
