917.848.7284 Data for Finance and Accounting

Financial Reporting and Accounting Blog

Understanding the NAV Database, Part 2—Fields in NAV That Aren’t in SQL Server

Adam Jacobson May 9,2017

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).
Read More

Tags: Dynamics NAV

Understanding the Dynamics NAV Change Log

Adam Jacobson March 7,2017

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.

Read More

Tags: Dynamics NAV

Creating a Summary/Detail Report for Jet Reports and Dynamics NAV

Adam Jacobson February 1,2017

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.

Read More

Tags: Dynamics NAV

Converting 1099 Balances in Dynamics NAV

Adam Jacobson January 19,2017

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.

Read More

Tags: Dynamics NAV

See All Dimensions for NAV General Ledger Entries

Adam Jacobson January 17,2017

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.

Read More

Tags: Dynamics NAV

Free SSRS Reports for Dynamics NAV

Adam Jacobson December 29,2016

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:

Read More

Tags: Dynamics NAV

How to Deal With NSF Checks in NAV (Without Using Reverse Transaction)

Adam Jacobson December 22,2016

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.

Read More

Tags: Dynamics NAV

Lawson and Dynamics NAV

Adam Jacobson December 20,2016

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).

Read More

Tags: Dynamics NAV

AR Revaluation Report – Dynamics NAV

Adam Jacobson December 8,2016

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.

Read More

Tags: Dynamics NAV

Using Totaling Accounts with SQL in Dynamics NAV

Adam Jacobson December 6,2016

If you’re like many of my clients, you didn’t clean up your chart of accounts when you installed your new ERP. Or maybe you did but then your business changed. So, instead of having a nice set of account ranges, like those in the sample Cronus companies, you need to create odd groupings of accounts.

Totaling Accounts are your first option in NAV. And they work well. However, if you want to use them to write reports using SSRS or another SQL tool, they aren’t that easy to work with. But I’ve built some code to make it a bit easier.

Read More

Tags: Dynamics NAV