Copy-paste with keyboard script 2: from Excel to D365FO

Again an entity was missing in D365FO to import the data, this time a custom one. Building upon my work Copy-paste automation in D365 FO with a keyboard script, I created a Wunderwaffe script to copy a simple table with a Code and a Description directly from Excel into the D365FO browser window.

Close all other windows and put the browser with D365FO and Excel side by side. Place the cursor in Excel in the Code column at the first row to copy, then hit Ctrl-J. The script will start copying data from Excel and pasting records in D365FO by switching windows forth and back:
^j:: ; --------- Place the focus on the code column in Excel, start with Ctrl-J Loop 200 { ; --------- Copy the content of the Code from Excel Send, ^c Sleep, 500 ; --------- Switch the window: Alt-Tab Send, !{TAB} Sleep, 1000 ; --------- Create a new record in the D365FO window Send, {Alt Down} Sleep, 500 Send, n Sleep, 500 Send, {Alt Up} Sleep, 1000 ; --------- Go to the next field and back in the D365FO to get into the edit mode Send, {TAB} Sleep, 1000 Send, +{TAB} ; --------- Paste the code Send, ^v ; --------- Switch the window and go back to Excel Send, !{TAB} Sleep, 1000 ; --------- Copy the content of the Description column from Excel Send, {Right} Send, ^c Sleep, 1000 ; --------- Switch the window for D365FO Send, !{TAB} Sleep, 1000 Send, {TAB} Sleep, 500 ; --------- Paste the description Send, ^v ; --------- Switch the window for Excel Send, !{TAB} Sleep, 1000 ; ---------Scroll down to the next record, and repeat the cycle Send, {Down}{Left} } Return ; Press F1 in panic F1::ExitApp

X++ Decorator pattern in Dynamics 365

A couple of times in my career I stumbled upon a development requirement to perform a set of loosely connected actions with a similar interface upon a certain business object depending on parameters or conditions:

Sometimes do this
Sometimes do that
Sometimes do this and that
In future, you may do something else in addition.

In Dynamics 365 for Finance and Operations, this usually results in a hierarchy of classes. The classes are typically bound to some parameter table containing checkboxes or – better – an enumeration field. This enumeration directs the SysExtension class factory which objects to instantiate.
A simple while_select loop traverses the parameter table and executes the instantiated objects one by one. However, if the classes implement more than one action i.e. method, this external loop may become repetitive. I ended up with a certain design pattern which turned out to have a name in object-oriented programming: a Decorator ~~an internal iterator~~.
Errata: as readers pointed out, there is more of a Decorator pattern in this, than an Iterator pattern.

The constructor instantiates an object and gives it the previous object as a parameter, thus forming a linked chain of objects. The external actor only needs to take one parameter – the topmost object in the chain – and to call one operation .doThis() upon it. This .doThis() method is exclusively implemented in the parent class of the hierarchy, and the parent class knows how to iteratively call the objects.

Adding a new grid with a parameter table to an existing form is more difficult than adding new field(s) into an existing table via extensions. If the number of possible actions is countable and small, you may think of de-normalizing the parameter table and making an array field with possible options in one record. Instead of iterating records, the constructor will be iterating fields.

Below is an application of this pattern to a simple task: calculation of the nutrition value of one meal, where a meal may consist of an Entrée, an Entrée and a Main course, a Main course and a Dessert and so on:

				
					class MealCourseAtrribute extends SysAttribute implements SysExtensionIAttribute
{
    MealCourseEnum mealCourseEnum;
    public void new(MealCourseEnum _mealCourseEnum)
    {
        super();
        mealCourseEnum = _mealCourseEnum;
    }
    public str parmCacheKey()
    {
        return classStr(MealCourseAtrribute) + enum2Symbol(enumNum(MealCourseEnum), mealCourseEnum);
    }
    public boolean useSingleton()
    {
        return true;
    }
}

abstract public class MealCourse
{
    private MealCourse prevMealCourse;
    abstract protected MealKcal kcal()
    {
    }
    final public MealKcal kcalTotal()
    {
        MealKcal ret = this.kcal();
        if (prevMealCourse)
        {
            ret += prevMealCourse.kcalTotal();
        }
    return ret;
    }
    private MealCourse prevMealCourse(MealCourse _prevMealCourse = prevMealCourse)
    {
        prevMealCourse = _prevMealCourse;
        return prevMealCourse;
    }
    protected void new()
    {
    }
    public static MealCourse construct(Meal _meal)
    {
        MealCourse mealCourse, prevMealCourse;
        MealCourseAtrribute attr;
        MealCourseEnum mealCourseEnum;
        int i;
        for (i = 1; i <= dimOf(_meal); i++)
        {
            mealCourseEnum = _meal[i];
            attr = new MealCourseAtrribute(mealCourseEnum);
            mealCourse = SysExtensionAppClassFactory::getClassFromSysAttribute(classStr(MealCourse), attr);
            if (prevMealCourse)
            {
                if (mealCourseEnum == MealCourseEnum::None)
                {
                    continue;
                }
                mealCourse.prevMealCourse(prevMealCourse);
            }
            prevMealCourse = mealCourse;
        }
        return prevMealCourse;
    }
    public static void main(Args _args)
    {
        info(strFmt("Total calories in this meal is %1", MealParameters::find().meal().kcalTotal()));
    }
}

