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.

An Example of Data Duplication

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:

NAV and Duplicated Data

And here’s Customer Posting Group:

NAV and Duplicated Data

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:

NAV and Duplicated Data

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:

NAV and Duplicated Data

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

NAV and Duplicated Data

The Challenge

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”: 

NAV and duplicated data

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

NAV and Duplicated Data

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

NAV and Duplicated Data

And then rerun the report for “Domestic.” 

The invoice disappears:

NAV and Duplicated Data

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

NAV and Duplicated Data

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”:

NAV and Duplicated Data

My Recommendation

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.

Adam Jacobson

Adam is founder and president of Red Three Consulting. He has over 20 years of experience in ERP consulting and BI consulting. Adam has particular expertise in complex accounting and other multi-company and international reporting challenges. Prior to founding Red Three, Adam was a partner in United Systems Consultants where he ran its 30-person Lawson software practice. Outside of work, he serves as board member and treasurer of the Riverdale Y. When not working, he spends his time answering his son’s political questions and cycling, swimming and reading.


Leave a comment

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult