Firebrand are doing the Free Training for Life competition again…
If you win, you can sit as many of their courses as you like over your lifetime, at no cost, so definately worth an entry. Courses range from Software Development (e.g. MCSD), to Ethical Hacking and Prince2. All carry some form of official certification at the end.
Note that your information most likely will be used for marketing purposes (Although I dont get much off them, just a few nudges every so often).
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:
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:
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:
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!
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.
URL Encoding is the transformation of special characters into a form which can be passed over the internet. A good example being ” ” (space) which should be replaced by “%20%”.
The following SQL function can be used to URL Encode a string in SQL:
CREATE FUNCTION dbo.UrlEncode(@url NVARCHAR(1024))
RETURNS NVARCHAR(3072)
AS
BEGIN
DECLARE @count INT, @c NCHAR(1), @i INT, @urlReturn NVARCHAR(3072)
SET @count = LEN(@url)
SET @i = 1
SET @urlReturn = ''
WHILE (@i < = @count)
BEGIN
SET @c = SUBSTRING(@url, @i, 1)
IF @c LIKE N'[A-Za-z0-9()''*\-._!~]' COLLATE Latin1_General_BIN ESCAPE N'\' COLLATE Latin1_General_BIN
BEGIN
SET @urlReturn = @urlReturn + @c
END
ELSE
BEGIN
SET @urlReturn =
@urlReturn + '%'
+ SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),3,2)
+ ISNULL(NULLIF(SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),5,2), '00'), '')
END
SET @i = @i +1
END
RETURN @urlReturn
END
In order to use the function, call it as follows:
SELECT dbo.UrlEncode('This is a test, *&%"£$')
This gives the following result:
This%20is%20a%20test%2c%20*%26%25%22%a3%24
Thanks to Peter DeBetta for the SQL.
I’ve had an ongoing issue with a Magento installation. When the maintenance mode is turned on (with access restricted to a few ip’s), web service calls using v2 SOAP API throws the following error:
SOAP-ERROR: Parsing WSDL: Couldn't load from 'http://**URL**/index.php/api/v2_soap/index/?wsdl=1' : failed to load external entity "http://**URL**/index.php/api/v2_soap/index/?wsdl=1"
The web service worked correctly when the site was not in maintenance mode.
After lots of research, managed to find the solution. Basically Magento itself was throwing the error as it uses the PHP SoapServer to create the API. This object needs access to Magento in order to function correctly. As maintenance mode was enabled, the SoapServer didnt have correct permission, so was getting the holding page and throwing the error.
In order to fix the issue, the IP address of the server needed adding to the exceptions for the maintenance mode. Full details on how to do this here – Magento Maintenance Mode
Once the IP was added to the white list, the web service calls started working again correctly.
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 E-Mail
In order to intercept the email, we use a standard “SMTP” input trigger. This trigger stores the following into variables:
Additionally, we also need to use the “Text Parser” tool in order to extract the following from the “From E-Mail”
Call Webservice
FOAAS is a REST based webservice. So I’ve built up a web service call, passing through the following parameters:
This web service returns two values:
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:
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:
FOAAS Web Service Connector
FOAAS Task
This task has been provided for humour/educational use only.
Within Microsoft Dynamics NAV, there are a few times which an input string needs stripping back to alphanumeric characters.
For example:
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:
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:
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:
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.
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:
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:
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