Optimizing your Aged Accounts Receivables Report

Doing numerous upgrades from an older version of Navision to NAV 2015 and 2013, one common complaint is how slow the reports are running. This is especially true for larger reports like the Aged Accounts Receivables, Aged Accounts Payables , Inventory Valuation, etc.

The old reports that took a long time, such as the Inventory Valuation report, will still take a long time. It doesn’t matter what version you go to. However, there are some reports that used to be quick, but is slow after the upgrade.

One of these reports is the Aged Accounts Receivables report (Report ID: 10040 – Aged Accounts Receivable).

The Breakdown
CAUTION: I’m about to get “programmer”. If you want the faster report, just skip down to the bottom and download the object.

Removing the Data Type of Column from the report, we get the following DataItem that the report loops through:


On initial look, the report looks simple enough. There are 3 data items:

  • Customer – The report loops through the customer record to see which customer we need to calculate the aging for
  • Cust. Ledger Entry – For every customer record it finds, it will loop through the all of the customer ledger for that customer. For any customer ledger that has a remaining amount, it’ll put it into a temporary table
  • Integer – This dataitem loops through the same records that are inserted into the temporary table on the Cust. Ledger Entry dataitem and summerizes the information to display in different aging “buckets”.

The Problem
The reason why this report is slow is if you check the DataItemTableView property on the Cust. Ledger Entry dataitem, you’ll see that the report is looping through ALL of the customer ledger for that customer.


This report will run fine if you’re A/R aging is small. However, this report will get slower as time progresses with more transactions. Worst, it’ll consume all the memory on the server and force you to restart.

The problem becomes real apparent when you have EDI customers that are running hundreds of invoices per day.

The Solution
The idea of the original A/R aging report is correct. Basically, look at the remaining amount based on the date criteria; if there is a balance, then it goes into the calculation.

The problem is that it’s not running any type of filters to exclude old transactions that has no relevance in our calculation.

To address the performance problem, here are the main things we will need to do:

  1. Look at only transactions that are marked as Open
  2. If the report is to be backdated, look into the only the history that pertains to the date criteria

First thing we do is to set the proper DataItemTableView property with the filter of Open.


Then we need to add a new Detailed Cust. Ledger Entry dataitem to look at the application history of our A/R transaction:

The Property:


The Code:


Basically, we’re limiting the reads of the database to only open transactions and the subsequent A/R applications from the Aged as of Date set on the report.

Here are the report objects and the text file for your reference:

Not sure what the developer at Microsoft is thinking when programming this report. Aged Accounts Receivable/Payable is one of the most data intensive reports next to the inventory valuation. Reading through every record just does not make sense.

Yes, it’ll work in the short run, but give a few years and the report will slow to a crawl, which is already experienced by customers upgrading.

Breaking up with Ex-Girlfriend vs. Implementing Dynamics NAV

The Decision
It’s time.

He has been with you for a long time. In the beginning, he was great. He did everything you needed him to do and you gave him anything he wanted. In return, he gave you what you needed as well.

But now, things are becoming toxic. It doesn’t matter how much time and effort you give him, he is just dragging you down, making you slow, and holding you back. What made you happy 5-10 years ago, is just not enough for you anymore. You’re looking for more, and it’s something more that he just cannot provide.

The New Relationship
The change is hard, but you know in order to grow, you need to change. It’s not just for you, it’s also for the people around you.

So you went for something new.

– He is smarter
– He works faster
– He looks better
– He makes you more efficient
– Any requests you throw at him, he will fulfill
– He will accomplish a lot more for a lot less

There will be a learning curve for you to get used to the way He moves, the way he looks, the routines you will try to figure out. There will be something that you don’t like, but you are willing to work with him on it. And he will change.

The Breakup
Anything new in your life requires some getting used to. Breaking up is hard. There are a lot of memories you’ve shared. Sometimes, those memories you’ve shared will come back.

– You missed the way he looks
– You missed the way he moves
– You missed having the routines you’re so used to with him every day

There are times where you’ll question yourself whether you’ve made the right decision. There are even times when you want to go back to him.

You’ll only want to remember the good things that he did for you and you will be willing to “forget” all the bad things he did. Painfully, sometimes you needed to be reminded how bad he was for you and your business.