[MealCourseAtrribute(MealCourseEnum::Entree)]
public class MealCourseEntree extends MealCourse
{
    public MealKcal kcal()
    {
        return 140;
    }
}

[MealCourseAtrribute(MealCourseEnum::Main)]
public class MealCourseMain extends MealCourse
{
    public MealKcal kcal()
    {
        return 600;
    }
}

[MealCourseAtrribute(MealCourseEnum::Dessert)]
public class MealCourseDessert extends MealCourse
{
    public MealKcal kcal()
    {
        return 200;
    }
}

Dynamics 365 FO may return no records for a certain table, but it cannot return an NULL value for a field; in addition, I would like to spare the caller an if (! parameter) {return}; validation. This is why the caller is always given at least one object of the MainCourseNone type which returns a zero and does nothing. The API then reduces to a one-liner:
… = MealParameters::find().meal().kcalTotal();
here for a main course and a dessert:

Adding a new meal course type e.g. a soup is as simple as adding a new enumeration element to the MealCourseEnum, adding a new array element to the Meal extended data type (but only if the Soup may be ordered in addition to the other courses), and implementing a class tagged with this enumeration element as an attribute:

				
					[MealCourseAtrribute(MealCourseEnum::Soup)]
public class MealCourseSoup extends MealCourse
{
    public MealKcal kcal()
    {
        return 100;
    }
}

Beware of the current limitations in the array fields support in D365FO: they cannot be exposed in Excel or imported properly in the Data management module.
You may download the source code here: TheMealProject

X++ programming blog series

Configuring Austrian and Norwegian per diems in Dynamics 365

Building upon my insight into travel expenses and per diem calculation in Germany, let us consider Austrian and Norwegian per diems side by side. In both countries scaled rates per hour apply, and in both counties rules for the domestic travel and for traveling abroad differ. Both countries can be implemented in Dynamics 365 for Finance and Operations with literally no customizations at all!

In Austria, the first 3 hours of domestic travel are not reimbursed, but once the duration of the trip reaches 3 hours, the employee becomes 4/12 of the full rate of 26,40 euros. These 8,80 euros apply to a trip with a length between 3 and 4 hours, from the 4th full hour the rate increases to 5/12 and so on. From the 11th hour the full rate 26,40 applies. A multi-day trip from the 01.05.2018 10:00 to 03.05.2018 13:10 is divided into 24-hour periods + the number of remaining begun hours: 2 days + 4 hours = 2 + 4/12 of the daily rate = 2,3333 * 26,40 = 61,60 euros.
In Norway, a domestic trip starts monetizing after the first 6 hours, the employee becomes 289 NOK. Contrary to Austria, in Norway there are just 2 steps: one for 6-12 hours of 289 NOK and the full 537 NOK for anything between 12-24 hours. 24-hour periods are counted, but the last day of a multi-day trip counts as a full one as soon as the remainder exceeds 6 hours. I.e. a trip from 01.05.2018 10:00 to 03.05.2018 18:10 gives 2 days + ~8 hours = 3 daily rates = 3 * 537 = 1611 kroner. A Norwegian speciality is a higher overnight stay rate of 733 NOK, while no overnight is 537 NOK.

Should the employer or customer provide free meals, the daily rates begin to decrease. In Austria, a domestic lunch leads to 50% reduction and the dinner reduces the daily rate to zero: 50% – 50%. The percentages refer to the full rate of 26,40 EUR, and a single lunch reduces a short 3 hours trip to zero: 4/12 – 1/2 = 1/3 – 1/2; the per diem amount may not become negative, though.
In Norway, a breakfast gives 20% of reduction, a lunch counts 30% and a dinner is 50%: 20% – 30% – 50%. I assume the percentages apply to the respective rate of 289, 537 or 733. Let us put the numbers on a diagram (a non-overnight Norwegian stay is represented):

Foreign travel is peculiar: for Austria one meal abroad leads to no reduction, while 2 meals (lunch and dinner) reduce the foreign daily rate to 1/3 i.e. the reduction is never more than 66,67% per day. For Norway, even a hearthy breakfast abroad weighs less than a hyggelig breakfast at home: 10% – 40% – 50% 😉

Both countries maintain tables of detailed per diem rates for numerous destinations abroad, and account for some expensive capital cities.
In Austria the same X/12 rule applies to every destination abroad.
In Norway, the first 6-12 hours give 2/3 of the total foreign rate, and more than 12 hours give the full rate. This time, no exception is made for an overnight stay.
These rules can be visualized as follows:

Austrian configuration

The Minimum hours for per diem parameter is 3 hours (Expense management > Setup > General > Expense management parameters), Meal percent = 100, and Base per diem calculation on = 24 hour period.
The deduction for the breakfast used to be a grey zone in Austria: in the past there was an own percentage of reduction for breakfasts. This regulation was eased, and now only lunches and dinners count. In case of a travel abroad, one lunch or one dinner do nothing; only 2 meals lead to a reduction of 66,67%. I suggest using the mode Calculate meal reduction by = Number of meals per day, while preventing the breakfast entry by the Expense management > Setup > General > Expense report fields setup. The user should not be entering more than 2 meals per day.

There can be as many Per diem locations as there are distinct rates per country and city. The location “AUT” for the domestic travel features the following rates and tiers (Expense management > Setup > Calculations and codes > Per diems):

The Meal percent per number of hours refers to the portion of the full Meals rate of 26,40. The Per diem rate tiers apply to Both, i.e. to both the first day between 3 and 24 hours, and the last remainder of 3-24 hours. For any day (24 hour period, to be precise) in the middle, the General settings apply. The Percentage reduction for 1 meal and Percentage reduction for 2 meals should be tailored carefully, because the reduction amount may not exceed the given meal allowance amount and Dynamics gives an error message on the expense report screen: “Total meal reduction cannot exceed total per diem.”

Here is the result. A domestic business travel from 01.05.2018 10:00 to 03.05.2018 18:10 of 72,60 euros = 26,40 + 26,40 + 8,80 is reduced by 50% on the 1st day, by 100% on the 2nd day and by 8,80 euros on the 3rd day because there is nothing more to reduce there:

This amounts to 13,20 EUR.

The same trip to Sweden brings significantly more: 42,90 + 42,90 + 14,30 is reduced by 0% on the 1st day (one lunch alone does not count), 66,67% on the 2nd day and 0% on the 3rd day = 71,50 EUR:

Norwegian configuration

For Norway, the Minimum hours for per diem parameter would be 6 hours, while Calculate meal reduction by = Meal type per day, because the breakfast, lunch, dinner all lead to a reduction by a specific percentage (20% or 10%, 30% or 40% and 50%, respectively). Breakfasts should be enabled for entry. The Norwegian krone is a relatively light currency, the 0,01% precision of Dynamics’ configuration starts making a difference, and Per diem rounding needs to be set to Always round up, thanks to all the rates aligned to whole kroner.

In the Per diems configuration for the domestic location “NO“ I assume an overnight stay for every period longer than 23 hours, while the day of the departure is not overnight, i.e. the number of overnight stays is the number of whole 24 periods on site, and any last day longer than 6 hours gives 537 NOK:

With this setup a domestic business trip 01.05.2018 10:00 to 03.05.2018 18:10 which involves 2 overnight stays at the hotel and 2 breakfasts, 2 sponsored lunches and 1 sponsored dinner gives 733 + 733 + 537 reduced by 30%*733 on the 1st day, by 100%*733 on the 2nd day and by 50%*537 on the 3rd day = 781 NOK:

The above assumption with regards to the overnight stay may not always be correct, and this may require an additional virtual per diem location for Norway. When travelling abroad, there is no ambivalence, and the configuration reduces to the following (note the different reduction percentages 10-40-50):

On a domestic business trip from Norway to Sweden from 01.05.2018 10:00 to 03.05.2018 18:10 this evaluates to 750 + 750 + 750 reduced by 40% on the 1st day, by 100% on the 2nd day and by 50% on the 3rd day = 825 NOK:

Author Archives: Eugen Glasow

Copy-paste with keyboard script 2: from Excel to D365FO

Copy-paste with keyboard script 2: from Excel to D365FO

X++ Decorator pattern in Dynamics 365

X++ Decorator pattern in Dynamics 365

X++ programming blog series

Configuring Austrian and Norwegian per diems in Dynamics 365

Configuring Austrian and Norwegian per diems in Dynamics 365

Austrian configuration

Norwegian configuration

Expense management blog series