Just like any piece of software, there are some bugs in account schedules, and the best way to deal with them is to know they exist. I’ll let the Microsoft folks debate whether these are a bug or not. To me, as an end-user, anything that removes a necessary feature or keeps a necessary feature from operating reliably without replacing it with something better is a bug.
It is important to note that the majority of these (except the last one) are NO LONGER AN ISSUE IN MICROSOFT DYNAMICS 365 BUSINESS CENTRAL, as they have been fixed in the newest versions of the software! So for anyone who likes their account schedules, but hasn’t yet leveled up to using Business Central, add this as just one more reason to get updated to the latest. If you are still struggling with these issues in the software version you have, you don’t need to struggle any more.
Refresh Error in NAV Classic Account Schedules
This bug causes the account schedule to come up blank when changing to a different account schedule. It can sometimes be partially remedied by scrolling up and down or left and right, but this sometimes only brings back some of the expected numbers. This is a refresh error that has existed for many of the Classic NAV versions. I’m really not sure how long it’s been there – I started using NAV with version 3.5 and I’ve seen this bug in that version as well as in 4.0, 5.0, and 2009 Classic. The best way to get past this one is to exit out of your account schedule and open it back up again. With the introduction of the matrix to account schedules in 2009 RTC versions, this bug (finally) went away.
Copy and Paste in 2009 RTC
While the introduction of the matrix brought us some very nice things, it brought us one nasty disappointing bug – in NAV 2009 RTC versions, copy and paste cannot be used in account schedules. This has by far been the worst bug I’ve seen in NAV account schedules, and really is a huge loss of efficiency to users who have that version of NAV. I rarely get worked up about this kind of thing, but I did put a post out there explaining why this was such a big loss. There is no way to get past this bug other than to upgrade to the next version of NAV, NAV2013, where thankfully, copy and paste are returned to us.
Buttons that don’t work and filters that work but are super clunky in 2009 RTC
There were a few other bugs in 2009 RTC that made this version of NAV the worst version ever for account schedule users. On the account schedules overview page, there were some mysterious buttons labeled as Next Set and Previous Set that don’t do anything. In addition, the filtering is terrible. Date filters always pick up the work date, don’t retain the date you were just using if you leave the screen and go back, and if you want to change the dates, you’ve got to type the entire date range, every time. There are no quick navigational buttons to move from one period to another. In addition, dimension filtering is difficult to get to and difficult to populate with the names of the dimensions you are using, in particular, if you use multiple analysis views. Luckily, every single of these bugs was fixed with the release of NAV2013.
To date, NAV2013 is by far the best version of NAV Account Schedules. It seems like Microsoft really got it right with a large number of capabilities related to the improved matrix, filtering, and navigation with account schedules, making it streamlined, intuitive and easy. Alas, NAV2013 is not perfect, and there are two bugs I’ve found. Unlike other versions, hopefully we won’t have to wait for a next release to see these ones fixed.
Refresh error in NAV2013
This one is a brand new and different type of refresh error than the one we saw in NAV Classic versions. When switching from an account schedule with many columns (let’s say a 12 month trended view) to an account schedule with fewer columns (let’s say an actual versus budget with variance), you’ll end up with a bunch of left over data from the 12 month view in the columns that are no longer a part of the actual versus budget view. There’s a quick and easy fix, which is to hit F5 or the brand spanking new Refresh button, and those pesky numbers will disappear. However, you’ve got to be on the watch for them and know what you’re looking at – a risk that’s not so great. As a NAV2013 user you’ll get used to using the F5/Refresh button frequently, so not a big deal, but I still don’t like any financial report showing up with numbers it shouldn’t.
Update workbook function no longer retains formatting in Excel when exporting account schedules with NAV2013
This one is a bigger deal and again, is a pretty big loss of efficiency for NAV account schedule users. Anyone who uses account schedules a lot should be using the export to Excel function all the time in order to product account schedules quickly and with consistent formatting. NAV2013 users can no longer do this and retain existing formatting. The update to Excel function still works in this version, but instead of just updating numbers in a formatted Excel worksheet, it dumps unformatted updated numbers into that worksheet, overriding the formatting you might have there. The recommended best practice is to continue to use the update worksheet function, but change one aspect of design of your Excel workbook. What you now need to do when you design your Excel workbook the first time are the following steps:
Use create workbook for any schedule to get an unformatted starting copy (Copy 1) of your schedule in Excel.
Copy this worksheet to another worksheet (Copy 2) and format it exactly like you’d like to see it.
Delete all the numbers out of Copy 2 and put in a link to Copy 1 that will pull all the numbers into Copy 2.
Hide the worksheet tab for Copy 1 so you only see the nicely formatted Copy 2.
When you update your Excel workbook the next time, only update the numbers to Copy 1. You will never update numbers to Copy 2. The links in Copy 2 will always pull forward from Copy 1.
I can’t say that redesigning my 18 page financial statement package this way was a breeze, but I did it because I had to, and have now taught hundreds of accountants how to do the same. The one nice thing about this is I can get a little fancier with some of my formatting and won’t need to continue to tinker with some of the small things I do, like taking underlines out of column A and readjusting the width of column B, things I had to do universally in NAV2009 Classic because while the update workbook function keeps most of the formatting, it does override a couple of pesky things. So ultimately, this method is faster on a monthly basis, but will take more design time and Excel comfort level, up front.
This might be a better solution for account schedules produced with many separate tabs for filtered dimensions. Imagine an Excel workbook with 20 tabs showing the same report, one for each dimension. One way to make it more efficient will be to design a single report with the needed dimensions in each column. This report will go to the one hidden worksheet in the workbook and the 20 user facing tabs will be beautifully formatted reports that link only to the applicable column from a single report. So, for that example, this actually turns into a time saver since you don’t need to export/update each filtered dimension to a new tabbed worksheet.
One concern is that the linked formulas could become unlinked/deleted/moved, but this could be safeguarded by locking cells on the formatted worksheet to keep them from being accidentally changed. Another concern is that your account schedule could change and then the linked copy won’t show the error that is not being picked up. There are a few more things to watch for with this change in place, but there are some positives as well as negatives that go along with this bug.
Note: This particular “bug” is now a normal part of the software, all the way up to the current version of Business Central. While this one is here to stay, and we’ve found a way to adapt by adjusting how to produce account schedules by using the Export to Excel functions with a refreshable shell to update numbers but keep formatting, I still feel Microsoft could improve on this. This particular aspect of the software causes many users quite a bit of frustration as the solution is in no way intuitive and still causes more than necessary manual work to actual publish finished financial statements. It’s really too bad, because fixing this one area could increase user adoption in the accounting area exponentially.
And yes, one brand new bug in Business Central!
No software is perfect. There is always something that someone hasn’t thought about and Business Central has one that makes the Export to Excel function just a little bit more difficult. Again, I’m probably going to get in trouble by calling this a bug, but this fits my definition of a bug: anything that removes a necessary feature or keeps a necessary feature from operating reliably without replacing it with something better. Here’s the new thing to watch out for. When using the Export to Excel function, the Update Existing Document has now been replaced with Update Copy of Existing Document. This causes the new updated document to be saved with a system generated file name which causes the user to have to constantly rename the file when updating. The file name is sufficiently long that if the user uses a name that is anything other than Spartanly brief, it causes an error in saving the file because the name is too long.
My suggestion? Using a very short working title while you are producing your financials and once you are completely done, rename the file with final beautiful title you have planned. Voila!
This posting is part of the Dynamics 365 Business Central Account Schedules 101 series. Find the entire list of lessons here.
Don’t forget to visit the Account Schedule Examples pages if you’re looking for even more ideas on how to improve your financial reporting using account schedules with Dynamics 365 Business Central.