Author Archives: Soren Klemmensen

About Soren Klemmensen

Another Microsoft Dynamics 365 Business Central geek

Get Currency Exchange from Bank of Canada

Did you get your Currency Exchange Rates from Bank of Canada? The format just changed as of June 1, 2017 and you now need to set it up via the new format.

Go to the Currencies page.

2017-07-02_14-09-28

Click on Exchange Rate Services

Select New and create one with the following settings2017-07-02_18-56-18

Once you have populated the correct URL as seen above you can use the assist button in the Source Field to populate the nodes from the XML file in the lines.

For the Starting date you need to use the “US_DATE_FORMAT” Transformation Rule. If you don’t already have it you can look in a Cronus Database. It is already defined in the demo data.

To get the currency code you need to pick the 3rd to the 6th character from the FXUSDCAD text string. You can see the string when click the Assist button to pick the Node.

2017-07-02_19-05-50

You can do this by creating your own transformation rule looking like this.

2017-07-02_19-08-46

As you can see you can test your transformation rule in the bottom of the page by inserting your text and hitting the update on your right. The result will show in the result field.

The bad thing here is that I did not find a way to import multiple currencies with the new format from the Bank of Canada. It doesn’t look like the functionality supports this new format very well or the format is just a little strange.

If you need to find the exchange rates that works for you, Bank of Canada has a website explaining how their service works.

http://www.bankofcanada.ca/rates/exchange/

Hope this is use full to you all.

 

Running PowerShell from VS Code

Did you know you can run PowerShell from Visual Studio Code?

One of the biggest strength of VS Code is its diversity and the fact it is very light weight. I have already been using VS Code to do a lot of things with Git for a while because it natively integrates nicely with Git which is a huge plus.

Now a while back I tried to use PowerShell with VS Code but it was just not working well at the time, but recently I tried again and things have changed a lot. If you install the PowerShell extension from Microsoft you can now edit and run PowerShell scripts natively inside VS Code just like you can from PowerShell or PowerShell ISE.

You can find the extension here:

https://marketplace.visualstudio.com/items?itemName=ms-vscode.PowerShell

VS Code – One tool to rule them all.

Is My Record Temporary (Part 2)?

Back in July I wrote a blog on how to test if Records are temporary or not.

You can read it here.

Microsoft has now added it so you can directly test if a record is temporary or not with out any extra coding.

You define a record and like “Cust” as a variable of Record based on the Customer table. Now you can write things like.

2017-06-25_14-58-20

Thanks Microsoft. Great to see you implement my suggestions.

 

Transform or …

Looking at the vast majority of partners today they still sell one off project in east, west, north and south. Partners have gotten so comfortable doing what they have always done that they continue to do it because it seems like the sure thing to do.

However the world is rapidly changing and customer increasingly see through the cost of maintaining custom software. Customers typically sign off on traditional project, so they now own the bugs that are left in the custom software including the burden of fixing them. The software is also not improving with an ever changing industry requiring better and smarter ways of getting things done including new regulations and opportunities.

Customer get stuck back on older versions with costly upgrades or reimplementation’s as a consequence. This means the customer typically every 7 years or so will consider an upgrade, reimplementation or a new product. Any one of them require training, lots of time to consider the options, dedicated staff to manage or be part of managing the project, new hardware, roll out, many rounds of testing, headaches and money.

So why do customers and partners allow it?

Increasingly customers say no. That is not where we need to be. Customers wants to focus on their business just like partners should focus on delivering value to customers. In other words partners need to transform their business model and delivery or …

Today we are heading to the cloud. We are heading agile.

Partners should focus on their industry knowledge and create small or big apps to resolve customer issues so customers than can pick and select to add together one way or another. We are heading to a world that works like the great Danish and world famous toy called LEGO which is a acronym for the Danish words “Leg Godt” which means “Play Well”.

So what does this mean for the channel.

Partners that don’t have industry knowledge have an issue. My advise is get it or partner with someone who has it but no product knowledge.

Secondly change the way you deliver software. Look in to SCRUM or some Agile form of delivery. It is proven to work, but there is no half way of doing it. You either transform your entire organization to work with SCRUM or you don’t. Doing it half and than saying it doesn’t work is not an option. SCRUM only works if you practice it through out your company.

