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.
In my previous post, I wrote about how to find the table name from a NAV page. Now, let’s look at another example, using the Vendors page and underlying table.
Here, I’ve filtered to show three vendors and the value for “Blocked” (which I’ve set):
If we look at the table information, we can see that the underlying table is indeed Table 23 Vendor:
And there is a field called Blocked:
We can now query the database to look at the value for Blocked:
We see that the field Blocked is there. But rather than alpha values, we see only numeric values. We can’t see blanks, “All,” or “Payment” as we saw on the screen.
So how can we figure out those values?
There are three different methods we can use:
- The NAV Development Environment
- The data stored in the database.
1. Using the NAV Development Environment to Find Blocked Values
You may or may not have access to the NAV Development Environment. (Often, folks writing reports over SQL databases aren’t the same folks who have access to NAV Development.)
But if you do have access to NAV Development, you can figure out the mapping between name and number fairly easily.
From Object Designer, we find the table:
We click on “Design” at the bottom.
Then, we scroll through Field No. until we find the Blocked field:
We then choose “Properties” or click shift-F4:
Then, we find “OptionString.”
In this case, we see: ,Payment,All
This expression works as an array, where each value in our array is separated by a comma. We start with zero (which occurs before the first comma) and then count forward.
So, here the blocked values map to the string as follows:
0 = No value (or blanks)
1 = Payment
2 = All
For another example, let’s look at Table 36 Sales Header:
In this case, “Document Type” has the following options:
Quote,Order,Invoice,Credit Memo,Blanket Order,Return Order
Which we can map to numbers as follows:
0 = Quote
1 = Order
2 = Invoice
3 = Credit memo
4 = Blanket order
5 = Return order
2. Using RapidStart to Find Blocked Values
If you don’t have access to the NAV Development Environment, but do have access to RapidStart, you can find blocked values by exporting a sample set of data.
Here, I’ve created a RapidStart package to export the Vendor table:
I choose “Export to Excel” and get the following spreadsheet:
We see that the Blocked column has the string and not the table.
However, if you hover over the column name, the mapping appears:
3. Using Data Stored in the Database to Find Blocked Values
Field level information is actually stored in the Objects table in the database. However, it’s not in a format that’s easy to use with just T-SQL. Therefore, I’m working on a separate post that will show how to pull that data.
Flow Fields and Flow Filters
Flow Fields aren’t in the SQL table at all. Rather, they’re essentially queries of other tables based on:
- Fields that are actually in the table.
- Flow filters (i.e. fields described in the NAV table but are only entered by users when performing filter operations).
To illustrate, let’s take the field “Balance”:
If we look at the field properties, we see FieldClass, called FlowField, and CalcFormula, which kind of looks like SQL:
Let’s look at CalcFormula in detail by clicking on the box to the right of the line:
Now we can see what the system is doing.
Method: The method is Sum. Other options are Average, Exist, Count, Min, Max and Lookup. (Lookup returns one value. All the rest should be obvious to the SQL folks reading this post).
Reverse Sign: Reverse sign simply does what it says. The number appears as positive even though (because of certain NAV conventions) it’s stored as negative in the related table.
Field: The field we’re going to return.
Table Filter: Again, let’s take a closer look:
Table Filter shows how NAV selects records from the table. These fields exist as fields in the database. In this case, as Vendor No. (which is fairly obvious).
As noted above, Flow Filters are defined in the table, so users may use them for filtering. They are not stored in the SQL Database.
A side note: As with the options strings, flow field definitions are stored in the Objects table as metadata. In a future post, I’ll explain how to read these if you want to keep everything as SQL.
Sum Index Fields
SumIndexFields are not separate fields in either the NAV Table or SQLServer Table. Instead, they’re fields that are aggregated based on a particular key. You don’t have to use SumIndexFields, but they may speed up your query.
Many ERP systems keep both transaction tables and balance tables. So, for example, in the general ledger, you could have a GL transaction table as well as a table that maintains balances per period for given accounts.
In NAV, we only have the transaction table—in this case, G/L Entry. So, if you’re writing in SQL, you might think you have to add up all the records in that table to get a balance, which is inefficient. And that’s where SumIndexFields becomes useful.
SumIndexFields relies on indexed views in SQL server. Every time a G/L Entry record is created, matching aggregates are also updated.
Here’s an example. We open the G/L Entry Table:
From the menu, we select View –>Keys:
Then we come to the list of keys. (These are just ways the table is sorted. They aren’t really keys in the SQL sense.)
Next to each key, you have SumIndexFields. Let’s take the example of the G/L Account No. and Posting Date keys. If we click in SumIndexFields, we see the fields that are summarized for each combination of the key:
We can actually see the view in the SQL database. The view number equals the row number on the key screen – 1.
In our case, we want: dbo.CRONUS USA, Inc_$G_L Entry$VSIFT$1
If we look at the code, we can see how the aggregate is created:
It’s a simple grouping on the key, plus all the fields we asked for are summarized.
One thing to note: As you can see, the indexed view only has the key fields we care about. To get other information, you’ll have to join back to G/L Accounts—and this may affect the overall efficiency of your query.
SSRS CTA will go here!