Sure, you will miss what was there before, but we all need to move on. And we all know he’s not coming back. You’re with someone new now.

You needed to retire your old software and implement Dynamics NAV for your business in order for your business to grow.

I guess the similar concept described above can be said about your leaving your old significant other…

Process vs. Technical Questions

If you’re an IT or Finance person working in a company that uses Dynamics NAV. First, congratulations! There’s no other ERP product for the mid-market that has the growth (and growth potential) as Dynamics NAV.

Having said that, often times you will be fielded with questions about NAV and it will be difficult to respond. This is especially true when you’re supposed to be the “expert” of the software within your organization. Of course if you really get in trouble, you have your Dynamics NAV partner to back you up so you’re never really alone.

However, in order for your NAV partner to make you look good in front of your peers, you have to phrase your question in a way to get a “on the point” response. Don’t assume your NAV partner to know what you’re thinking, because trust me, being in this business since 1999, I still don’t know what you’re thinking. I did, however, developer a better sense of asking the right questions, but that’s probably a topic for a separate blog post.

When fielding questions, there are generally 2 different types of questions. One is technical question and the other one is Process.

What is Process and What is Technical?
I’m glad you asked that question. Here’s my definition of both

Process Question: A question that’s related to the daily/weekly/monthly workflow of a particular operation. For example, reconciling inventory to G/L at month end. (Non-linear)

Technical Question: A question that’s indirectly related to the daily/weekly/monthly workflow of a particular operation. For example, an error message when you’re trying to post. (Linear)

Troubleshooting the Process
Which of these questions is more straight forward? Of course the technical questions. It follows a linear path. These types of problems can usually be resolved using a debugger.

As the Dynamics NAV go-to person in your company, I bet most of your time is trying to figure out the process questions. The Non-linear questions that can have multiple paths.

It’s important to identify what is process and what’s technical question. If it’s a process question, you should get the experts or the person responsible for that piece of information involved. As much as you like to help out the process by request or making little modifications that solves their process question, it’s not the right way to go about it.

For example, over receiving. Some times the warehouse will complain that you cannot over receive. As an IT guy, yes it’s easy to bypass the check process and just modify the quantity.

However, by doing this, you’ve cascaded the problem and implemented a flawed process that people will get used to.

In this example, why is it flawed? Among the various reasons, one of the biggest problem is because when the vendor send you extra items, who’s responsible for it? Do you have to pay for it? Should the vendor give it to you for free? What if the buyer did not want to receive the additional items because it messes with their budgeting? You get the point.

Changing the Process
That’s not to say that processes cannot be changed. It’s really about how you go about changing the process.

The first step is to get with the people responsible for the process. For some reason there are some internal IT and finance people that simply will not do this. They want to assume they know what they’re doing or they do not want to ask the people responsible questions. This is absolutely a MUST!!

In our example, the purchaser should be involved and the warehouse manager should be involved. Sit with them and describe this problem and ask the all important question: “how do you want to resolve this?”. It’s up to them to define a process that satisfy the buyer and their vendors, the warehouse and the receiving process. Once that process is agreed upon, then it becomes a technical question.

As the NAV guru in your company, you’re most likely swamped.

We want resolve problems and issues to “get them off our plate” as soon as possible that sometimes, we mistakenly treat process questions as technical questions. I know because I’m guilty of that as well. This is not the right way to go about doing implementations and certainly not the way you should go about addressing the needs for your company.

Because ususally when IT gets involved in process questions, developers tend to find the shortest way to resolve the problem, not the right way. As always, finding the shorest way will always require exceptions in processes. Exceptions are fine until there are exceptions to exceptions. Then exception to exceptions to exceptions. This is a sure fire way to complicate your business process and have each department not taking responsibility and point fingers at each other.

Why am I doing Dynamics NAV

Recently, one of my employees challenged me about what my goal is for the company. I was taken a back because I never thought about this. I knew why I was working in this industry but I never thought it was relevant to tell anyone.

What started as a short e-mail paragraph turned into a… Well… an essay. My original intention was just to circulate within the company, but I haven’t made an actual confession on this blog in a while.

My Background
The times I’ve spent with my family are my most valuable moments. Why? Because when I was growing up, I didn’t have these moments.