Things are changing. Are you?

PLAY WELL my friends.

 

 

A couple of “secret” switches in Dynamics NAV

Unless you are very new to Microsoft Dynamics NAV I am sure you know of the General Ledger Setup.

This is where we setup most General Ledger related settings such as Check G/L Account usage, the local Currency Unit (LCY) ,if VAT is in use or if we are using Unrealized Tax.

Many might not be aware that this also has a few hidden settings.

If you run table 98 General Ledger Setup from the Object designer you will see a few more fields then what the users sees in the standard page in your client.

A few that I have found very use full from time to time are:

Field What the helps say
Mark Cr. Memos as Corrections Specifies whether to automatically mark a new credit memo as a corrective entry.

 

You can use the field if you need to post a corrective entry to a customer account.

 

If you place a check mark in this field when posting a corrective entry, the program will post a negative debit instead of a credit or a negative credit instead of a debit. The Debit Amount field (or Credit Amount field) on the relevant account will then include both the original entry and the corrective entry, which will have no effect on the debit (or credit) balance.

Amount Decima Places Determines the number of decimal places the program will display (for example, on invoices and in reports) for amounts in $. The default setting, 2:2, specifies that amounts in $ will be shown with two decimal places.
Unit-Amount Decimal Places Determines the number of decimal places the program will display (for example, on invoices and in reports) for sales and purchase prices for items, and for sales prices for resources in $. The default setting, 2:5, specifies that sales and purchase prices in $ will be shown with a minimum of two decimal places and a maximum of five decimal places.
Amount Rounding Precision Specifies the size of the interval to be used when rounding amounts in $.

Amounts will be rounded to the nearest digit.

Unit-Amount Rounding Precision Specifies the size of the interval to be used when rounding unit amounts (that is, item or resource prices per unit) in $.

 

Use this field to have the program round item prices on invoice lines when you sell to a customer or buy from a vendor in $. The program rounds the amount in the Unit Price field for customers and the Direct Unit Cost field for vendors, on the invoice line. Amounts will be rounded to the nearest digit.

Especially the “Unit-Amount Rounding Precision” have come in great use many times where customers have special rounding requirements on their unit amounts (things like Item Costs, Prices with more).

One setting to rule them all.

 

Let’s clean up NAV #12 – Next Counting Period

My good friend Luc van Vugt started a little miniseries on his blog with things we can do to clean up NAV. What a great idea, so here is something I have noticed.

In NAV 2015 the Next Counting Period was replaced by 2 date fields after I reported it. The field was used to store the date filter for the next counting period, but if you used counting cycles and had users with different date setting in their regional settings you quickly got quite the mess to figure out. You should of course never store Date filters in text fields unless they are used with in the same transaction ensuring that the user have not changed regional settings in the meantime.

After this was done the Field “Next Counting Period” Text(250) which exist in Table 7380 Field 8, Table 5700 Field 7382 & Table 27 Field 7382 was never removed in 2015. It is not used in the code or in the UI to my knowledge, so maybe it is time to clear it up and get rid of it permanently.

Should there be a need to display the date filter we can always display it based on the 2 new date fields by calculating it on the fly which would ensure it is consistent with the uses current regional settings.

It is a low impact issue as the field has no use today and it is easy to fix not to mention it reduces record length of a tables considerable.

Why Microsoft should add a “Surrogate Key” to Microsoft Dynamics NAV

If you have meet me at a conference last year I am sure we have discussed my favorite topic: “Surrogate Keys”

Why is this so important to me?

The key message we have heard the last few years at every conference has been repeatable solutions, but if you want to create repeatable solutions it all starts with repeatable code.

So what has that to do with Surrogate Keys you might ask?

Well Surrogate Keys are one way (and until today one of the best I have found) to create functionality that is completely generic and reusable. Back a few years before I had even formalized my thoughts about this I called it “LEGO Programming” (okay I am Danish and love LEGO. Deal with it J) because I wanted to create code similar to these wonderful building blocks that can be placed anywhere in a construction and they just work.

