JSON to X++ Classes the easy way

Calling API’s that expect and return JSON from within X++ has become fairly routine and simple with the release of Dynamics 365 for Operations. Recently we moved a module written in C# that resided as external DLL’s within AX, to an Azure-based API that talks JSON via REST. Calling the API was fairly straightforward but manipulating the JSON between X++ and the API seemed like a lot of manual work requiring get/set operations for each property.

Assume you have a class in X++ containing a number of properties, similar to this one below.

class PFLPayrollResultsRequest
{
    str customerID;
    str appKey;
    str legalEntity;
    str payRunNumber;
 
    public str CustomerID(str _customerID = customerID)
    {
        customerID = _customerID;
        return customerID;
    }
 
    public str AppKey(str _appKey = appKey)
    {
        appKey = _appKey;
        return appKey;
    }
 
    public str LegalEntity(str _legalEntity = legalEntity)
    {
        legalEntity = _legalEntity;
        return legalEntity;
    }
 
    public str PayRunNumber(str _payRunNumber = payRunNumber)
    {
        payRunNumber = _payRunNumber;
        return payRunNumber;
    }
}

 

This class might be used for data retrieval and storage within X++, and let’s assume at some point we want to send the state to an external API using JSON. At first glance it seems that we would have to access each individual property and retrieve it from the class, build the JSON string up from that, and then submit that across the wire. The NewtonSoft library gives us a fair bit of methods to work with, however that is external to X++, and NewtonSoft does not understand X++ classes. When the API call returns, it might contain thousands of records which then have to be loaded into X++ classes, property by property.

The solution lies in using the FormJsonSerializer class. Simply decorate your X++ class with a number of attributes (DataContractAttribute, DataMemberAttribute) as shown below:

[DataContractAttribute]
class PFLPayrollResultsRequest
{
    str customerID;
    str appKey;
    str legalEntity;
    str payRunNumber;
 
    [DataMemberAttribute]
    public str CustomerID(str _customerID = customerID)
    {
        customerID = _customerID;
        return customerID;
    }
 
    [DataMemberAttribute]
    public str AppKey(str _appKey = appKey)
    {
        appKey = _appKey;
        return appKey;
    }
 
    [DataMemberAttribute]
    public str LegalEntity(str _legalEntity = legalEntity)
    {
        legalEntity = _legalEntity;
        return legalEntity;
    }
 
    [DataMemberAttribute]
    public str PayRunNumber(str _payRunNumber = payRunNumber)
    {
        payRunNumber = _payRunNumber;
        return payRunNumber;
    }
}

 

Now we can serialize this class to a JSON string for transfer, and also load an X++ class with the JSON result set returned. A quick example is shown below, in X++.

    public static void GetTempTrans()
    {
        System.Net.WebClient webClient;
        System.Text.UTF8Encoding encoder;
       
        try
        {
            PFLPayrollResultsRequest request = new PFLPayrollResultsRequest(); - this is the request class we convert to JSON and send to the API
            request.AppKey("232B8D90-7C3BF92DEA9F");
            request.CustomerID("27C2C06F8C2737");
            request.LegalEntity("AUP");
            request.PayRunNumber("12345");
 
            webClient = new System.Net.WebClient();
            System.Net.WebHeaderCollection headers = webClient.Headers;
            headers.Add("Content-Type", "application/json");
            encoder = new System.Text.UTF8Encoding();
            str json = FormJsonSerializer::serializeClass(request);// use this AX helper class (FormJsonSerializer) to convert to JSON here
            System.Byte[] encodedBytes = encoder.GetBytes(json);
            System.Byte[] response = webClient.UploadData("https://www.myapi.com/api/Engine/Post/TemporaryResults", encodedBytes);
            str jsonResponse = webClient.Encoding.GetString(response); 
            // deserialize result returned from API here using FormJsonSerializer::deserializeClass();
        }
        catch
        {
        } 
    }

API to CDM using Flow in a PowerApp

There have been some interesting developments since late last year around Microsoft Dynamics with the addition of Flow, PowerApps and the Common Data Model. It has been hard for ISV’s to keep up to date, considering we’ve gone from “AX 7” to Microsoft Dynamics 365 for Operations and in the future the introduction of the Common Data Model, bound to shake things up even more, all in the space of less than twelve months.

As an ISV with a payroll module that has been tightly embedded into AX since 2009 we’ve not been immune to these changes and considerable thought and preparation has gone into future-proofing our product. As our payroll engine have always been .NET based, this has allowed us some flexibility in terms of portability. Dynamics, or AX, has always been just a UI for us, something that facilitates interaction between the user and our payroll engine, while utilizing the storage of AX in the same vein.

This all changed over December last year when we decoupled our engine from AX and moved it to Azure as a proper App Service with an API, moving storage from AX to Azure Table Storage at the same time, delivering flexibility, scalability and a fourfold performance increase over our standard “fully embedded” AX version. In the future, Dynamics properly becomes no more than a UI for us, and our interaction with Dynamics will be via the Common Data Model to retrieve and store key data elements (mostly HCM fields) as we need it during processing.

To that end, it is important to ensure our API-based product is architected well enough to take advantage of, and provide services via PowerApps, Power BI and is able to be used in Microsoft Flow. The steps below is very much exploratory, as this is my first experience in building a PowerApp and using Flow, so don’t expect miracles in this post.

So the aim here is to utilize our payroll engine via its API hosted in Azure to fetch the results of a pay run, and then populate that into a custom entity in CDM, potentially then being used in Dynamics in the future. First things first, let’s get the call to the API worked out.

We’ll start with a basic Flow which calls the API via HTTP, retrieves the pay run results, parses the resulting JSON and then save that into a CSV somewhere in OneDrive for Business. All of this is possible using Flow using the fairly simple (if somewhat buggy) web-based UI.

The overall result is shown below and we’ll go into each step in more detail this post.

FlowDesign

So here we have an HTTP service, posting its results to a JSON parser, which in turn transfers the results to a CSV generator, ultimately ending up as a file in the OneDrive for Business service right at the end.

FlowDesign1

The HTTP service requires a number of parameters. First we are doing a POST call, and we’ll enter the URL and method for that call. No headers are required, but our API expects a number of parameters to be supplied via the body, which we’ll hardcode for now and paste as JSON. These include the CustomerID, APP Key, Legal Entity and Pay Run.

FlowDesign1_1

For the JSON parser we need to specify where the JSON is coming from, which is of course the body of the result from our HTTP call, so we will select that.  Now things unfortunately get a lot more painful. We need to supply the JSON schema for the result being returned so that the JSON parser can parse it all properly. So if you are building an API, make sure you have all your specifications ready and sorted.

At this point I’ll go back to the API drawing board and fiddle around to extract the schema by pasting an example result. Flow is smart enough to figure out the schema for us from that. Nice. To create a CSV file from that we simply select that output in the CSV service as shown.

FlowDesign2

The rest is easy, just select the OneDrive for Business service as the next connection, sign-in to supply credentials and specific the folder, file name and the input, which is of course the CSV we created in the previous step.

FlowDesignRun

Let’s go ahead and save our changes, then run this Flow as shown.

FileCreated

Our OneDrive for Business folder shows the file created, and we can open that in Excel to view the results which is spot-on. So that works.

ExcelView

This is all fine, but we really need this data in a CDM entity not a CSV file. So let’s remove the CSV and OneDrive for Business services. Next, create a new custom entity in CDM as shown below.

FlowEntity

Unfortunately, we have to enter each field manually, there is no easy way to import a schema that I can see. It’s simple enough though, we can use the Excel we generated previously and just copy the headers in as field names. We’ll save this new entity as “XalariTrans” and return to fixing up our Flow.

FlowCreateRecordDesign

Drop in a Create Record Service as shown, and supply the database and entity. Each field in the entity needs to be mapped to an element extracted from the JSON in the previous step, and this is made easy with Flow.

Let’s run this. Notice that Flow automatically added a loop around the Create Record service to ensure that all records are inserted automatically.

ParamFinalOutput

The run was successful and we can go back to our custom entity and verify that the data was transferred from the API to CDM as shown below.

FlowDataView

This approach while working, is rather lame. What we really want is to build a set of API calls that can be reused as connections, and for that we need to create a Custom Connection. So under Connections click Custom, and then click Add. We’ll enter a name, description and upload an icon. We also need to provide a Swagger file which is essentially an API specification that details the inputs, outputs, schema, URL, and much more.

Creating a Swagger file from scratch is a study in frustration for first-timers. I ended up using several tools to generate and edit the proper Swagger JSON file and even then, had complaints when loading it into Flow.

So, with the Swagger specification created and selected, we can add this new Connection Service.

Connections

We can now update our Flow by removing the HTTP service and replacing that with our new Connection service as shown below.

ConnectionExecute

This runs fine, however recall that our API calls require parameters in the body of the POST call, including Customer ID and App Key. For now we’ve pasted the body JSON into the Connection as a parameter but that is rather useless, as these are customer-specific. So we need to retrieve these as settings from our PowerApp somehow, pass that to Flow during execution, which in turn will stick that in the body of the POST call. The way to achieve this is to modify our Swagger file for our Custom Connection as shown below:

Swagger

With that done we can update the Custom Connection. When we edit our Flow, notice how those parameters are now available in the Connection as shown below.

AppParamsFlow

As these will come from our PowerApp, we can specify that for each required input parameter. Notice it is in purple, matching the PowerApp at the top to indicate that expectation. So it knows those values need to be provided by the PowerApp. Nice.

So let’s use the PowerApp designer to build a PowerApp that will use our Flow. You’ll need to download the desktop version if you want to call a Flow from within your PowerApp, which is not supported in the online version.

We’ll build a simple app by dropping two text boxes on the form with a single button we rename to “Transfer”. We’ll set the text for the boxes to “DME” and “1” to correspond to the LegalEntity and PayRun parameters we require. This can of course be left blank and edited by end users.

AppParameters

Select our Flow under the Actions tab. This links the Flow with our button’s OnSelect event, the result being when the button is clicked, the Flow will be called. Notice at the top we are passing our parameters to the Flow, being the Customer ID, APP Key, and the contents of the two text boxes, allowing users to change the Legal Entity and Pay Run by editing the values in the text boxes.

AppFinal

That’s it, we can run the PowerApp and hit the Transfer button. After a second or two we can return to our Entity in CDM and view the Data tab, showing that the records was transferred successfully.

A simple exercise indeed, we are still miles from having a professional PowerApp but we’ve managed to figure out a number of things including API calls, using Swagger, building Custom Connections and Flow’s, and passing parameters from a PowerApp to Flow, then to a backend API and storing the results as records in CDM.

 

 

 

 

Dynamics AX RFID using IoT

Don’t you hate it when you need to track someone down in an office building for an urgent message, or to sign some paperwork and you have no idea where they are? In a meeting perhaps, phone switched on silent? In the kitchen taking a break?

So here is a quick and easy worker locator using RFID swipe cards with an IoT board that displays your office layout in Dynamics AX, and pin-points where you can find Waldo right now.

For this project we’ll use a $20 Adafruit HUZZAH ESP8266/WiFi board with an MFC522 RFID reader. Mount the reader in a small plastic casing as shown below. The signal is strong enough to read access cards through the plastic wall.

We’ll add a LiPo rechargeable battery to make this portable as well. Just in case you need to mount one of these as and when required.

IMG_0182

Next we’ll solder the Adafruit board onto some PCB and add a red and green LED to signal “success” or “failure” when a card is touched to the box. The PCB is really just required to add resistors between VCC and the LED’s.

I like reusing stuff between projects so again, mounting the IoT board with headers onto the PCB makes sense. The completed PCB setup is shown below. The edges of the PCB have been trimmed to fit into the box with the rest, and that measures 8cm x 5cm so perfect to mount on a wall.

IMG_0184

Once the RFID reader has been connected and soldered onto the PCB we can stack all of it safely into the box as seen below. The LiPo battery can be recharged via USB and we can get a lot of usage out of it between charges, by putting the ESP8266 WiFi chip into deep sleep mode as often as possible.

IMG_0186

We’ll drill two small holes for the LED’s at the top, connect the battery and make another hole for a USB charger connection. All sorted the finished product is seen below ready for mounting.

IMG_0189

Coding-wise, I’ve used C through the Arduino IDE to make the whole project work. The code for this is below.

#include <SPI.h>
#include <MFRC522.h>
#include <ESP8266WiFi.h>
 
#define RST_PIN         15
#define SS_PIN          2
#define LED_GREEN       4
#define LED_RED         5
 
const char* tempssid = "TempAP"; // use a mobile phone to setup AP using IoTLink.net
const char* temppass = "TempPass";
const char* iotlink = "www.iotlink.net";
bool routed = false;
 
MFRC522 mfrc522(SS_PIN, RST_PIN);
 
void setup() {
  Serial1.begin(115200);
  while(!Serial1){}
 
  Serial.begin(9600);
  SPI.begin();
  mfrc522.PCD_Init();
  pinMode(LED_GREEN, OUTPUT);
  pinMode(LED_RED, OUTPUT);
  digitalWrite(LED_GREEN, LOW);
  digitalWrite(LED_RED, LOW);
 
  WiFi.begin(tempssid, temppass);
  while (WiFi.status() != WL_CONNECTED)
  {
    delay(500);
    Serial.print(".");
  }
}
 
void loop()
{
  if (!routed)
  {
    route();
  }
 
  MFRC522::MIFARE_Key key; // default to FFFFFFFFFFFF
  key.keyByte[0] = 0xFF;
  key.keyByte[1] = 0xFF;
  key.keyByte[2] = 0xFF;
  key.keyByte[3] = 0xFF;
  key.keyByte[4] = 0xFF;
  key.keyByte[5] = 0xFF;
 
  // Loop until card is presented
  if (!mfrc522.PICC_IsNewCardPresent()) {
    return;
  }
 
  if (!mfrc522.PICC_ReadCardSerial())   
  {
    digitalWrite(LED_RED, HIGH);
    delay(1000);
    return;
  }
 
  byte readbuffer1[18];
  byte readbuffer2[18];
  byte block;
  MFRC522::StatusCode status;
  byte len;
  byte sizeread = sizeof(readbuffer1);
 
  block = 0;
  status = mfrc522.PCD_Authenticate(MFRC522::PICC_CMD_MF_AUTH_KEY_A, block, &key, &(mfrc522.uid));
  if (status != MFRC522::STATUS_OK) {
    // failed
    digitalWrite(LED_RED, HIGH);
    delay(1000);
    digitalWrite(LED_RED, LOW);
    return;
  }
  else
  {
    for (int i = 0; i < 18; i++)
    {
      readbuffer1[i] = 0x00;
      readbuffer2[i] = 0x00;
    }
 
    // read worker name or ID from card
    status = mfrc522.MIFARE_Read(1, readbuffer1, &sizeread);
    if (status != MFRC522::STATUS_OK)
    {
      // read failed
      digitalWrite(LED_RED, HIGH);
      delay(1000);
      digitalWrite(LED_RED, LOW);
      return;
    }
    mfrc522.PICC_HaltA();
    mfrc522.PCD_StopCrypto1();
  }
 
  // success
  String workername = String((char *)readbuffer1);
  digitalWrite(LED_GREEN, HIGH);
  delay(1000);
  digitalWrite(LED_GREEN, LOW);
 
  // send to ThingSpeak
  const char* host = "api.thingspeak.com";
  const char* thingspeak_key = "your api key";
 
  WiFiClient client;
  const int httpPort = 80;
  if (!client.connect(host, httpPort))
  {
    return;
  }
 
  String url = "/update?key=";
  url += thingspeak_key;
  url += "&field1=";
  url += workername;
 
  client.print(String("GET ") + url + " HTTP/1.1\r\n" + "Host: " + host + "\r\n" + "Connection: close\r\n\r\n");
  delay(10);
  while(client.available()){
    String line = client.readStringUntil('\r');
  }
}
 
void route()
{
  WiFiClient client;
 
  if (!client.connect(iotlink, 80))
  {
    Serial.println("IoTLink connection failed");
    return;
  }
 
  // uniquely identify our device using the chip id.
  // this id needs to be loaded into IoTLink and mapped to an AP by the customer
  String url = "/route?deviceid=" + String(ESP.getChipId());
  client.print(String("GET ") + url + " HTTP/1.1\r\n" + "Host: " + iotlink + "\r\n" + "Connection: close\r\n\r\n");
  unsigned long timeout = millis();
  while (client.available() == 0) {
    if (millis() - timeout > 5000) {
      Serial.println("IoTLink Timeout !");
      client.stop();
      return;
    }
  }
  String response = "";
  while(client.available()){
    response = client.readStringUntil('\r'); 
  }
  response.remove(0,1); // gobble NL
  String ssidx = getValue(response,',',0);
  String passx = getValue(response,',',1);
  char ssidnew[32] = {0};
  char passnew[32] = {0};
  ssidx.toCharArray(ssidnew, 32);
  passx.toCharArray(passnew, 32);
 
  WiFi.disconnect();
  delay(4000);
  WiFi.begin((char *)ssidnew, (char *)passnew);
  while (WiFi.status() != WL_CONNECTED)
  {
    delay(500);
    Serial.print(".");
  }
  routed = true;
  // optionally store retrieved AP information into EEPROM in case of device reset
  // this avoids having to use a temporary AP again to route to IoTLink
}
 
// Helper
String getValue(String data, char separator, int index)
{
  int found = 0;
  int strIndex[] = {0, -1};
  int maxIndex = data.length()-1;
 
  for(int i=0; i<=maxIndex && found<=index; i++)
  {
    if(data.charAt(i)==separator || i==maxIndex)
    {
        found++;
        strIndex[0] = strIndex[1]+1;
        strIndex[1] = (i == maxIndex) ? i+1 : i;
    }
  }
  return found>index ? data.substring(strIndex[0], strIndex[1]) : "";
}
 

 

First, we use a temporary AP and IoTLink (www.iotlink.net) to connect to the assigned AP for internet access. Once that is routed and connected, we wait for a card swipe. We read the worker details off the card, and send this to ThingSpeak via our WiFi connection.

ThingSpeak supports multiple channels with up to 8 fields per channel. For this example, I used a single channel and assumed each field will correspond to an office or room. Since that is limited, you might want to create a channel per location where you want to mount an RFID reader and just use one single field per channel. This is a prototype project so I took the easy way out.

Once we are satisfied that the electronics work, and swiping a card generates data in ThingSpeak, we can switch to AX 7.

For AX, I created a simple form and an extended control. In the HTML of the control, I load the office layout graphic (directly from my friends at EDrawSoft who kindly gave me permission to use it) and then get the latest data from my ThingSpeak channel using JavaScript.

I can go through each channel and field to determine who has swiped where in the building, and in this example we’ll just look for worker ‘VOS’. Once found, we can highlight where they last swiped in red around that office or room, and display their name at the top. The HTML/JavaScript for AX is shown below.

src="/resources/scripts/FBXRFIDControl.js">
id="FBXRFIDControl">
/>
style="border-width: 1px; border-style: solid; width: 100%; height: 100%; float: left;"> id="thecanvas">
<br /><br /> type="text/javascript"> var clickX = new Array(); var clickY = new Array(); var clickDrag = new Array(); var paint; function InitForm() { var thecanvas = document.getElementById('thecanvas'); thecanvas.width = 700; thecanvas.height = 600; var thecontext = thecanvas.getContext("2d"); var theImage = new Image(); theImage.onload = function () { thecontext.drawImage(this, 0, 0); } theImage.src = "https://www.edrawsoft.com/images/examples/office%20layout%20sample.png"; $.getJSON('https://www.thingspeak.com/channels/yourchannel/feed/last.json?callback=?&offset=0&location=true;key=yourkey', function (data) { var worker = data.field1; if (worker != '') { // someone in office 1, display who it is and highlight this office as 'occupied' var thecanvas = document.getElementById('thecanvas'); var thecontext = thecanvas.getContext("2d"); thecontext.fillStyle = "red"; thecontext.font = "bold 10px Arial"; thecontext.fillText(worker, 30, 30); thecontext.strokeStyle = "Red"; thecontext.lineWidth = 6; thecontext.rect(10, 15, 235, 175); thecontext.stroke(); } }); } </div>

 

Running in AX produces the results shown below. The code needs some work to add drop-downs for selecting worker, or perhaps clicking on a room will display the names of everyone currently swiped into that room. There are many ways to improve on this.

AX7

A nice simple project to combine RFID, IoT and Dynamics AX into an easy to use little project.

IoT Health & Safety in Dynamics AX 7

This is a quick project I put together to explore using small-size IoT modules for practical work, say Health & Safety or Equipment monitoring and reporting. For this post I’ll use the super-compact Cactus Rev 2 which gives me multiple I/O ports with WiFi built-in via an ESP8266 and I will use that to transmit three sensor values to Dynamics AX 7 via ThingSpeak.

The Cactus Rev 2 module is an amazing little device that measures less than 4cm by 2cm and is half a centimeter thick. It can be powered via USB or RAW and runs off <4v which is important as I want to power this using a LiPo battery to make it portable. The Cactus is pretty close to being the best possible module to use for building “wearable” IoT and can be powered using two coin batteries, with the ESP8266 being disabled and enabled as required to conserve power.

To make things a bit more practical, I’ll add a DHT11 temperature and humidity sensor, as well as a light sensor. I can also add an MQ9 sensor to detect various dangerous gasses but since that requires 5v it will need a booster added to the circuit so I’ll pass. It’s the idea that counts, right?

The completed circuit is shown below after soldering everything to a PCB and doing the required wiring, using 1 x Analog and 1 x Digital port all up, so leaving plenty of room for other sensors if required. I tried to keep things small and this measures 5cm x 4cm and about 1cm thick when completed. Keep in mind that sending this to production will drastically reduce the size, but this is only a prototype.

IMG_0173

I’m running this off USB to test, but ran the wiring (top-left) from RAW and GND to the back of the circuit where I can mound a rechargeable LiPo battery, and this will recharge  automatically every time I connect the USB cable. So it’s portable, virtually wearable and can be recharged at your desk.

We’ll use WiFi via the onboard ESP8266 and for that we’ll need an Access Point (AP) and obviously SSID and PASS. I could have opted to use an Arduino Nano with NRF905 instead as well. While it’s a prototype, we want to be production-ready so the sketch includes WiFi routing via a vendor/customer IoT Directory available at www.iotlink.net and I have coded this into the sketch. This means we’ll just need a temporary hardcoded AP during installation (or search for open AP) and IoTLink will take care of the rest.

The sketch code (in C) is shown below, pretty straightforward. We connect to a temporary AP, then using that and IoTLink find the correct customer AP settings in the device directory and re-route. Once connected we can start measuring sensor values and send  that to ThingSpeak to display in a dashboard.

#include <espduino.h>
#include <rest.h>
#include "DHT.h"
 
#define DHTPIN 15
#define DHTTYPE DHT11
DHT dht(DHTPIN, DHTTYPE);

#define PIN_ENABLE_ESP 13

ESP esp(&Serial1, &Serial, PIN_ENABLE_ESP);
REST rest(&esp);
boolean wifiConnected = false;

//IoTLink
boolean routed = false;
#define DEVICE_ID "DEV001" // This unique device ID
#define SSID  "TempSSID" // Default AP used for configuation
#define PASS  "TempPASS" 

void wifiCb(void* response)
{
  uint32_t status;
  RESPONSE res(response);

  if(res.getArgc() == 1) 
  {
    res.popArgs((uint8_t*)&status, 4);
    if(status == STATION_GOT_IP) 
    {
      wifiConnected = true;
    } else {
      wifiConnected = false;
    }  
  }
}

void setup() {
  Serial1.begin(19200);
  Serial.begin(19200);
  delay(1000);
  dht.begin();
  esp.enable();
  delay(500);
  esp.reset();
  delay(500);
  while(!esp.ready());
  if(!rest.begin("www.iotlink.net", 443, true)) // use SSL secure connection
  {
    while(1);
  } 
  esp.wifiCb.attach(&wifiCb);
  esp.wifiConnect(SSID, PASS); 
}

void loop() 
{
  esp.process();
  if(wifiConnected) 
  {
    if (!routed)
    {
      delay(1000);
      Route();
    }
    else
    {
      float h = dht.readHumidity();
      float t = dht.readTemperature();
      int l = analogRead(2);
      char response[266];
      char buff[64];
      char str_hum[6], str_temp[6], str_light[6];
      dtostrf(h, 4, 2, str_hum);
      dtostrf(t, 4, 2, str_temp);
      dtostrf(l, 6, 0, str_light);
      sprintf(buff, "/update?api_key=<your key here>&field1=%s&field2=%s&field3=%s", str_temp, str_hum, str_light);
      rest.get((const char*)buff);

      if(rest.getResponse(response, 266) == HTTP_STATUS_OK)
      {
      }
      delay(5000);
    }
  }
}

bool Route()
{
  char response[60]={','};
  char buff[50]={0};
  char ssid_new[20]={0};
  char pass_new[20]={0};

  sprintf(buff, "/route?deviceid=%s", DEVICE_ID);
  rest.get((const char*)buff);
  if(rest.getResponse(response, 60) == HTTP_STATUS_OK)
  {  
    String sr(response);
    String ssidx = getValue(sr,',',0);
    String passx = getValue(sr,',',1);
    ssidx.toCharArray(ssid_new,20);
    passx.toCharArray(pass_new,20);
    wifiConnected = false;
    routed = true;
    delay(500);
    esp.reset();
    delay(500);
    while(!esp.ready());
    if(!rest.begin("api.thingspeak.com"))
    {
      while(1);
    } 
    esp.wifiCb.attach(&wifiCb);
    esp.wifiConnect(ssid_new, pass_new);
    return true;
  }
  return false;
}

// Helper
String getValue(String data, char separator, int index)
{
  int found = 0;
  int strIndex[] = {0, -1};
  int maxIndex = data.length()-1;

  for(int i=0; i<=maxIndex && found<=index; i++){
    if(data.charAt(i)==separator || i==maxIndex){
        found++;
        strIndex[0] = strIndex[1]+1;
        strIndex[1] = (i == maxIndex) ? i+1 : i;
    }
  }
  return found>index ? data.substring(strIndex[0], strIndex[1]) : "";
}

 

I could have used Azure IoT Hubs and Power BI instead of ThingSpeak, but getting that working is time-consuming (compared to ThingSpeak), requires TSL from what I can gather and the Cactus just does not have the power nor storage capacity (it does support SSL though).

Running our circuit via the Arduino IDE in debug mode shows us it is all working nicely, as can be seen below:

IoTArduino

Dashboards are getting populated too, so the data is streaming in and we can move  this into Dynamics AX 7.

IoTThingSpeak

Say instead of wearable Health & Safety we wanted to use this to monitor a vital piece of equipment on the shop floor instead. For that we’ll create a simple dialog in AX, and add an extended control which will do the data fetching from ThingSpeak and display it on a factory layout diagram.

We start with a basic control, and then modify the HTML to include some JavaScript as shown below. All we are effectively doing is fetching the latest sensor readers from ThingSpeak, as reported by the IoT device. We request the latest sample, essentially.

One thing I am yet to find a workaround for in AX is the ability to utilise a JavaScript timer. This seems to be disabled somehow in AX. A timer would be useful to refresh all samples, say every 15 seconds or so. If someone has figured this out, please let me know.

src="/resources/scripts/FBXRFIDControl.js">
id="FBXRFIDControl">
/>
style="border-width: 1px; border-style: solid; width: 100%; height: 100%; float: left;"> id="thecanvas">
<br /><br /> type="text/javascript"> var clickX = new Array(); var clickY = new Array(); var clickDrag = new Array(); var paint; function InitForm() { var thecanvas = document.getElementById('thecanvas'); thecanvas.width = 700; thecanvas.height = 400; var thecontext = thecanvas.getContext("2d"); var theImage = new Image(); theImage.onload = function () { thecontext.drawImage(this, 0, 0); } theImage.src = "https://www.edrawsoft.com/templates/images/production-pfd.png"; $.getJSON('https://www.thingspeak.com/channels/yourchannel/feed/last.json?callback=?&offset=0&location=true;key=yourkey', function (data) { var temp = data.field1; var hum = data.field2; var light = data.field3; // can change font of text depending on whether light levels are sufficient // in turn, IoT device can adjust office lighting if levels are too high or too low var thecanvas = document.getElementById('thecanvas'); var thecontext = thecanvas.getContext("2d"); thecontext.fillStyle = "blue"; thecontext.font = "bold 10px Arial"; thecontext.fillText(temp + 'C ' + hum + '%', 490, 250); }); } </div>

 

Running this in AX produces the result shown below, with the sensor readings being displayed in blue next to the vital piece of equipment we are monitoring, in this case a motor.

IoTOHS

In terms of practicality it covers a number of potential use-cases:

  • Office environment monitoring
  • Health & Safety monitoring (toxic gasses, working temperature, low-light conditions, high humidity)
  • Add a vibration sensor to monitor potential equipment malfunction

 

 

 

 

Routing WiFi-enabled IoT Devices

While testing various projects using the ESP8266 WiFi chip I found myself wondering about the practicalities of manufacturing and distributing pre-programmed IoT devices to business and consumers alike. Sitting at a laptop connected to your IoT device of choice is relatively painless. You can sketch something up in C that reads sensors and does all kinds of neat stuff, sending that data into the cloud for consumption and analysis, but that all works fine while you’ve got your home or lab WiFi router SSID and Password coded into your sketch.

Once your project goes into manufacturing, that SSID and Password no longer applies, and it has to be configured somehow by the end-user to of course match whatever AP and security they have on-site. Chances are if your consumer IoT device was bulk-manufactured and pre-programmed in say China, there is a less than zero chance it will work out of the box. To complicate matters even more, customers tend to regularly change AP configurations, rendering your IoT device useless unless reconfigured or reprogrammed.

You of course have several options available to bypass this problem including:

  • Require the user to have an open AP for your device to connect to
  • Require the user to have a secure AP with a specific SSID and password available to match what is hardcoded in your device
  • Give the user the ability to reconfigure the device using their own machine and a USB cable, or maybe via Bluetooth
  • Give them a utility for configuration onto SD card, which is then installed in the device if supported

All of the above solutions creates a new set of problems:

  • Security needs to be dumbed down or
  • Painful reconfiguration is required by a specialist during installation or
  • Your IoT manufacturing cost now needs to include SD card and/or Bluetooth, when it will end up using WiFi and already include that in the manufacturing cost
  • Add an LCD screen with configuration buttons and a user manual in multiple languages – as simple as programming a VCR was back in the 80’s

An alternative approach is offered with the IoTLink platform (www.iotlink.net) which I shamelessly promote as it is my platform. With IoTLink, you can utilize a temporary AP (iPhone, Android, pocket WiFi router) on-site, either by a technician or through simple instructions to the end user. There are numerous apps available for both iPhone and Android which allows you to use them as a temporary AP where you can define the SSID and Password.

This “required” SSID and Password can be documented with your shipped device, allowing the user to find a temporary Internet connection, via temporary AP (phone), which then connects to IoTLink where both the device vendor as well as the end user are given the ability to whitelist and configure the IoT device to then link to whatever secure AP is available at the user site, whether at home or in a business scenario. It also allows reconfiguration and can collect a reasonable amount of diagnostic information for both end user and the device vendor.

There is a lot more information available at the IoTLink site, with a number of examples which shows how IoTLink can be incorporated into your project. As a quick example in this post, I will show how to program an ESP8266 hooked up to an Arduino Mega to:

  • Find the first available open AP and connect to it
  • Use it to look up proper secure customer AP information from the IoTLink directory
  • Disconnect from temporary AP and establish a secure connection to the customer AP. This example does not use SSL but IoTLink supports both SSL and open connections (for extremely limited devices or testing scenarios).

 

#include <ESP8266.h>

String SSID;
bool routed = false;

ESP8266 wifi(Serial2);

void setup(void)
{
  Serial2.begin(115200);
  Serial.begin(9600);  
     
  wifi.setOprToStationSoftAP();
}

void loop(void)
{
  if (!routed)
  {
    // find an open AP
    String apList = wifi.getAPList();

    bool haveOpenAP = false;
    if (apList.indexOf("+CWLAP:(0,\"") >=0) // find first open AP
    {
      haveOpenAP = true;
      apList = apList.substring(apList.indexOf("+CWLAP:(0,\"") + 11, apList.length() - (apList.indexOf("+CWLAP:(0,\"") + 11));
      apList = apList.substring(0, apList.indexOf("\""));
      if (wifi.joinAP(apList))
      {
        wifi.disableMUX();
        route();
      }
    }
  }
  
  // your code here

}

// IoTLink
void route(void)
{
  uint8_t buffer[1024] = {0};
  wifi.createTCP("www.iotlink.net", 80);
  char *hello = "GET /route?deviceid=DEV12345 HTTP/1.1\r\nHost: www.iotlink.net\r\nConnection: close\r\n\r\n";
  wifi.send((const uint8_t*)hello, strlen(hello));

  uint32_t len = wifi.recv(buffer, sizeof(buffer), 10000);
  if (len > 0)
  {
    char ssid_new[20]={0};
    char pass_new[20]={0};
    char *content = strstr((char *)buffer, "\r\n\r\n");
    if (content != NULL) content += 4;
    String sr(content);
    String ssidx = getValue(sr,',',0);
    String passx = getValue(sr,',',1);

    ssidx.toCharArray(ssid_new,20);
    passx.toCharArray(pass_new,20);
    wifi.leaveAP();
    delay(1000);
    wifi.joinAP(ssid_new, pass_new);
    routed = true;
  }
  wifi.releaseTCP();
}

// Helper
String getValue(String data, char separator, int index)
{
  int found = 0;
  int strIndex[] = {0, -1};
  int maxIndex = data.length()-1;

  for(int i=0; i<=maxIndex && found<=index; i++)
  {
    if(data.charAt(i)==separator || i==maxIndex)
    {
        found++;
        strIndex[0] = strIndex[1]+1;
        strIndex[1] = (i == maxIndex) ? i+1 : i;
    }
  }
  return found>index ? data.substring(strIndex[0], strIndex[1]) : "";
}

FormsBox for Dynamics AX 7

In my previous post I alluded to the fact that I am in the process of porting a cloud-based forms management system to Microsoft Dynamics AX 7. The goal is to produce a proper fully-integrated ISV module for AX that can be deployed from LCS, with as close to zero configuration required as possible.

The product is still currently in BETA, but I thought it might be a good time to showcase some of the functionality that will be available to AX users to gain some early feedback.

FormsBox, as I said, is a cloud-based forms management system with a load of really cool features. It is hosted in Azure and developed using C# and ASP.NET and utilizes numerous Azure functions such as blob storage and encryption.

The web version of FormsBox can be accessed here: www.formsbox.com

Integration into AX 7 presented a number of challenges, mostly around replacing key functions wrapped into ASP.NET controls with pure JavaScript using the Extensible Control Framework, and in such a way that standard AX best practices are honored. On the positive side, it requires no real implementation or configuration and once installed you simply enter the license key and it is up and running.

To ensure seamless integration with AX, I avoided requiring a dedicated workspace as much as possible by tying in with existing AX forms within ESS (Self-Service), HR and of course document attachments. It should be an extension of AX, not a replacement of what is already available.

A manager will typically get one additional tile added to their self-service workspace as shown below. Here they can get a snapshot of forms that have been sent to them for review and approval, new forms they have received, and any forms that have gone past their expiry date (contracts, for example).

ESSWorkSpace

They also have direct access to the FormsBox library containing over 400 document templates, and of course, they can also upload their own organizational forms for either public or internal-only consumption.

Keep in mind that FormsBox for AX provides a shell around the API and the backend cloud-based system. So all forms are physically stored in the FormsBox cloud, not within AX. So for example, when workers view their own forms, as shown below, these forms are in fact located in FormsBox, not AX. This means all actions performed in the AX version of FormsBox is instantly accessible outside of AX, using the standard FormsBox web platform.

EmployeeFormBrowser

Clicking the Attach button will however bring a copy of that form into AX, and store it in their document attachments, as a PDF document. These can be accessed, viewed and downloaded like any other AX document attachment:

EmployeeAttachment

Within AX the workflow functionality provided by FormsBox is fully supported. This allows forms to be sent from FormsBox to workers within the organization for completion and vice-versa. Forms can be approved or rejected with commentary, or escalated internally for further review.

AXRejectReviewForm

The FormsBox library is accessible from within AX, allowing workers to browse hundreds of free document templates and distribute it internally or externally for completion and signing. Organizations can upload their own documents into the library, and either share these publicly (product disclosure statements as an example) or only internally.

LibraryDistribute

A form sent for completion internally (in AX) is smart enough to extract all fields required for completion from the PDF, giving workers a guide for completion by merely doing data entry.

CompleteFormFields

On devices supporting a pen or mouse (laptop, mobile, tablet) forms can be digitally signed as well, as shown below. This is fully supported in AX 7. Standard annotations in multiple pen colours are also supported.

EmployeeBrowserSignForm

For bulk document mail-merge and distribution, FormsBox offers several options including batch processing, where a CSV file extract can be uploaded with data, and FormsBox will capture these into the specified form and perform the bulk distribution automatically, with workflow.

The AX version of FormsBox includes a set of helper classes in X++ to allow developers to perform any API action; for example, a query can be built in X++ to link various pieces of data across AX into a dataset that is then used to batch populate forms for distribution.

Batch processing of forms takes place in the FormsBox cloud, moving the processing away from the AX production environment for minimal impact on regular users.

QR Codes are also supported, so instead of sending a lengthy form description, a QR Code can automatically refer an external party to a form stored in the FormsBox cloud application (external to AX), which they can then complete and sign, and this will be returned to the worker into the AX version.

AXQRCode

There are a number of good use-cases for using FormsBox in AX, for example:

  • Pay stubs and tax returns
  • Insurance proof
  • Employment contracts and reviews
  • Training & certification copies
  • Rental agreements
  • Warranty details
  • Receipts
  • Invoicing
  • Expense claims
  • Mail-merge / Bulk distribution
  • Long-term electronic archiving

So if you are after an AX 7 document management system, do have a look at FormsBox and get in touch if you need more information. The FormsBox site contains more detailed screenshots and end to end use-cases.

As it is in BETA, there will be some look & feel changes and additional functionality. Pricing is not available yet, but on the plus side there is an option for an organisation to purchase a copy of the platform with source-code to manage and control hosting on their own (including custom site branding and using their own Azure instance for storage).

 

 

 

Azure/Cloud Forms System for AX

In my previous short post, I created a simple doodle control for AX, and mentioned that the actual objective was to build a signature pad using the Extensible Control Framework. In this post I’ll extend that a little by adding the ability to auto-complete a PDF form using data from AX and then give it to the user to sign using their pointing device of choice.

For the AX developers out there this post will unfortunately not include source code as I am working on a commercial product, but hopefully the ideas I share here will help you in your own projects at some stage.

I am not very familiar with AX Document Management, but what I have seen in the latest version of AX 7 RTW is that attachments are supported, but what I am really looking for is something that also gives me the following features:

  • PDF support with fillable fields
  • Signatures, without requiring digital certificates
  • Offsite-storage for once the volume of documents reaches critical mass
  • Archiving offsite for say, 7 years (payslips, tax certificates)
  • Forms distribution to users (mail merge)
  • Access to archived forms OUTSIDE of AX (like payslips, when an employee has been terminated)
  • Annotations
  • Library with form templates

In a previous project I built a forms management system supporting all of the above items, and as I am learning X++ I thought it would be a good learning project to convert as much of that as possible into AX. The platform, called FormsBox, is still in BETA so please don’t sign up. However, one item I did add early on was a REST API to allow retrieval, editing and downloading of forms via that API. So I will utilize the same platform now to try and achieve a similar experience in AX. FormsBox runs in Azure and was built using ASP.NET (C#).

Let’s have a quick tour of FormsBox (www.formsbox.com)

 

FBA

First off, when signing in, users can see their home folder with subfolders and all their uploaded forms. Forms can be uploaded as images, Word, Excel or PDF. They are all automatically converted to PDF during the upload process. You can also email a form to the site, and a backend service will collect the email, extract any attachment, and stick it into your home folder automatically. Handy for storing email attachments. Forms are stored as Azure Blobs and encrypted.

 

FBBFormsBox also features a public library of forms (currently just under 500) which any user can copy into their home folder for use, completion and sharing/distribution. Looking at the Library view, notice the GUID which lists the form code. We’ll need this code for the API, and we will use code F03B7658-9CE1-4921-A5D7-FEFF02FFC733 which is the code for the Check, Stop Payment form.

 

FBC

We can preview the form in FormsBox, and note it contains fillable fields. Each field has a field name, in this case FirstNameLastName, AccountNumber, Payee, Amount etc.

FBD

One item of concern in AX is PDF support, without requiring the addition of anything additional to core AX. Like it or not, PDF is still the de-facto choice for creating and completing forms. So we have a couple of issues to deal with here:

  • Display a PDF in AX, without any plugins
  • Complete that PDF using data from AX
  • Allow a user to sign the PDF, correct/undo a bad signature
  • Save, Download or return that form (I’m putting that into the to-do list for another post)

We’ve already got a basic signature pad going, as per my previous blog post. I’ve added some extra JavaScript to allow the resetting of the canvas object to undo a bad signature.

So the bigger challenge now is to display a PDF in AX.

There are a couple of ways we can do that. First, we can call an external service for rendering the PDF, and host that in an IFRAME. Unfortunately, that will display a pop-up for the user asking whether they want to navigate away from the current page. Bad choice. Also, unless that site uses HTTPS, a security warning will also be displayed. Not the type of quality we want from what will end up being a commercial product costing money.

As we only want to display the PDF, what we can do instead is convert the PDF to an image, and send the image back to AX to embed into an IMG tag inside of our Extensible Control HTML.

Thus, what we want to achieve is to call the FormsBox API, pass our AppKey, AX data fields, and the form code, and have it complete the form for us, and send it back as a PNG image so the user can sign on it within AX. This is depicted graphically below.

Drawing1

For that I used a combination of tools including GhostScript and iTextSharp for conversion and data-stamping into the fillable fields. I have to also resize the end result to ensure it properly fits in the AX form. There are a number of improvements I need to make, but so far so good.

Let’s take a look at the PDF being display in AX, as shown below. As we can see we called the FormsBox API and passed the data from AX to the API, which returned the completed form as a resized PNG image which we embed into an IMG tag.

 

FB0

The next screen shows the signing process in action using our signature pad. We’ve also added a “New” button, to allow the user to reset the image if they botched their signature. As it takes a few seconds to load the image via the API, we also added a “Loading…” text into the canvas, which is erased once the image arrives back.

 

FB1

Finally, we can zoom in a bit to view the completed, signed document.

 

FB2

Still some things to improve, but we have a workable solution using the kitchen sink of toolsets (Azure, ASP.NET, HTML, PDF, CSS, JavaScript, C#, X++, AX 7).

With that, we have the beginnings of a viable commercial product or at the very least a usable plugin for other AX modules.