I recently posted a seven-part series on SQL for super users. (You’ll find the introduction here.)
In this series, I chose to use generic Northwind database (with some enhancements) for my examples. (The posts came out of presentation I made to a mixed group of Dynamics users.)
But, as you know, each kind of Dynamics software (i.e. GP, NAV, AX, CRM) has its idiosyncrasies. Therefore, I’m in the process of adapting the generic Northwind examples to specific Dynamics types. And I’m starting with NAV, as I happen to spend quite a lot of time on it. (If you’re interested in getting a copy of the PDF when it’s ready, let me know.)
As I prep the NAV series, I realize a basic intro to NAV would be useful before launching into it.
Therefore, in this post I’m going to discuss:
- Finding the right table—the “About This Page”
- The suboptimal use of special characters
- Foreign keys and the NAV036 data dictionary report
- How many tables you have to join in NAV reporting?
1. Finding the Right Table—The “About This Page”
For every page you visit in NAV, you can use the “About This Page” feature to find the major table the page updates.
For example, say we’re in Vendors:
To see what table Vendors updates, we click the top right arrow, select help and then “About This Page”:
And this window appears:
What we care about is Source Table, which in this case is Vendor.
Let’s see what we get when we follow the same steps with the Vendor Ledgers Entries:
We get the source table of Vendor Ledger Entry.
Of course, we now have the NAV table name. But that’s not quite the same thing as the name in SQL Server. Why? Because NAV creates a different table for every company you create.
So, for example, if we had two companies:
- Cronus USA, Inc.
- Cronus Mexico, Inc.
Then we would find two database tables representing vendors:
- Cronus USA, Inc_$Vendor
- Cronus Mexico Inc_$Vendor
As you can see below:
About This Page—Finding Column Data
About This Page is also useful when you want to know not just the table name but the columns themselves.
Let’s use Customer Ledger Entries as an example.
If we open the Table Fields tab, we find a list of columns. It starts with the primary key (PK) for the table and continues with all remaining columns in alphabetical order:
What’s great is that you can see what data is populating each column – so you can have some confidence when you go to build your reports that you’re choosing the correct column.
Limitations of About This Page
About This Page works fine when you have only one table per page. But it doesn’t work so well when you have multiple tables per page, as is the case with Sales Orders.
Sales Orders has both header and detail tables. But About This Page only displays the header:
Most header detail combinations are fairly easy to figure out. Here, for example, Sales Header matches to Sales Line and Purchase Header matches to Purchase Line. But it’s not always so obvious. (I’m planning to write a series on basic queries to get you started.)
2. The Suboptimal Use of Special Characters
Unfortunately, NAV allows the use of spaces and other special characters in its table and column names. From my SQL perspective, this is annoying because it forces users to place square brackets around all names.
For example, let’s start with a table name that works:
Here, SQL interprets the space between CRONUS and USA as the end of the table name. Therefore, it looks for a table called CRONUS and comes back with “invalid object name.”
By the way, do yourself a favor and keep your company names simple. In this case, “CRUS” would be much nicer than “Cronus USA, INC.” (On a side, note using special characters can also mess up Web Services. But that’s another topic).
Another catch: In this example the name of the table is the same in Help and in the database. But that’s not always the case because you can use periods in NAV’s internal names, but you can’t use periods in table names in SQL server. So SQL replaces the period with an underscore.
Let’s look at the Customer Ledger Entries to illustrate:
In SQL Server, the name is:
[dbo].[CRONUS USA, Inc_$Cust_ Ledger Entry]
Here, “Cust.” has become “Cust_”.
The same is true for column names. So it’s “[Entry No_]” not “Entry No.”
And remember: You need to put brackets around your column names as well. They almost always include spaces or other special characters.
3. Foreign Keys and the Jet Sample Report NAV036 Data Dictionary
Unlike in the sample Northwind Database, NAV doesn’t maintain foreign key integrity at the database level. So, you don’t have any of those nice silver key emojis to help you figure out relations.
The good news is that, in general, foreign keys in NAV are fairly straightforward.
For example, if you were reporting on Sales Order and wanted to know where the customer data was found, you could simply go to the Customer page using “Advanced”:
And once you’re on the Customer page, you can use About This Page again.
About This Page tells you that the sell-to customer on the sales order came from the Customer table:
Jet Sample Report NAV036 – Data Dictionary
But there’s another option. You can use Jet Sample Report NAV036 – Data Dictionary to figure out how to join tables.
Let’s go back to our Cust. Ledger Entry table and look at NAV036:
First, we see that TableNo and TableName are the same as in the About This Page option.
Second, every column is assigned a Field No.
Mostly, what we care about is the RelationTableNo and RelationFieldNo (i.e. columns seven and eight).
In this case, we can see that Customer No. is related to Table 18 on FieldNo. But the related field number is actually 1 (i.e. When a RelationTableNo exists and the RelationFieldNo is zero, then the related field number is actually 1).
If we now look up Table 18, we see that the related Table is Customer, as we expected.
We can draw two key points about this information:
- Beware the No. (or No_) column
Based upon the data dictionary, you’ll be relating the “Customer No.” column in “Cust. Ledger Entry” table to the “Customer No.” column in the “Customer” table. But there is no column called Customer No. in the Customer table!
This is different from almost every other ERP database I’ve used. In Dynamics NAV, the “No.” (or “No_”) column name is used for different purposes in different tables.
Yes, this gets tricky. Which is why I’ll cover it in more detail in the NAV PDF.
- Not all relations are shown
Let’s take a look at Sales Line (used for sales order and unposted sales invoices):
We can see that Sell-to Customer No. is validated against table 18 – Customer.
But “Document No.” isn’t shown as related to anything—even though we know it must be in table 36 – Sales Header. (Shown as “No.” Not “Document No.” Of course.)
Further, the “No.” column doesn’t have any Relation table. This makes some sense as No. can be validated against multiple tables—including Item (table 27) and G/L Account (table 15) depending on Type.
Again, I’ll cover this wrinkle in more detail in my upcoming PDF on SQL for NAV super users. I’ll also include some sample queries (and hopefully diagrams) to show you the practice, and not just the theory, of NAV data.
4. How Many Tables Do You Have to Join in NAV Reporting?
Before we start, let me say that my goal here isn’t to discuss relational database theory. My goal is to make sure you get what you want (and expect) when writing reports.
The challenge when writing reports in NAV is that it copies a lot of data between tables. While this can make reporting easier, it can also make it much harder.
Let’s look at an example.
If we look at Sales Order, we see not only the sell-to customer, but also the city and state:
If we take a look at About This Page, we see the data stored in the Sales Order table:
So the Sales Order table includes data for Sell-to City and Sell-to State. Which means that if we’re building a query, we don’t need the Customer table. Isn’t that great?
Well, yes and no. Watch what happens when I update the customer address because the company has moved from Atlanta, GA to New York, NY:
The address change isn’t reflected in the Sales Order table:
This could be good or bad. In this case, maybe you want to keep the old address—so then your reports on sales by territory or state don’t change.
But what if it was the company name that had changed, instead of the company address? Then you’d have two names in your sales report, when you only want one.