My father owned a business and made a lot of money. My mom was a pretty good mom when she was present. Looking at it from the surface, everything was nice. But we had a lot of problem as a family. To this day, I never understood why my dad thought money and status was so valuable or why my mom thought gambling was more important than her kids.

I don’t blame them for what happened because it’s not their fault. They had to do what they did in order to survive.  Just like what my brother and sister had to do in order for us to survive. We’ve all made decisions that we’ve regretted looking back; however, at that particular moment in time (and that particular environment) it was the best possible decision we can make.

When I was probably 13 or 14, and I don’t remember what exactly happened, I cried in bed and made myself promise that one day I will start a family of my own and I will be a good husband and a good father.

I will not let my kids to have the same kind of childhood I had. I will always be available when they need me and I will be their father. I will love and cherish my wife with all my heart and never let her feel lonely.

The Miracle
I don’t know why. Maybe God took pity on me. I was able to achieve what I wanted. I met my wife and she gave me exactly what I was missing in my life. A stable family.

Although striving to become a good husband and a good father is still an ongoing process, there’s not a moment where I don’t feel blessed and feel that I’m incredibly lucky.

The Improvement
Because of my past, I frequently think about how we can finish our work in a more efficient and accurate manner. So we can quickly go home to our loved ones.

Every time I see people work until late at night, I always think about that person(s) waiting for them at home. But they couldn’t go home; they had to finish their work.

I want them to use that time at work, and instead use that to spend time with their kids and loved ones. Because time, once it’s gone. It’s gone.

Why ERP?
So why do I want to get into the ERP software industry?

My answer is that it was not my intention. You remembered when I said that I was lucky earlier? I think I really am.

When I was a student, I only had a student visa. After I graduated, I needed a company that was willing to sponsor for my permanent stay in the US. My grades weren’t that great and my social skills were lacking, but I made sure my determination and persistence was there. After countless interviews and networking/recruiting events, no companies hired me.

My lucky break came when one of my friends gave my resume to a company that sold Navision (my friend worked in a company that bought NAV). This company was very special, it was their strategy to hire a tone and fire quickly you did not work out within a few months.  During my 2.5 year at the company, I must’ve seen 30-40 people come and go, and we were only a 10 people company.

At the time when I got started, we had the Y2K problem. Within a month, there were on average of 3-4 implementations going on. It was trial by fire. Tiring, but fun.

The Revelation
I remembered my first year in this company. When I go to client sites to implement NAV, I couldn’t believe why people needed to work such long hours. What were they working on? Why does it take such a long time?

I always thought, if they can finish their work could they go home earlier?

This gave me the insight into how I can use software to help other people. I wanted to learn more about ERP (in particular Dynamics NAV) so I can help others.

This will become my tool to help others to have the opportunity to give what I’ve always wanted when I was growing up. A happy family.

Chinese Language Pack for Dynamics NAV (Navision)

Recently, we started a branch in Taiwan. One of the reasons, amongst other reasons, I started this branch is because it’s country where I was born. So in a way, I feel a little obligated to help the companies in my home country to be more efficient and enjoy the benefits in what Dynamics NAV has to offer.

There are many lessons learned in terms of starting a company in a different country; the local culture and views on technology, how to speak and appeal to the local management, etc. I’m sure I’ll blog about in a future article. For now, I want to share with you guys the many challenges when doing translation in Dynamics NAV (Navision).

One of the Technical Challenges
When selling into a local market, one of the things that is mandatory is whether you have the local language pack. As you know, Dynamics NAV is a software that’s utilized all over the world. Microsoft also supports languages for many countries out of the box.

Unfortunately, the Chinese language pack is not one of them. This means we had to create our own.

The Process
The process involves creating caption for all the fields, errors, messages, reports, etc.

There are 3 main parts for creating translations that works in Dynamics NAV.
1. Translate the platform by translating the resource file for each DLL file.
2. Translate all the captions by adding a new language into captionML in each object
3. Localize development environment by translating .stx and .etx files. These 2 files have to be sent to the Microsoft regional office and sealed by Microsoft. (Only required if you want the Development Environment to be in Chinese as well)

Fortunately, Microsoft provides tools that allow you to make your own localization. In our case, we did this for the Chinese language. The step by step instruction on how to do this is explained here. Basically download the PLLP (Partner Localization and Translation Licensing Program) and follow the instructions very carefully:

You will spend most efforts on translate all the captions. However, this part can be accelerated a little bit by exporting all the captions in txt file. Once you translate all the exported captions with A1033 code (English label) to the corresponding language code (e.g. A1028 represents Chinese Traditional), import the txt file back to NAV. Then it’s done!

Introducing the Chinese Language Pack for Dynamics NAV 2013 R2
As you will see, we did this for Traditional Chinese, but the nice thing about this is that it can be easily translated to Simplified Chinese.

Here are the results:

Chinese Dynamics NAV Selection

Chinese Dynamics NAV Selection

Chinese Dynamics NAV Role Center

Chinese Dynamics NAV Role Center

Chinese Dynamics NAV Departments

Chinese Dynamics NAV Departments

Chinese Dynamics NAV Customer Card

Chinese Dynamics NAV Customer Card


Restarting Job Queue when it Hits an Error on Dynamics NAV 2013

Job Queue
In Dynamics NAV 2013, you can setup job queue to automate processing of tasks. Popular tasks that are automated are things such as Adjust Cost – Item Entries, EDI processing, Reporting, etc.

Setting up job queue in NAV2013 couldn’t be much easier can a step by step instruction can be found here.

The Problem
When the job queue runs into an error, it will never get picked up again. This means that while the Job Queue is an automated process, the IT manager will need to monitor this every day to make sure every process is running.

I know what you’re thinking, “This does not make sense!”. I fully agree.

There are some processes where the error is fatal. This is the reason why you would not want to have it run again. However, there are some situations where the error occurs when the table is locked by another process, in this case, you absolutely need to have it restart again.

This is especially true when you process EDI orders. You have to send back acknowledgment and/or confirmation within a certain timeframe or else you’ll get charge backs. Having the job queue error out because of table locks does not make too much sense.

The Solution
The problem lies in the Job Queue Dispatcher codeunit (448). If you go down and find the local function GetNextRequest, you’ll see that for some odd reason, the process is only looking at any statuses that are Ready.JobQueueReadySo we will need to modify the code to scan for the error entries as well.


Depending on what you use the Job Queue for, I would include job queues that are In Process. The reason is if it’s running and someone stops the job queue, it’ll stay stuck in the In Process status.

By setting this, it’s important that you set the Max No. of Attempts. You don’t want the job queue to keep running if there really is a critical error.

A special thanks to Rafael Rezende for helping me with this!

Lot Accumulation Period vs Rescheduling Period for Dynamics NAV Manufacturing

With the release of Dynamcis NAV 2013 and NAV 2013 R2 (Navision 7.0 and 7.1 respectively), there has been a few improvements in the existing functionality. One of the more notable ones that I’ve been meaning to write about is the enhancement to how NAV create the planning worksheet.

Ordering for Manufacturing
Most manufacturing companies will plan what they want to make within a period. How long that period is will be determined by the company running the plan. Speak to any manufacturer and they will tell you that they are a Make-To-Order manufacturing company. This means they don’t, or at least intend to, make any items if there are no existing demand for the item.

Typically, the Reordering Policy for these types of companies will be set to Lot for Lot. Meaning that when MPS and MRP plan is ran, it will look to consolidate orders within the period and make the most efficient production order(s) and purchase orders to satisfy the demand.

The Old
In the versions prior to Navision 8.0, you only had the Reorder Cycle. So if the demand does not satisfy the 2 weeks supply, it’ll just tell you to cancel the order and create the new order for the new demand requirement. Well, if you stay in any manufacturing environment long enough, you’ll know that it’s some times impossible to change what’s already scheduled or released to the shop floor.

Using one field is fine if your aggregation period is the same as your tolerance for rescheduling, but that’s not typically the case.

The New
In Dynamics NAV 2013, they split the Reorder Cycle into 2 different fields. Lot Accumulation Period and Rescheduling Period.

The Lot Accumulation Period replaces the Reorder cycle in that it will aggregate demand for the 2 weeks out from the time you run your plans. The Rescheduling Period basically looks at the orders created during these 2 period, and if there are any changes in the demand or supply, instead of asking you to cancel the production/purchase order, it’ll ask you to reschedule it.

This may not seem much of a big deal, but when I first saw this, I was incredibly happy. For a long time, users had to deal with suggestions on canceling and creating new. So instead of carrying out these action messages, the users basically ignored these advices.

