NAV and Duplicated Data

I recently presented “NAV SQL from Friends to Foes” at the recent NAV User Group Summit.

Here’s a copy of the presentation:

Here’s the code I reference (in GitHuB).

My presentation is a whirlwind tour of the NAV database and SQL. But still, it’s a good starting point for folks who want to get comfortable with them both.

As I continue to build on this topic, I’m going to write about it here. 

And I’m going to start by explaining how NAV is (and isn’t) a relational database. 

And the first related topic is duplication of data — because this is a common source of confusion that all begins with NAV reports.

Read more…

SQL Server and Dynamics NAV: MAXDOP and Cost Threshold for Parallelism

In this article, I’m going to delve into the topics of MAXDOP and cost threshold for parallelism. 

I’m writing it for two reasons:

1. There was a recent thread on SQL Server settings on the NAVUG list. Some of the information was good. But much of it was expressed in terms of “Well, I heard…,” which makes me nervous. You should have a basic understanding of what various settings do before you start taking anyone’s advice. (This isn’t to make me look superior. I’ve had plenty of times in my life where I (or my team) have been caught in the “let’s give this setting a try” loop — and it’s not pretty.)

2. I’m preparing a presentation on SQL performance for NAV. While I’ll be focusing mostly on improving BI query performance (which is where I spend a lot of my time), I do get asked about server settings, so I think it’s a worthwhile topic.

Read more…

Jet Reports – What NAV Server Am I Using?

A client recently asked me: “When I run a report, how can I know which server/company I’m using?”

It’s a good question — and one that others may have.

Below, I’ll walk you through the answer.

And because pretty colors impress most folks, I’ve even made it match the colors that show up in NAV.

Read more…

SOX Audits and Dynamics NAV, Part 1: “The Auditors are Here and They Have a Few Questions”

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. And you end up spending a LOT of time with them—without much to show for it.

Read more…

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

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.

Read more…

Understanding the Dynamics NAV Change Log

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…

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

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…

Converting 1099 Balances in Dynamics NAV

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 1099 amounts.

Just go to the vendor ledger, find the appropriate invoice, and adjust the IRS-1099 amount and/or the IRSS 1099 Code. Note that this is done at the invoice or credit memo level, not at the payment level. The fields may need to be added through choose columns:

2. 1099 amounts are specified on the invoice/credit memo and/or purchase journal line level. 

You cannot issue a payment to a vendor without a prior document and have the balance affect 1099 output.

3. You must pay a document for it to affect the 1099 balance.

If you just convert the invoice and don’t pay it, you won’t have any 1099 effect.

The Basic Point

To understand this process, you need to remember your T-Accounts. Just like when we convert open items, we want the invoice to represent our credit/debit balance for the same account, thereby having no general ledger effect. We’re going to create an invoice and matching payment that will have no G/L effect, yet still update 1099 balances.

With that out of the way, let’s get started.

Step 1 – Create a One-Time Setup

First, we need to choose an account that will be our “clearing account.” If we only have one payables account, we can use that. Or we can create a special one-time clearing account for this purpose.

To make this easy, let’s create a one-time clearing account:

Now, let’s create a dummy bank account to “create our checks.” (We don’t want to create checks in our regular bank accounts because that will mess up our reconciliation.)

To do this, we go to bank account posting groups:

We set up a new one that points to the new account 22300-1 previously set up.

By the way, I like using accounts as part of my codes on many posting groups. It makes it easier to keep things straight if I only use the groups to specify accounts.

Now that we have our posting group, we can set up the bank account. Let’s name it with a “Z” so it will always fall to the bottom of our list (never to be used again).

We only care about No., Name, Last Check No., and Bank Acc. Posting Group. We’re using the Bank Acc. Posting Group Set Up in our previous setup.

One last point: I’m assuming that all your vendors are setup with proper 1099 information. If you’re not sure, confirm and complete if necessary before continuing.

With that, setup is done.

Step 2 – Create the Purchase Journal/Purchase Invoice

Next, we create the invoice that represents the amount we want to have on our 1099s. Here, I used RapidStart, which allows me to enter 1099 codes and amounts for purchase journals. For the purpose of this example, I’m going to enter the 1099 . (Note we can’t do this from the Purchase Journal page.)

We can enter a 1099 code on Purchase Invoice, so let’s do that:

Now, we only care about a few fields beyond the normal invoice stuff:

  1. Invoice Number. We put 1099 in front of the invoice number. This makes it easier to find in our check run.
  2. Due Date. We want to choose a due date that’s before our conversion date. This way we can pay only the dummy 1099 invoices.
  3. G/L account for the line. This should be the clearing account we set up in the above step.
  4. IRS 1099 Code. The point of this exercise.

Once we’re done, we can post our records.

If we look at the register for this posting, we’ll see that the system debited our “clearing” account and credited accounts payable:

Step 3 – Cut the Checks

It’s important we set the due date correctly so when we select payments in our payment journal, we find only the records we care about.

When we reach “suggest vendor payments,” we use our dummy bank account for the check run:

Now here’s the payment we want to make:

And we can see it’s going against our dummy bank account.

Once we print the checks and post them, we can look at the entries for our check:

We see that our accounts net to zero.

Now, we can print our 1099s and close our invoice with no effect on our ledger.

Any questions? If so, feel free to comment below.


See All Dimensions for NAV General Ledger Entries

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…

Free SSRS Reports for Dynamics NAV

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:

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.



Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284

Request a Consult