LEGO is just one of these wonderful toys that you can be very creative with.

Why should my code be any different?

Well back to why Microsoft should look at this as a standard part of the platform:

  1. They want us to create generic solutions, so give us generic tools to do it with. Yes we can use things like RecordID, but a surrogate key is much more flexible and doesn’t depend on any record data. It also doesn’t suffer from renaming issues…
  2. My implementation has some faults. If you would use the INIT function it gets cleared out which is should not unless you actually insert a new record. This is standardly done twice in Table 37 and 39 as an example. We can work around it quite easy, but we should not have to do so. A proper implemented Surrogate Key in the platform would resolve that issue.
  3. Too many places in the application things have been done locally while using global wording causing things that I find highly regrettable. Take the table 5062 “Attachment”. Just based on the word you would assume it would work anywhere in the application and not just in a small local area of the functionality. Why should attachments be limited to one record when it could be so much more?
  4. A generic platform implementation of this would save the community a lot of work of adding Surrogate keys to all tables where it is needed.
  5. It is so simple to do compared to the benefit it would bring the community.

Just to be clear I am not pointing at anybody with my examples. They were developed in another age and great at the time. All I am trying to say is that we need to change gears.

It is time to start thinking generically and hold ourselves to a higher standard.

Copy/Move Data placed in Surrogate Key Based tables

An example of how to do things very generically when using the Surrogate Keys Design Pattern is when you need to copy or move data attached to one record to another record.

Looking at classic design you often see different functions created to copy or move data in Subsidiary Tables between different master records. Take an area like Comments (My favorite example) where we have 29 different implementations of it (at least if I can count correctly). Let’s say I would like to copy or move the comments between any of these 29 implementations to any other of them. Now with 29 tables in play I would need to create a function from the first table and to all the other 28 tables. Than do it again for Table 2, 3, … & 29.

Yes that is a lot of functions and not exactly a small task.

So what is the difference if I had used a Surrogate Key and created only one generic implementation of Comments?

Well my copy function could look like this:

Because I am always using the same record to store my Subsidiary Table Data it is very easy to copy from one Master Record to another.

To make is even more generic and because I am lazy by nature I also added this function.

So now I can copy between 2 Records, 2 RecordRefs or a combination of a Record and a RecordRef without knowing anything about them.

This last function relies on a few other functions in the same codeunit.

These 2 functions in turn relies on my RecordRefLibrary Codeunit and the function

And my SurrogateKey Management Codeunit with this function.

Yes I use a fixed Field No for all my Surrogate Keys and no it is not 59999, but I had to create an example for this blog.

Before you ask why I use a fixed number I better address it.

There are 3 reasons for it:

  1. It is the easiest
  2. Once (Not when or if) Microsoft gets around to make their own version of a surrogate key in NAV it would make the most sense they did something similar
  3. It makes (at least that is my hope) for an easy and generic upgrade script once this finds its way to the standard platform

Let’s get back to the real issue here. Basically in a few lines of code we have resolved all our coding needs for copying comments between any master records and it doesn’t matter how many Master Tables comments are added to in the future. This will always work.

Creating repeatable implementations starts with creating repeatable code.

 

 

Find your Table ID

Using many hours programming with my favorite Design Pattern “Surrogate Keys” I often need to know simple things that can be a little hard to find at times.

One of these is to know the Table ID of any record I might be handling at any given time.

All the tables that use the surrogate key pattern use a primary key based on “Table ID”, a “Surrogate Key Reference” & maybe a “Line No.” or “Code” depending on what the table is hosting of data. In other words I often need to know the Table ID to filter correctly based on the record I am calling from.

I handle this with the following Code:

The ReturnTableID function takes a Variant as Parameter and calls Variant2RecRef to get it transformed to a Record Ref no matter if a Record or a Record Ref was passed to it. After that the function Returns the RecRef.Number which is the Table ID of whatever was passed to it.

This way I can call the first function with any Record or RecordRef and get the Table ID returned.

This creates a completely generic function that always returns your Table ID.

This is a fixed part of my RecordRef Library Codeunit and now hopefully of yours too.