Finally Microsoft did this. Now if we can get more of our requests done in the next release, everyone will be even happier.

Warning About Enabling Directed Pick and Put-away in NAV

When you turn on Directed Pick and Put-away to enable Warehouse Management in Dynamics NAV and you use multiple units of measure (this applies to WHM users using only 1 unit of measure as well), there will be some instances where the Quantity and Quantity (Base) will not match. This will result in the quantities in the Item Ledger be different than the Warehouse Entry.

The problem was first discovered in version 5.0 and is still a problem in NAV2013 R2.

If you want to enable Directed Pick and Put-away, you will need add this fix in the code.

Table 7302
WhseActivLine.SETRANGE(“Variant Code”,”Variant Code”);
WhseActivLine.SETRANGE(“Unit of Measure Code”,”Unit of Measure Code”);
//WHM Fix – Begin
WhseActivLine.SETRANGE(“Action Type”,WhseActivLine.”Action Type”::Place);
//WHM Fix – End
COPYFILTER(“Lot No. Filter”,WhseActivLine.”Lot No.”);

In addition, you have to modify this codeunit marked in bold:

Codeunit 7313
//WHM Fix – Begin
IF (BinContent.”Max. Qty.” <> 0) AND
(BinContent.”Max. Qty.” * BinContent.”Qty. per Unit of Measure” >= BinContentQtyBase * BinContent.”Qty. per Unit of Measure”)
//WHM Fix – End

Special thanks to Brian Rocatis for the fix.

Recap of Directions 2013 – Dynamics NAV 2013 R2 is Official

Dynamics NAV 2013 R2 is Officially Released
At the keynote session, Paul White officially announced that Dynamics NAV 2013 R2 is officially released.

The main theme of this year’s Directions is the tight integration between Office 365, Windows Azure, and deploying Dynamics NAV2013 R2 in the cloud.

What Does “tight” Integration Mean?
You know the story. With every release of Dynamics NAV, Microsoft has been touting the release enables Dynamics NAV to be tightly integrated with Office. For years, I never understood what that meant. Office is office, Navision is Navision. To make these 2 talk to each other, you had to cut off an arm and a leg and do a rain dance to start the first steps of integration between the 2 products.

So when I first heard say that Navision is now tightly integration with Office 365, in my mind, I thought “here we go again”. To my surprise, the integration now a lot more seamless. Now to be honest, I was not impressed. The reason is because when I first heard about integrating Office and Navision, my impression of it is what they showed us as of right now. Having said that, there are still a lot of improvements to be had but during this session, I actually felt comfortable and (more importantly) worthwhile rolling this out internally within my organization and recommending it to our clients.

Obviously, words cannot clearly articulate what this “tight integration means. So we will need to wait until Microsoft release more “How Do I” videos on MSDN or Youtube.

Interesting Points From the Conference
There are a couple of interesting points that I got from this conference:

Dynamics NAV now has 98,000 customer installs worldwide!

This number just bellows me away. Wasn’t it just 2 years ago that Microsoft reported that the installation was around 80k?

On the technical side:
– Dynamics NAV 2013 and future releases of Navision can now just do technical upgrades (not full upgrades) Once you’re on Dynamics NAV2013, future upgrades will be easier because you can now upgrade databases using Shell commands.
– Use Query Objects as a datasource to push data into Excel to create reports and BI type analysis

On the lighter side:
– The food this year was faboulous. It was a tremendous improvement over last year.
– Claus Lundstrom has issued a public challenge to me stating that he can create/modify a RDLC report faster than I can do so in the classic client. Look for this in my next blog post.

It’s nice to see some fellow Microsoft MVPs in this event. I had to hunt down Matt Traxinger because he refused to introduce himself at all during the event. I only met him by accident as I happen to walk past him getting a beer.


From the left to right: Mark Brummel, me, Matt Traxinger, Claus Lundstrøm

The person missing here is Rashed Amini. I couldn’t find him even though he’s the tallest amongst all of us.

Why You Cannot Find a Good Service Based Solution Center

The Conversation
Recently, I had a meeting with an owner of a company that I thought was going to ripe me a new hole. This company originally purchased NAV from another solution center and overpaid for subpar service. Problems steming from wrong advice given to unnecessary modifications causing them delays in their operations. Noting this, they went to another solution center that did a wonderful presentation, but eventually suffered the same treatment as the original solution center that sold them the product. Basically, non-existent support and overbilling.

