NAV Object, Field, and Option Numbers in the SQL Server Database

adminredthree

Like ( 1 )

Report

When writing SQL queries for NAV, you can run into a problem.

While column names are generally pretty self-explanatory, sometimes the data within those columns requires decoding.

Those are: 1) Table Numbers, 2) Fields Numbers and 3) Option Numbers.

I’ve already covered this in my posts (and presentation) on Understanding the NAV Database.

Here I’m going to repeat myself — and then give you the code you need to get these numbers into SQL.

(All this code can be found in my GitHub repository.)

NAV Table Number to Table Name – The Easy Example

There are multiple tables I use regularly where I need to convert Table Numbers into Table Names. They can be grouped as follows:

Change Log Related
Change Log Entry
Change Log Setup (Field)
Change Log Setup (Table)
Default Dimension
Permission.

In the Change Log Related and Default Dimension tables, the column is nicely named Table No, as shown in this simple query of the Change Log Entry table for CRONUS USA:

We can easily get the Table Name by linking to the Object Table. (Note that we have to specify Object Type as 1for the table and then we can bring Table Name into our query.)

Note that while the Permission Table sets permissions for multiple types of NAV objects, the object type is already available to us.

A quick side note (which I only realized just now): In my first query, I used Object Type 1.

But in the Permissions query (where I didn’t specify object type), it shows up as Object Type 0. What’s going on?

It turns out that the NAV Object Table has TWO entries for every table object, one is Table Data (0) and one is the Table (1).

They always have the same name:

I’m a data guy and not a NAV Developer, so I can’t explain exactly what NAV uses Object Type 0 for.

Regardless, Object Type 1 is what I’ll use to find Metadata in the next step.

Extracting NAV Field Number Information from SQL Server

So, table numbers are an easy problem to solve. But what about field numbers?

In my first posts on understanding the NAV database, I mentioned there’s a standard Jet report that will give you all the table numbers and matching fields numbers.

So, if you’d like, you could take that spreadsheet and manipulate it into a format that you can then upload into SQL Server. (Check out this post on uploading Excel to SQL server).

However, I know the data is already in the SQL table. And part of me enjoys a hacker challenge. So, I figured out how to extract Field Numbers directly from SQL — and this approach has the added benefit of delivering Option field information as well.

This is how I did it:

Step 1 – Finding the data I need

The first thing I have to do is create a SQL query to extract the column with the metadata:

Step 2 – Decompressing that Column

Now, unless you’re Commander Data (I said I was a geek), that Metadata field doesn’t look particularly helpful.

If you look at the table definition in SQL, you’ll see it’s described as an image datatype:

When I googled this, I found out that it was a deprecated kind of VARBINARY.

For more on this, this question on Stack Exchange is super helpful: https://stackoverflow.com/questions/21223762/read-dynamics-nav-table-metadata-with-sql

And so is this blog post: https://devch.wordpress.com/2014/01/21/accessing-compressed-blobs-from-outside-nav-nav2013-revisited/

Essentially (as a person who never deals with this kind of data), I would describe this is a compressed form of all the metadata you can see in the developers’ environment.

If you take that field and remove the first four bytes, you’ll actually get XML that looks like this (for the example of the G/L Account Table).

Looking below, I can find Field ID 1 which is named No.

If I look further down, I can even find an Option field for Account Type.

If I scroll across (I deleted some of the lines for easier viewing, below), we can see the OptionString.

To correlate these to numbers, we just count (starting at 0).

Thus, Posting is 0, Heading is 1, Total is 2, Begin-Total is 3, etc.

Step 3 – How can I get that XML into SQL Server?

Unfortunately, as far as I can tell, you can’t decompress that field and get the XML using T-SQL.

Another side note: I didn’t say you couldn’t do it in SQL Server. Given that you can run Python or .net in SQL Server using CLR (i.e. common language runtime), it’s theoretically possible to build all the code in SQL Server. However, you have to setup the ability to run a CLR when you install SQL Server. And that’s not something I’ve attempted.

Therefore, I used Python instead.

Eventually, I want to get this into PowerShell because I’m guessing that more folks reading this blog have touched PowerShell than Python. But I haven’t had time to get the decompress code to work yet.

So here’s how get that XML into SQL Server using Python.

1. Create Tables in Your Reporting Database

The first thing to do is create a table in SQL Server to hold the Field and Option information I need.

We don’t need a new table for Table Names as we already have the Object table from standard NAV.

And as I’ve written previously, I do all my work in a reporting database.

(For more on this, see my GitHub.)

2. Extract and Decompress the Data to XML

Next, I created a Python program to read the data and both print it to the screen and create a text file.

The program is below, and I’ve added comments (in green) to delineate each step.

Note that is has two parts. First, I run a query over the database. Second, I take the output and “decompress it.”

I display the data to both the screen and to a file.

If I run the program in Visual Studio, I can see the XML:

It’s not really readable. But the program did create the nice XML file that I produced earlier.

3. Get the Data I Want from XML

So, I have the XML Data, but XML isn’t the end goal. I want to populate my SQL Table.

At the end, all of this will happen in one program. But I’m going slowly here for illustrative purposes.

Here’s another simple bit of Python code:

If I run the above, I can see that Python can find all my fields.

And if I follow the instructions and uncomment the other lines, I get a more organized listing.

The fully loaded program is a little more complex. It uses Pandas DataFrame to hold the data output and SQLAlchemy to load the data directly to SQL Server.

Here it is below, but I will also put the full code into GitHub for your reference: