Category Archives: Computers

Automating Reports from Microsoft Dynamics Nav using ETL Tools

Recently I’ve been looking into a piece of software called ETL-Tools – Advanced ETL Processor Enterprise.

This software is designed to perform ETL operations, similar in which to Taskcentre does.

The software is quite well priced, with a single user licence being between $340 and $690 (£216 and £440). This is a purchase cost and includes a years worth of support. Annual maintenance is 20%, but is optional. A site licence is also available which allows unlimited users.

In this blog post, I’m going to cover creating an automated task to generate a PDF document with the sales totals for company, split by salesperson code. The report is designed to run daily and is sent to the sales manager in order to view the progress.

This is using the Nav demonstration database – Cronus, with Nav 2015, running on Server 2012.

Creating Connections
For this task, we’re going to need 2 connections as follows:

MS SQL Database – Connection to the Nav Database
SMTP – In order to send the email

After opening Advanced ETL Processor, it’s a simple task of right clicking on the blank connections and choosing New. In the screenshot on the right, I’ve shown the Nav connection.

Creating The Report
On right clicking to create the report a wizard is shown. This wizard takes you through a few steps in order to create the basis of the report:

Source Connection (In this case, it’s the connection we’ve created earlier)
SQL Query
Fields to add to the report
Groupings
Report Orientation
Report Layout (Rows or Columns)
Theme

After completing the wizard, this opens up in design view for further editing and design:

For this task, I’ve used all default options, plus the following SQL:

Select
   [CRONUS UK Ltd_$Sales Header].[Salesperson Code],
   Sum([CRONUS UK Ltd_$Sales Line].Amount) AS Sum_Amount
From
   [CRONUS UK Ltd_$Sales Header] Inner Join
   [CRONUS UK Ltd_$Sales Line] On [CRONUS UK Ltd_$Sales Header].[Document Type] =
   [CRONUS UK Ltd_$Sales Line].[Document Type] And
   [CRONUS UK Ltd_$Sales Header].No_ =
   [CRONUS UK Ltd_$Sales Line].[Document No_]
Where
   [CRONUS UK Ltd_$Sales Header].[Document Type] = 1
Group By
   [CRONUS UK Ltd_$Sales Header].[Salesperson Code]

Create Package
After creating the report, we need to create a package to perform the following steps:

Run Report
Save PDF to Disk
Send Email with PDF Attachment

Creating the package is very simple, right click and choose add. We need to drag two “Blocks” onto the planner, “Report” and “Send Email” and join these up together:

The screenshots below shows the setup of these two steps:
Run Report

Send Email

Running Task
The task can be run by either opening up the Package, right clicking and choosing “Run Package” (F3). Alternatively, under the schedule tab, it can be scheduled to run automatically.

In this example, I’ve set the email to be run manually and sent to my email address. Example in the screenshot below:

In due course I will do some further investigation into ETL Tools. Watch this space!

Microsoft Dynamics Nav 2013 R2 Action Image Library

Leave a Reply

In Nav, it’s possible to change the icon on a menu option, in order to make it clearer to the user. These are called “Action Images” in Nav 2009 or “Icon Collection” in Nav 2013 onwards. In the screenshot to the right I have highlighted an example of such an image.

These icons can be changed in “Object Designer” by a Microsoft Dynamics Nav Developer, within the properties for the action.

Depending on the version of Nav, it is either a free text entry, or a lookup. But regardless of the version of Nav, unfortunately there’s no image preview. Hopefully this will be included in a future version.

Luckily, the full library of options available are available on the Microsoft’s website:

Unfortunately for Nav 2013, these are split across multiple pages. This post is a lift of all the images from the site above, but all on a single page to make it nice and easy to view and select the perfect icon for your page.

For up to date images, I recommend viewing the Microsoft articles above.

Automating E-Mail Support Requests using FOAAS and Taskcentre

1 Reply

In this day and age, everyone is looking for prompt responses to e-mails and support requests. In this blog post I’m going to cover integrating FOAAS into e-mail using Taskcentre in order to provide prompt responses to support requests.

By responding quickly to emails, it can greatly improve satisfaction levels, plus also using FOAAS’s technology can also reduce the number of support requests*.

* by increasing the likelyhood of a P45.

In order to integrate the solution, we need to break it down into 3 major steps:

Intercept Email
Call Web Service
Send Response

Intercept E-Mail
In order to intercept the email, we use a standard “SMTP” input trigger. This trigger stores the following into variables:

From E-Mail
E-Mail Subject
E-Mail Body

Additionally, we also need to use the “Text Parser” tool in order to extract the following from the “From E-Mail”

Sender Name
Sender E-Mail

Call Webservice
FOAAS is a REST based webservice. So I’ve built up a web service call, passing through the following parameters:

Sender Name
From Name

This web service returns two values:

message
subtitle

Additionally, the web service requires custom headers which have been amended using the tool

Send Response
A response is then sent to the inbound e-mail automatically, using the following steps:

XML to Recordset (HTML Tool only recognises recordsets)
HTML (To build up an email)
Outbound SMTP

Example
Below you can see an inbound email from a used. As you can see, the email contains both a subject and a request for the support helpdesk. In my VM, the “From” name is “James – Demonstration”, as I use the VM for testing and demonstration purposes.

Here is the response to the email, including the automated response to the web service:

Source Task and Installation
Below is the download link for the example, plus also the web service connector. In order to install the task, the install needs to be done in the following order:

Import Web Services Connector
Import Task

FOAAS Web Service Connector
FOAAS Task

This task has been provided for humour/educational use only.

Stripping String to Alphanumeric in Microsoft Dynamics Nav using Regex

Leave a Reply

Within Microsoft Dynamics NAV, there are a few times which an input string needs stripping back to alphanumeric characters.

For example:

Unique Identifiers
Shipping Tracking Numbers
Vehicle Registration Numbers
etc

This can be done directly in C/AL, or also using .NET.

Directly in C/AL:
Directly in CAL, you need to create a function as follows:
Name: StrinStringToAlphaNum Accepts: Text - 250 Returns Text - 250 Local Variables:



tempString - Text
i - Integer

The code required is:

 IF STRLEN(StringToStrip)=0 THEN
   EXIT(StringToStrip);

 FOR i:= 1 TO STRLEN(StringToStrip) DO
   BEGIN
    IF StringToStrip[i]IN ['a'..'z','A'..'Z','0'..'9'] THEN
      BEGIN
          tempString:=tempString+FORMAT(StringToStrip[i]);
      END;
   END;
  EXIT(tempString);

Using .NET:
Directly in .NET needs less code, but is only suitable for Nav 2009 onwards (NAV 2009, 2009 R2, 2013, 2013 R2 and 2015).
Local Variables:



RegEx - DotNet - System.Text.RegularExpressions.Regex.'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

The code required is:

"Output String" := RegEx.Replace("Input String",'[^A-Za-z0-9]','');

Comparison
The Regex method is less code, but only works on newer versions of NAV. Whereas the C/AL only method works on older versions. (If you use the “classic client”, it’s an old version and doesn’t support .NET)

I’ve not tested both methods for speed, but both are really quick and not noticeable to the user.

Regex could be used for other things too, such as providing validation, classic examples being:

E-Mail Address
Phone Numbers
Website Addresses
etc

The Regular Expression Library is a good starting point for these regular expressions.

Maybe at some point I’ll provide some details on how to do these.

Trojanized PuTTY Software

Leave a Reply

Putty is a very popular open source SSH/Telnet client which can be used to connect to other devices and machines, such as Linux plus also some firewalls and other networking devices. It runs on Windows and has an installed version plus also a portable client.

Some hackers have decided to exploit this by adding code which uploads the credential information to a 3rd party server.

There’s no mention in the code to suggest the uses of this stored information, but obviously it will not be used for legal uses.

Unfortunately this is very easy to do with open source projects, the hard thing is to get critical exposure. The difference here is that the hackers used a variety of SEO techniques to get the sites hosting the compromised version up the search engine listings.

