917.848.7284 Data for Finance and Accounting

Financial Reporting and Accounting Blog

Understanding the NAV Database, Part 2—Fields in NAV That Aren’t in SQL Server

Adam Jacobson May 9,2017

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.

Option Fields

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

Image 1.png

If we look at the table information, we can see that the underlying table is indeed Table 23 Vendor:

Image 2.png

And there is a field called Blocked:

Image 3.png

We can now query the database to look at the value for Blocked:

Image 4.png

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:

  1. The NAV Development Environment
  2. RapidStart
  3. 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:

Image 5.png

We click on “Design” at the bottom.

Then, we scroll through Field No. until we find the Blocked field:

Image 6.png

We then choose “Properties” or click shift-F4:

Image 7.png

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:

Image 8.png

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:

Image 9.png

I choose “Export to Excel” and get the following spreadsheet:

Image 10.png

We see that the Blocked column has the string and not the table.

However, if you hover over the column name, the mapping appears:

Image 11.png

 

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

Image 12.png

If we look at the field properties, we see FieldClass, called FlowField, and CalcFormula, which kind of looks like SQL:

Image 13.png

Let’s look at CalcFormula in detail by clicking on the box to the right of the line:

Image 14.png

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:

Image 15.png

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:

Image 16.png

From the menu, we select View -->Keys:

Image 17.png

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.)

Image 18.png

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:

Image 19.png

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

Image 20.png

If we look at the code, we can see how the aggregate is created:

Image 21.png

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.

7 Keys to Cost Effective Business Intelligence 

Tags: Dynamics NAV