If you work at the intersection of systems and accounting, as I do, the title of this post may put you on edge—because you know how the story goes. Your system is working fine. You may even have time to get to some of the “nice to haves” as the “must haves” are going pretty well. But then the auditors show up.… Read More
In May, I’m presenting at the NAVUG Focus conference on the topic of “BFFs with SSRS.” (Not a title I chose. Please.) In preparation, I’ve been doing more work on the database, and I thought it worth writing up some of my findings. In this post, I’m going to review three fields that are in the NAV table but aren’t (or aren’t obviously) in SQL Server: Option fields Flow filter fields (FlowFilter) Sum index fields (SumIndexFields). If you’re new to NAV, you should start with my Understanding the NAV Database post from last summer. Option Fields In my previous post, I wrote about how to find the table name from a NAV page. Now, let’s look at another example, using the Vendors page and underlying table. Here, I’ve filtered to show three vendors and the value for “Blocked” (which I’ve set): If we look at the table information, we can see that the underlying table is...
Tracking changes is a good idea in any ERP application, including Dynamics NAV. And Dynamics NAV’s “Change Audit” feature is a good place to start because it can comprehensively track changes to your tables. That said, it has some limitations in reporting that are important to know. In this post, I’m going to review some of the limits and also point out a few other key things to remember when tracking changes. Setting Up Change Log Setting up the change log is easy. First, we navigate to Change Log Setup. Click the check box “Change Log Activated”: (This doesn’t actually do anything. It just enables the next step.) From this page, we choose the Actions tab and click on Tables: We come to a list of our tables. I’ve filtered the list so we can work with the vendor table. (Vendor table is the table that probably everyone should track changes to: Each table has three options: Log Insertion—when we...
One weakness of NAV Account Schedules is its inability to specify a range of accounts (or a totaling account) and then give details on the fly. Unfortunately, when my client went to their first Jet consultant with this issue (thinking that Jet Reports could do better), they got the same answer. If you want to see detail, the consultant said, you have to enter EVERY account you want to see. (Which is something of a maintenance nightmare.) Further, it just isn’t true. In today’s post, I’ll show you how to do it. I’m going to divide the process into three parts: How to enter a range of accounts and see the detail How to add a total to the range How to create one report that can show either detail or summary, depending on your parameters. These posts assume you have some basic familiarity with Jet Reports and the special functions it provides...
I’m in the process of finishing up a data conversion from Lawson to Dynamics NAV 2016. The last piece to figure out are 1099 balances from checks issued before we converted. (We only converted open AP Items, so we don’t have the history necessary to calculate 1099s at year end.) I’ve looked around the web and found a variety of incorrect and partial answers. Many solutions have a lot of discussion about invoices and credits. But I think I’ve found a much simpler way of doing this. While converting 1099 balances is a fairly rare occurrence, you may find it useful for other scenarios as well. (For example, in many systems, I use “bank accounts” for clearing things that aren’t real payments.) But before we start, there are three things you need to remember about NAV and 1099s: 1. If you have paid invoices in the system for the year, it’s easy to update...
Dimensions are a wonderful feature of NAV. However, when doing General Ledger analysis, sometimes clients just want to dump everything into Excel and “play” with it—especially if they use Analysis Views. And to do this, clients want to see all dimensions, at the detail level, for each transaction. But here’s the challenge: You can only view or filter two dimensions directly on the General Ledger entries page. Fortunately, there’s a way around this restriction. How to See All Dimensions for NAV G/L Entries Let’s start by reviewing the problem: We want to see all transactions for a specified dimension, but when we look at General Ledger Entries, we can only see and filter by the two shortcut dimensions. We start with the basic General Ledger Entries page in Cronus, USA.: Out of the box, no dimensions are shown at all. If we choose Columns, we see the two global dimensions, in this case Department Code and Project Code: If...
Since most of my clients have switched to Dynamics NAV (many of them from Lawson), I’ve been busy writing a bunch of SSRS reports that facilitate specific finance and accounting functions my clients require. Here are the reports I’ve completed thus far: Using Totaling Accounts with SQL in Dynamics NAV AR Revaluation Report – Dynamics NAV RapidStart – A Query to Check Your Errors. I have more reports in the works. If you’d like my complete package of free SSRS reports for Dynamics NAV when it’s ready, let me know. ...
I was at the NAVUG user group in NYC last week. There, I learned that NAV2017 now allows users to cancel posted sales invoices and sales credit memos. While I’m sure some users will welcome these changes (and I’ve yet to see exactly how they’ll work), my first impression is that this isn’t such a great idea. Part of my reasoning is that, honestly, I believe that people who “pay for their mistakes” are more likely to stop making them. But I also hesitate because I don’t like reversing transactions in NAV more generally. Using Reverse Transaction in Dynamics NAV NAV has at least three “reverse transaction” options: General Ledger Entries, Customer Ledger Entries, and Vendor Ledger Entries. When a customer bounces a check, you could usually reverse that in the customer ledger. To better understand my hesitation about reversing transactions, let’s look at a payment reversal as an example. Let’s start with payment 2596 (below) in...
Since 1995, I’ve worked extensively with Lawson software. Until this month, that is, as my last Lawson client is moving into Dynamics NAV. I’ve learned a lot about NAV over the last few years. And while I do miss Lawson at times, Lawson’s time has passed for my core clients (who aren’t in health care or government). Why My Clients Have Moved From Lawson Before I explain, note that my observations about Lawson are largely based on my for-profit clients in the greater NY area. Generally, these clients fall into two large categories: Companies that grew into the multi-billion dollar category As these clients grew, they saw no real added functionality in Lawson and have therefore moved to SAP and Oracle mostly. This move was often driven by the desire to consolidate on a single ERP—and they didn’t feel Lawson had the functionality they wanted or a strong interest in meeting their needs. Mid-market...
In my last series of posts, I wrote about NAV currency setup, entering foreign currency sales invoices and how adjust currency amounts can be run at period end to create gain loss entries. In that last post, I promised to show you a sample report that would allow you to see the gain loss before you actually post it. And here it is. Aged Accounts Receivable in NAV The Aged Accounts Receivable report in NAV has the option to print results in the customer’s currency: If we run this report for Beef House (49525252), and select Print Detail, we see the following: We can see the transaction in euros as well as a report total in USD. Which is all good. However, it’s important to note: The USD total reflects the current rates as of the cut off date for the report whether or not we’ve actually posted gains and losses to the ledger. How do I know...