This has been done before with FileZilla. The compromised software being nicknamed Stealzilla.

Further information plus how to identify if you are running a compromised version below:

Trojanized PuTTY Software

Taskcentre Alert Notifications – Update

Leave a Reply

In a previous post, I mentioned about posting alerts to HipChat.

Unfortunately passing the “notify” parameter resulted in error messages, regardless of the text passed.

It appears that this was a bug with the web service api. I’ve just tried again and passing the following work as expected:

true
false

Windows XP support deal not renewed by government, leaves PCs open to attack

Leave a Reply

It’s been over a year since Windows XP was officially made end of life, but still approximately 28% of users still use Windows XP according to NetMarketShare.

Amazingly the UK Government is one of those who have not upgraded yet….

Departments still migrating despite having had a year to move away from ancient system

Source: Windows XP support deal not renewed by government, leaves PCs open to attack

Windows 10 IoT – With Raspberry Pi

Leave a Reply

It’s finally here… Microsoft have released the insider preview to Windows 10 IoT (Internet of Things)

The install for the Raspberry Pi 2 looks pretty easy, similar to installing Rasbian. Once installed, you use powershell to connect to and manage the device.

Note: It’s not compatible with the earlier Raspberry.

On the Windows 10 IoT, there’s detailed instructions for installing on:

Raspberry Pi 2
Minnowboard Max
Galileo
Arduio
- Windows Remote Arduino
- Windows Virtual Shields for Arduino

On the IoT Website, there’s a load of examples to get started, with detailed instructions into building your first app.

Time to order mine in order to experiment!

Microsoft Virtual Academy – Free IT Training, Online Learning of Microsoft Technologies

Leave a Reply

Came across this website this evening while doing some research, wish I’d have found it earlier. Very useful in brushing up your skills in Microsoft products, including Server 2012, Azure, Office 365 etc. And best of all, it’s free…!

Looking for a simple, effective way to get training on Microsoft’s Cloud technologies? Microsoft Virtual Academy!

Source: Microsoft Virtual Academy – Free IT Training, Online Learning of Microsoft Technologies

Base64 Encoding and Decoding with SQL Server – Unicode Characters

Leave a Reply

With a SQL query I’ve had to write, I’ve had to encode some text in Base64.

The following functions where extremely useful, this is based on this solution, but has been adapted to handle Unicode characters.

Convert to Base64

CREATE FUNCTION [dbo].[fn_str_TO_BASE64]
(
    @STRING NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT
            CAST(N'' AS XML).value(
                  'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
                , 'NVARCHAR(MAX)'
            )   Base64Encoding
        FROM (
            SELECT CAST(@STRING AS VARBINARY(MAX)) AS bin
        ) AS bin_sql_server_temp
    )
END

Convert from Base64

CREATE FUNCTION [dbo].[fn_str_FROM_BASE64]
(
    @BASE64_STRING NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT 
            CAST(
                CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@BASE64_STRING"))', 'VARBINARY(MAX)') 
            AS NVARCHAR(MAX)
            )   UTF8Encoding
    )
END

Here’s how you run the SQL queries:

SELECT dbo.fn_str_TO_BASE64('Hello World')

SELECT [dbo].[fn_str_from_base64](dbo.fn_str_TO_BASE64('Hello World'))

JRevell.com

Johnathan Revell’s Personal Website

Category Archives: Computers

Automating Reports from Microsoft Dynamics Nav using ETL Tools

Microsoft Dynamics Nav 2013 R2 Action Image Library

Automating E-Mail Support Requests using FOAAS and Taskcentre

Stripping String to Alphanumeric in Microsoft Dynamics Nav using Regex

Trojanized PuTTY Software

Taskcentre Alert Notifications – Update

Windows XP support deal not renewed by government, leaves PCs open to attack

Windows 10 IoT – With Raspberry Pi

Microsoft Virtual Academy – Free IT Training, Online Learning of Microsoft Technologies

Base64 Encoding and Decoding with SQL Server – Unicode Characters