Third week under COVID-19 lock-down in Austria (5. April )

General Situation

Three weeks have passed under lock-down and things have calmed downed, people are getting used to stay at home. Homeoffice, Home Schooling, etc. becomes more organized. Although many people (83%) accept the lock down in general, with these good weather conditions people tend to interpret the lock down a little bit too relaxed. But for thoses who get caught, getaways can become really expensive (up to 3600€). The police has announced that they will put additional effort to enforce the lock down including mountain police patrols and helicopters.

Good weather conditions
Good weather is bad for lock-down discipline

The number of infections is still on the raise but the rate is rather low. Under lock down the increase rate now has droped under 10% for many days. In addition masks are now mandatory for going shopping.

COVID-19 rate in Austria
Positive tested for Covid-19

Tracking is still a big issue and recently discussed in the media and politics. Google statistics show the effect of lock-down. Some politicans are discussing if the Red Cross’ Stop Corona App should be mandatory. Another idea is to automate the app’s handshake. Right now, a handshake with other app users has to be done manually. This isn’t done very often, especially with stranger e.g. at the super market. A future update may automate the handshake to track every people around you. Although a persons ID is anonymous, an automated tracking of everyone is not accepted by most people.

Azure Cloud and Business Applications

Last week the resources in the Azure Cloud region Europe were at its limit. The deployment of VMs in West/North europe often failed. This week we tried to deploy a Dynamics 365 Finance / SCM Tier2 environment. After days in status Queued we were adviced to place the environment in US South region.

Deploy Dynamics 365 Finance / SCM Tier2 environment in US-South Central

The highlight this week was the virutal launch event for Dynamics 365 and Power Platform april wave. Our company attended the event from our home offices while the speakers were presenting from their home offices.

From Home Office to Home Office: Business Applications Wave April’20 Launch Event

From all the new features coming to the Power Platform, I found the UI flow very interesting. In combination with an on-premises gateway, Power Automate (aka. Flow) can be used to interact with locally installed windows applications and websites. This is especially interesting to integrate legacy applications without an API. I’ve made a short Video how to use the UI Flows for web sites to get the actual Bitcoin / EUR rate and send it via Email.

UI Flow demo: Integrate a web site with Power Automate

More Cooking

Gemüsefleckerl, Fleischlaberl, Pudding, Apfelschlangerl, Fleckerlspeis

Stay home, stay safe

Use UI Flows in Power Automate to interact with a web site

UI Flows are new features from power platform april 2020 wave, and allow you to integrate local installed applications and web sites. I’ve made a video with UI Flow for web sites. In this demo I’ve create a flow that reads the Bitcoin / Euro rate from a web sites and sends it per Email.

Call an Azure Function from X++ in Dynamics 365 Finance / SCM

Create an Azure Function

Azure Functions are simple way to pack and provide business logic as web service without worrying about hosting a web server. Azure Functions can be implemented in different programming languages like C#, JavaScript, PHP, Java, etc. and can be hosted on Linux and Windows with different runtime environments that feed your need.

In the Azure Portal click + Create a resource and search for Function App:

Create a Azure Function App

In the next screen choose a subscription and create a resource group (or use an existing one if you like). Provide a useful name and choose code as Publish method. Select .NET Core 3.1 as runtime stack and a region that is near your location:

Configure the Azure Function App to use .NET Core 3.1

Click Review + Create to create the Azure Function. It takes a view minutes to provision all the required elements:

Deploy the Azure Function App

Click on Go to Resource. Next to the Functions group click + to create a new function and select In-Portal to edit the function code direct in the browser:

Create a new HTTP trigger

Choose the webhook + API to create a demo function that can be called via HTTP POST.

Use webhook for the Azure Function

This will create a function that takes a name as parameter and returns “Hello ” + the parameter name.

C# Azure Function code

You can test the function by using Test tab on the right. The function takes a JSON string with a name parameter and returns a simple string.

Test the Azure Function with a JSON string

Call the function from X++

In the azure portal get the function URL with a function key. Copy the URL with the key:

Copy the Azure Function URL with function key

In Visual Studio create an X++ class with a main method for testing. Use the System.Net.Http.HttpClient class to call the service. The content is a JSON string encoded in UTF-8 with a name parameter and value. In this example the name is Dynamics:

System.Net.Http.HttpClient httpClient = new System.Net.Http.HttpClient();
System.Net.Http.HttpContent content = new System.Net.Http.StringContent(
        "{\"name\":\"Dynamics\"}",
        System.Text.Encoding::UTF8,
        "application/json");

At the moment X++ does not support the await keyword for asynchronouse calls. The workaround is to use the Task.Wait() method. Call the service with your function URL async and get the content of the call:

var task = httpClient.PostAsync("https://<YOUR_FUNCTION_URL>",content);
task.Wait();
System.Net.Http.HttpResponseMessage msg = task.Result;

System.Net.Http.HttpContent ct = msg.Content;
var result = ct.ReadAsStringAsync();
result.Wait();
System.String s = result.Result;

info(s);

Start the class from Visual Studio. The result should look like this:

Call the Azure Function from Dynamics 365 Finance

Second week under COVID-19 lock-down in Austria (28. March)

General Situation

The second week of lock-down has passed. The number of infections are growing, but the rate of new infections is decreasing. However, a small increase of large number is still a larger number and it is expected that we will see the peek in the next weeks. With a incubation time up to 10 or 14 days it looks like the lock down has a positive effect. The number of infections is only an approximation to reality, because only a small number of people are tested. The more tests, the more positive cases will be identifed. Thats why it was announced to use the number of people in hospital and in intensive care as more realisitc indicators.

Increasing number of infections, decreasing rate

The government is publishing actual data on regular basis: https://info.gesundheitsministerium.at/

Austrias Covid-19 dashboard

Remote-, Home-, Distance- Everything

In week 2 almost everything possible has been switched to digital and remote work. Many courses at Johannes Kepler Universtity have been switched to distance learning via video streams. Whenever I prefer presence courses because it’s hard to say concentrated for an hour watch video streams.

JKU Linz Moodle

Companies like SAP took the chance to offer free courses and certifications for students who now have time and stay at home. Not only SAP but many other companies use the forced timeout to gain new customers via free videos, tutorials, training, etc.

Free SAP course and certification

The Austrian Red Cross has developed a Stop Corona app to track social contacts. The app assings an anonymous number to the phone and you can do a handshake with other app users. If one person is tested positive on Covid-19 all other contacts get notified on their phone. The use of the app is (actually) optional. Other countries like chain also have tracking apps, but these apps are not anonymous and not optional.

Anonymous tracking app

Fight for Resources

The huge number of Microsoft Teams users (44 million now) forced Microsoft to increase the resources by x60 times. Microsoft also announced to reduce the functionallity of cloud services like Teams, SharePoint, Stream etc. There are also complaints that deploying new Azure resources in region West-Europe fails. I tried to deploy 2 servers (2 cores, 7 GB RAM) and indeed one deployment failed because no resources could be allocated.

No resources in Azure region West-Europe

More Cooking

I’ve used the time in lock-down to train my cooking skills. Fleischlaberl, Kaiserschamarrn, spinach, Apfelschlangerl, Noodle Gratin, Käsekreiner, Apple rings.

Cooking in Isolation

First week under COVID-19 lock-down in Austria (March 22nd)

General Situation

One week has passed since Austria was set under lock-down. When being outside (Work, Shopping Food, Taking a walk) at least 1m distance has to be kept between people, gathering in groups is not allowed. The Police is enforcing the rules with fines up to 3600€. Students, who party and potentially spread the virus can be expelled from university.

Severe consequences for students

Home Office

Our company is now running 100% from home office. It turned out that Teams is now the backbone for us. On monday some of us experienced problems in Teams. However, this is more than understandable when so many companies had to switch to home office and schools are also using Teams for distance learning. Microsoft reported they have now 44 million users on Teams. The usage of Teams also increased significantly in our company.

Teams usage increased during home office

While the cloud scales pretty good, the local internet connection at our office is relativly fixed. There are peeks during working hours because we still have some workloads on-prem but monitoring the traffic it seams we are pretty fine with the current bandwidth. Same goes for the sizing of our RDS host which can handle the remaining on-prem workload as well. The more you are cloud-based, the less you have to worry about on-prem bottlenecks.

Traffic monitorig for on-prem workloads

During the Corona crisis Microsoft, Google, Teamviewer and others offer their collaboration tools for free. To ensure there is enough bandwidth for home office (e.g. while the kids are streaming videos), Netflix announced (was asked) to reduced the quality of video streaming. The RTR announced that providers may omit net neutrality and reduced the bandwith for video streaming. Even more concerning is that A1 Telekom now gives anonymous(?) movement profiles of cell phones to the government and red cross.

There is also something good to take away from this crisis. It is a massive push for digital transformation because now companies are forced to embrace cloud services like Teams to stay operable. We already heard from cloud-sceptical customers that especially Teams is very welcome. Teams & Co is not only used to enable home office, but also to switch former face-to-face contacts with customers and suppliers to remote contact.

Now, that we the number of appointments and customer vistis have been significantly reduced we use some time for house keeping like patching servers, upgrading operating systems, etc. We were planning to take some exams to meet the Microsoft Competency requirements. Thomas Maurer published a good article about how to take an certification exam online. This is also a breathing time to work on our Blockchain research project and migrate applications from the Blockchain Workbench to the Blockchain Service in Azure.

Evaluating the Blockchain Service (Beta)

Home Office = Home Cooking

Home office means home cooking and I started to enjoy cooking a fresh meal every day like Schnitzel, Cevapcici, Chili, etc.

Mahlzeit

First day under COVID-19 lock-down in Austria (16. March'20)

In order to reduce corona infections in Austria we are under lockdown, meaning everybody should stay at home except working in a critical business, buy food and drugs or help others. At this moment we have 1018 people infected and 3 dead.

Monday morning but most people stay at home

As IT company we can switch pretty easy to home office. Communication is now based on Microsoft Teams and Email. We have clear rules for home office. Sign-in and Sign-out in our Shop Floor Control System, phone, email and Teams has to be enabled. We have set up a seperate company Team to group all employees at home in virtual office.

A virtual office in Teams

We’re utilizing our cloud services like Office365, Remote Desktop Service Hosts, VPN and Facebook as well for instant communication. I’ve also summarized the options today in an email to my colleagues. Although there are reports that Teams is facing issues because of the high usage, I didn’t recognize problems in Teams today.

Many articles regarding home office suggest to make a clear break between private and working time. If decided to use my Surface environment (Laptop, Keyboard, Mouse) for work and my Desktop PC for private time.

Cooking and lunch is done at home, and is far not so much fun like in the office. Especially withouth the office dog Meggie.

Cooking noodles for lunch

Public transport is still operating, but the tramway is almost empty (as it should be). Borders are closed for regular travellers, only trucks and business travel ist allowed. Most passenger flights from and to Linz airport are cancelled, freight is still operating.

Clear sky, no planes

Let’s see how the rest of the week will be. Someone posted this today on facebook:

Everything is going to be alright ❤

Extend SalesTable2Line Framework (Dynamics 365 Finance / SCM)

This is an update to my older post how to extend the SalesTable 2 Line framework. The big difference is that in Dynamics 365 Finance and SCM overlaying is not supported and extensions and delegates need to be used. This post uses the same use case. A sales-notes field from the SalesTable needs to be updated in the SalesLines if it is configured so.

Download the sample source code: https://erpcoder.blog/source-code/

Extend the data model

Create a new string datatype and call it ERPSalesNote. Extend the SalesLine and add the ERPSalesNote datatype to the list of fields. Extend the SalesTable and add the ERPSalesNote to the fields. Also add the ERPSalesNote field to the field group HeaderToLineUpdate.

Extend the user interface

Extend the SalesTable form. Place the SalesTable.ERPSalesNote in the Delivery group of the HeaderView tab.

Add the SalesLine.ERPSalesNote field to the delivery group in the LineView tab.

Prepare the update-order-lines dialog

The dialog to choose if and which fields need to be updates at the lines is generated automatically based on the HeaderToLineUpdate field group. There is some code needed to show the fields name in the dialog. This is done by subscribing a custom method to the delegate SalesTable2LineField.lineUpdateDescriptionDelegate

Create a new class that returns a name for the ERPSalesNote field.

class ERPSalesTable2LineField
{
[SubscribesTo(classStr(SalesTable2LineField), delegateStr(SalesTable2LineField,lineUpdateDescriptionDelegate))]
public static void lineUpdateDescriptionDelegate(FieldId _fieldId, TableId _tableId, EventHandlerResult _result)
{
  if(_tableId == tableNum(SalesTable) &&
  _fieldId == fieldNum(SalesTable,ERPSalesNote))
  {
    _result.result("Sales Note");
  }
}
}

Open the Header to Line update dialog by clicking on Accounts receivable > Setup > Accounts receivable parameters > Tab update > update order lines

Extend the framework classes

Create an extension for the AxSalesTable class and create a parm method for the ERPSalesNote field

[ExtensionOf(classStr(AxSalesTable))]
final class AxSalesTable_Extension
{
public ERPSalesNote parmERPSalesNote(ERPSalesNote _salesNote = "")
{
  if (!prmisDefault(_salesNote))
  {
    this.setField(fieldNum(SalesTable, ERPSalesNote), _salesNote);
  }
  return salesTable.ERPSalesNote;
}
}

Create an extension for the AxSalesLine class. Implement a parm and set method. Use the chain of command pattern to extend the setTableFields method.

[ExtensionOf(classStr(AxSalesLine))]
final class AxSalesLine_Extension
{
public ERPSalesNote parmERPSalesNote(ERPSalesNote _salesNote = "")
{
  if (!prmisDefault(_salesNote))
  {
    this.setField(fieldNum(SalesLine, ERPSalesNote), _salesNote);
  }
  return salesLine.ERPSalesNote; 
} 
protected void setERPSalesNote() 
{ 
  if (this.isMethodExecuted(funcname(), fieldnum(SalesLine, ERPSalesNote))) 
  { 
  return; 
  } 
  this.setAxSalesTableFields(); 
  if (this.isAxSalesTableFieldsSet() || 
      this.axSalesTable().isFieldModified(fieldnum(SalesTable, ERPSalesNote))) 
  { 
  this.parmERPSalesNote(this.axSalesTable().parmERPSalesNote()); 
  }
} 
protected void setTableFields() 
{ 
  next setTableFields(); 
  this.setERPSalesNote(); 
}
}

Test your implementation

Make sure that the update method in the parameter is set to prompt. Open an existing sales order. Change to Header view and switch to edit mode. Change the notes in the delivery tab and save.

A dialog pops up and asks to update the lines. Click yes.

Check the sales note field in the sales line. The note field at the sales line should be updated with your text from the header.

Connect to the SQL database of a Dynamics 365 Finance Test instance

In Dynamics 365 Finance / SCM we can no longer access the SQL database of the production environment directly. However, we can access the SQL database of the Acceptance Test instance. All required information can be found in LCS. I’ve made a video where to find this information in LCS and how to connecto to the SQL database.

Green IT Consulting 2019

By using train instead of car for business travel, I managed to avoid 1000kg+ CO2 in 2019

DateFromToCo2 reduction (kg)
24.2.2019LinzNürnberg63,8
26.2.2019NürnbergLinz63,8
9.5.2019Linz Vienna 38,1
10.5.2019 Vienna Linz38,1
14.5.2019Linz Vienna 38,1
17.5.2019 Vienna Linz38,1
28.5.2019Linz Vienna 38,1
29.5.2019 Vienna Linz38,1
12.6.2019Linz Vienna 38,1
12.6.2019 Vienna Linz38,1
25.9.2019Linz Vienna 38,1
27.9.2019 Vienna Linz38,1
24.10.2019Linz Vienna 38,1
24.10.2019ViennaLinz38,1
28.10.2019LinzVienna38,1
29.10.2019ViennaLinz38,1
9.11.2019LinzBerlin174,4
14.11.2019BerlinLinz174,4
1009,8 kg

Please find the summary for 2018 here: https://erpcoder.blog/2019/06/11/green-it-consulting/

XYZ Analysis for Dynamics 365 FO in PowerBI

XYZ analysis is used to categorize products based on the variance of their demand. Products with a low demand variance, i.e. same quantity demanded regulary, are categorized with X, products with an unstable demand Y and products with a high variance in demand as Z.

The categorization is based on a calculated measure, often referred to as variance coeffizient. This coefficient is calculated by the standard deviation of the demand divided by the mean.

Video

Here is a video tutorial how to build the XYZ analysis in PowerBI

Example

Here is an example of three products with different demand over a year. Toilet paper is needed every month in the same quantity, car tires have a higher demand in spring and autum, firework is demanded only on special ocasions.

Demand of different products
Demand of different products in a year

Prepare Data in PowerBI

The basis of the XYZ analysis will be the SalesInvoiceLines entity. At least three columns are needed. The InvoiceDate, the InvoicedQuantity and the Product Name. In this example I renamed the dataset to “Demand”

In PowerQuery create two new columns, one for the year based on the InvoiceDate and one for the month, also based on the InvoiceDate. Afterwards remove the InvoiceDate column

YEAR  = Date.Year([InvoiceDate])
MONTH = Date.Month([InvoiceDate]) 

Next, remove the InvoiceDate column and group the records by ProductName, Year and Month and aggregate the InvoicedQuantity column. Here is an example:

Aggreage demand by product name, year and month
Aggregation in PowerBI

The second dataset contains the XYZ data template, including the ProductName, Year and Month. For simplicity you can enter the 12 records for Year / Month combinations manually. Add an additional column containing the distinct list of ProductNames and expand the rows.

ProductName = List.Distinct(Demand[ProductName])
Product name, year and month

Finally, merge the two datasets to a new one using a left outer join based on the second dataset. As result you get a list of ProductName, Year, Month, Qty combination for each product and every month no matter if there was an acutal demand or not. If there was no demand, the Qty will be null and needs to be converted to 0.

Left Outer Join on calendar and demand
Merge queries in PowerBI

The resulting dataset has records for eacht month and product. It can be used to calculated the mean, standard deviation and variation coefficient. To do so, create a new measure in PowerBI. It will calculate a coefficient value that can be used to categorize the products in X, Y and Z.

Variation Coefficient in PowerBI