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.

Let’s take an example of customers and customer ledger entries.

When we set up a customer, we assign two pieces of information (among others) that get copied to whatever customer-related transactions we create: 1) Nameand 2) Customer Posting Group.

Here we see Name:

And here’s Customer Posting Group:

When we create transactions in NAV, this data gets duplicated in many places. 

For example, if we look at a posted invoice, we see Sell-to Customer Name:

While it’s not on the standard screen, if we look at About This Page table data, we see that Customer Posting Group is also stored on the table itself:

This data is also stored in the customer ledger entry, which drives most of our aging reports:

When querying the data, we have to ask ourselves: Do we want the value as it was when the transaction was created? Or do we want the value as it is in the customer table right now? 

We can argue either way.

If the Customer Posting Group relates to actual accounting data (account numbers), we probably want the Customer Posting Group that was used when the record was created. 

However, if we’re using Customer Posting Group as an easy way to group customers, we probably want the current Customer Posting Group.

And therein is the problem: NAV is not consistent.  

Let’s look at an example. Say we run Aged Accounts Receivable for “Domestic”: 

And I see the entry as expected (i.e. invoice #203023 for Progressive Home Furnishings):

But try this: Go into Customer and change the Customer Posting Group to “Foreign”: 

And then rerun the report for “Domestic.” 

The invoice disappears:

But if I run it for “Foreign,” it’s on the Aged Accounts Receivable:

In contrast, if I run Customer Register, I don’t see the record if I choose “Foreign,” but I do see it if I choose “Domestic”:

In general, I only look at out-of-the-box reports when I need to reconcile something (or prove to an auditor that something is working). So, most of the time, I don’t much care about inconsistencies between NAV reports. 

But I do care about making sure that the reports and queries I build for my customers give them what they want. 

So, you have to know what you’re trying to do. If you’re trying to recreate an original document (for example, you’d like to reprint an invoice months later) use the data on the transaction, not on the customer master.

But if you’d like to get a general report (like a sales summary) use the most current data.  

One more tip: If a customer changes names, most folks looking at the report won’t care about the old name and will be confused. Therefore, use the name from the customer table.