They eventually found us and after my interview with the owner of this company, I went through our company philosophy, how we approach customers, and what our princples are. We’re able to gain the trust of the owner and we soon put him at ease that we were the people they were looking for.

Then, the bombshell questions came. “Why are there no good company that service Navision (Dynamics NAV) out there?”. Followed by, “why didn’t we buy the software from you?” I have to admit, the 2nd question caught me off guard.

The Industry
It’s really not fair to say only Navision (Dynamics NAV) has this problem. The problem really applies to all of ERP industry, regardless of whether you sell Great Plains (Dynamics GP), SAP B1, Epicor, Oracle, etc. It’s all the same. Why are there no good service companies that cater to existing companies and users?

I pondered the same question. We obviously carved out a good niche providing excellent service to existing customers. It’s not like we’re smarter than other people doing ERP implementations. It’s not like we have more technical abilities.

With all the talk about our how our industry business model is built on having a long customer relationships, why is it that it’s so hard to find good solution centers that provides good service? That makes the customer want to stay with the company for many, many years?

It’s All About the Money
Yeah, you knew I was going here.

When you’re in the ERP or accounting software industry, the majority of the money from a customer is spent in new sales. Consider the following graph:


Assuming the customer is purchased the product in 1/1/13 and they went live on 4/1/13. In the first 3 months, the customer will have spent the majority of the total value of the entire implementation. The cost that you would’ve paid to the solution center will include the software license and the initital implementation.

The reason why you see a few bumps in the post implementation support is because after the customer is live with Navision, they will add on modules, users, etc. In addition, every year the customer (if they choose to) pay for the 16% enhancement. Sometimes the customer will even do an upgrade which will require some labor.

You can extend the graph out longer but it’s pretty much the same.

It will take many years, if ever (if the customer is not on the enhancement plan), for a service based company to match the same revenue obtained from a new sale in the first 3 months.

If you were to own a solution center, where do you want to be? Just looking at the graph, it’s a no brainer. To maximize revenue for a company, you absolutely have to focus on new sales and new implementation. Microsoft rewards you if you focus on the new sales. The owners of the company rewards you if you can bring in new sales.

High Risk and High Reward
Make no mistake about it, to focus on new sales is a tough business. You have to hire and manage a sales force. You have to have enough talented manpower to handle all of that new implementation. On top of that, you have to feed these talents even if you don’t make a sale.

So here’s the cycle, the cost of talent is not cheap. In order to feed this talent, you have to focus on new sales and drive in revenue. Basically, you have to feed the machine. This means that if the customer does not spend enough with a solution center that focus on new sales, you will not get the love that you’re expecting.

Because the machine is so costly to feed, if you don’t make a new sale, it does not matter how talented your staff are. You’re going out of business.

You have to focus your best talents on revenue generating endeavors, basically new implementations. For the low money generators, such as support, you hand those off to junior people or cheap resources. Because post implementation support, to most companies, is a money losing operation. Solution centers of a certain size cannot live off of support services.

Refusing to Play the Game
Now to the 2nd question that client asked us “why didn’t we buy the software from you?”.

The answer to this is simple. When you’re buying a new software, you’re mostly buying from perception or the preceived quality of the company that’s proposing the software to you. When you walk into their office, they have a very beautiful office with very beautiful people. When they do their demos and presentations, it’s all done professionally with case studies and well thought out demos of the software. They even have a secretary to bring you whatever you want to drink.

If you were to come to the place where I work, I’ll be pretty embarassed to show you around. It’s a glorified office that’s 200 sq ft. with dirty walls and old furniture. But I can service you some instance coffee or water.

Now if you were a prospective customer and you walk into their office and you come to our office, which one will give you a better perception of credibility?

How I’ve been able to live and thrive in this business is because I refuse to play the game as I explained here.

While I was explaining this to the owner of the company, he kept saying “but this is not right, this is not right!”. Yes, everyone in the world will agree that this is not right. But this is the reality of our industry.

In the end, my response to the owner of the company was this:

1. It’s hard to find a good service company because focusing on service for existing companies is a money losing business
2. The reason why you didn’t buy from us is because on initial look, we’re pretty damn fugly.