Accessing Dynamics Business Central (NAV) Cloud Data, Part 1: Terminology and Tools

When databases became a common part of ERP systems over 20 years ago, developers would say that no matter what else happened, they would always have SQL. “Give me a connection string and I’ll get you anything you want,” was their moto.

Today, that’s not the case. With the advent of SaaS in general (and cloud-based Business Central in particular), you now need to know things like “ODATA and “APIs” — especially if you want to combine data from multiple systems in a single report or data warehouse.

I joke that the definition of SaaS is “software where companies save lots of money on infrastructure and upgrades, which they end up spending on data and reporting.” (Which is good for me, I suppose. I never liked infrastructure and always liked data.)

Read more…

NAV and Duplicated Data

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.

Read more…

SQL Server and Dynamics NAV: MAXDOP and Cost Threshold for Parallelism

In this article, I’m going to delve into the topics of MAXDOP and cost threshold for parallelism. 

I’m writing it for two reasons:

1. There was a recent thread on SQL Server settings on the NAVUG list. Some of the information was good. But much of it was expressed in terms of “Well, I heard…,” which makes me nervous. You should have a basic understanding of what various settings do before you start taking anyone’s advice. (This isn’t to make me look superior. I’ve had plenty of times in my life where I (or my team) have been caught in the “let’s give this setting a try” loop — and it’s not pretty.)

2. I’m preparing a presentation on SQL performance for NAV. While I’ll be focusing mostly on improving BI query performance (which is where I spend a lot of my time), I do get asked about server settings, so I think it’s a worthwhile topic.

Read more…

Jet Reports – What NAV Server Am I Using?

A client recently asked me: “When I run a report, how can I know which server/company I’m using?”

It’s a good question — and one that others may have.

Below, I’ll walk you through the answer.

And because pretty colors impress most folks, I’ve even made it match the colors that show up in NAV.

Read more…

SOX Audits and Dynamics NAV, Part 1: “The Auditors are Here and They Have a Few Questions”

If you work at the intersection of systems and accounting, as I do, the title of this post may put you on edge—because you know how the story goes. Your system is working fine. You may even have time to get to some of the “nice to haves” as the “must haves” are going pretty well. But then the auditors show up. And you end up spending a LOT of time with them—without much to show for it.

Read more…

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.

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

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:

  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:

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.

 

Understanding the Dynamics NAV Change Log

Tracking changes is a good idea in any ERP application, including Dynamics NAV. And Dynamics NAV’s “Change Audit” feature is a good place to start because it can comprehensively track changes to your tables.

That said, it has some limitations in reporting that are important to know.

In this post, I’m going to review some of the limits and also point out a few other key things to remember when tracking changes.

Setting Up Change Log

Setting up the change log is easy.

First, we navigate to Change Log Setup. Click the check box “Change Log Activated”:

(This doesn’t actually do anything. It just enables the next step.)

From this page, we choose the Actions tab and click on Tables:

We come to a list of our tables. I’ve filtered the list so we can work with the vendor table.

(Vendor table is the table that probably everyone should track changes to:

Each table has three options:

  1. Log Insertion—when we add a record (see caveat below)
  2. Log Modification—when we change a record
  3. Log Deletion—when we delete a record.

On each of the three fields, we have three options:

  1. Leave blank—to do nothing
  2. Some Fields—to track some fields
  3. All Fields—to track all fields.

If we select “Some Fields,” we can then specify the fields we want to track:

Remember: We must close the client for the change log to take effect!

Tips on Tracking Fields

1. Tracking “All Fields” for insertion doesn’t do what you think it does.

If you’ve used Dynamics NAV for any amount of time, you know that NAV adds a record into the database as soon as you’ve keyed the absolute minimum amount of information. And what’s the result? Not much shows up in the change log as an insertion insertions. For example, with vendors I find the only field I care about that comes through on an “insertion” is vendor number.

Even the name, when entered for the first time, comes through as a modification. Both the insertion and the modification will have similar datetime stamps, so you can put the pieces together.

2. Limit the number of fields you track.

As you’ll see in the next step, having too many fields creates a lot of data. In addition, the out-of-the-box report and screen isn’t fantastic. Indeed, the screen can become painfully slow.

Looking Up Changes

Once we’ve started tracking changes, we want to see the information, naturally.

To do so, we go to Change Log Entries:

Here, I’ve filtered the screen on Vendor and added one new vendor.

As you can see, adding just one vendor created 10 insertion records and another five modification records (which is the data I actually care about).

It’s hard to show all the data in one screen. If we scroll right, we’ll see the old and new values for the fields. In this case only new values:

Another option is to print the data. But, please, make sure you’ve filtered to see only the records you want. Otherwise, you’ll kill a ton of trees.

To print, merely choose print from the Actions tab:

Unfortunately, it’s not the most usable report ever created:

However, if you have some in house technical help, I can show you how to create a better report in SSRS. Stay tuned for details my next post.

Archiving Purchase and Sales Quotes/Orders in Dynamics NAV

One more thing: The change log isn’t the only way to save old versions of things. You can save archived versions of Purchase Quotes, Purchase Orders, Sales Quotes and Sales Orders.

To set up archiving, go Purchases & Payables Setup. Simply click Archive Quotes and Orders:

The same option is available in Sales & Receivables Setup:

Viewing Archived Purchase and Sales Quotes/Orders

To view archived purchase or sales quotes and orders, go to the relevant archives. They are:

  1. Purchase Quote Archive
  2. Purchase Order Archive
  3. Sales Quote Archive
  4. Sales Order Archive.

Have questions about the Dynamics NAV SSRS change log or purchase/sales archives? Feel free to post below.

 

Creating a Summary/Detail Report for Jet Reports and Dynamics NAV

One weakness of NAV Account Schedules is its inability to specify a range of accounts (or a totaling account) and then give details on the fly.

Unfortunately, when my client went to their first Jet consultant with this issue (thinking that Jet Reports could do better), they got the same answer. If you want to see detail, the consultant said, you have to enter EVERY account you want to see. (Which is something of a maintenance nightmare.) Further, it just isn’t true. In today’s post, I’ll show you how to do it.

I’m going to divide the process into three parts:

  1. How to enter a range of accounts and see the detail
  2. How to add a total to the range
  3. How to create one report that can show either detail or summary, depending on your parameters.

These posts assume you have some basic familiarity with Jet Reports and the special functions it provides for Excel. You might also want to have some familiarity with GL, NL, NF and hide functions in Jet.

For my examples, I’ll use the Cronus, USA company..

Part 1—Showing Detail for a Range of Accounts

Let’s start with a simple example in Jet, using the GL function to return the amounts we’d like.

As explained in an earlier post, I’m using accounting periods for my report and not dates.

In this example, I’ve entered the same accounts in three ways:

  1. As individual posting accounts
  2. As a range of accounts
  3. As a totaling account.

I want to point out two key things:

First, the amount columns (G,H,L and M) all use the GL function to return the information we want. We can see the formula, and here’s an example of it:

=GL(“Balance”,$B7,CYPSD,CYED)*$E7

Second, we can’t just rely on the GL formula. We need to remember that, unlike many financial report writers, Jet also creates regular database reports.

Entering a Range of Accounts

So, let’s check out the new report design. In it, we use two basic functions to retrieve non-financial data: NL and NF.

We start by listing all our revenue accounts 41000..41999. Then we place that range in column B.

Note that column B is set to hide when the report runs:

In column C, we use our NL function. As you know, NL function can do many things. In this case, we ask it to create a row in our Excel worksheet for every record returned from the specified table.

Note that the formula appears somewhat cryptic in the spreadsheet. But if we look at the formula itself, we can understand what’s going on:

=NL(“Rows”,”G/L Account”,{“No.”,”Name”},”Account Type”,”Posting”,”No.”,$B$8)

But the easiest way to see the formula is by clicking NL in the ribbon:

Here, we see that we’re asking for rows to be returned from the table “G/L Account” where the chart of accounts is stored. We ask for Fields “No.” and “Name” to be brought in. And we’re filtering by “Posting” accounts. Most importantly, we reference the range of accounts that we setup in our spreadsheet, which Jet helpfully show us to be 41000..49999.

So, the NL function looks good. But we need to do one more thing. We need to put numbers and names into our report. And we’ve done that with the NF function, which we see in columns D and E.

In column D, the formula refers back to the cell with the NF function and asks for “No.,” (i.e. the account number):

=NF($C8,”No.”)

Similarly, column E asks for the name:

=NF($C8,”Name”)

We can click on NF to see the formula structure clearly:

“Key” is where we’re getting the data and “Field” tells us what we want to see in this cell.

We then update our GL formulas to reference this cell:

=GL(“Balance”,$D8,CYPSD,CYED)*$F8

Now, let’s run the report and see what happens:

And we have a list of all the accounts we want with the appropriate numbers.

Yay!

Part 2—Adding Totals

When Jet ran, it inserted one row per account.

Before we started we had totaling formulas under our data:

For example, take a look at the current MTD column. Ideally, we’d like to see this automatically add up all our inserted rows. (In practice, I use SUBTOTAL instead of SUM in most of my reports, but I don’t want to confuse things here.)

Let’s see what happens:

Unfortunately, the formula didn’t change. It’s still only includes row 8.. How can we work around this?

Let’s try inserting a row to “anchor” our formula:

The formula now spans H8:H9.

Will this work? Let’s try it:

And it does!

We can, if we want, place a hide command in that row so we won’t see it when the report runs:

And when we rerun it, it still works:

The hidden row doesn’t appear, but the formula still works.

You can repeat the above process for as many ranges as you’d like.

Part 3—Creating a Summary Detail Report

So, we’ve seen how we can get financial detail for a range of accounts. Now, we want to go a step further and create a summary or detail version of our report.

We start by having a cell where we can enter “S” (for summary) or “D” (for detail).

So, we create a named cell on our parameter page where the user can select “Summary” or “Detail.”

Now, when we return to our report page, we need to make two changes:

First, we add a formula for each row so that if we want summary, the row will be hidden. We do this by displaying the word “Hide.” (This works because at the top of the column we’ve entered “Hide.”)

=IF(SummaryDetail=”Summary”,”Hide”,””)

Second, on the actual summary row, we note that we only want to see a name if we’re summarizing:

=IF(SummaryDetail =”Summary”,”Revenue”,””)

So, if we run this as detail, we see what we received before:

And if we choose summary:

Perfect!

Feel free to comment below if you have any questions.

 

Converting 1099 Balances in Dynamics NAV

I’m in the process of finishing up a data conversion from Lawson to Dynamics NAV 2016. The last piece to figure out are 1099 balances from checks issued before we converted. (We only converted open AP Items, so we don’t have the history necessary to calculate 1099s at year end.)

I’ve looked around the web and found a variety of incorrect and partial answers. Many solutions have a lot of discussion about invoices and credits. But I think I’ve found a much simpler way of doing this.


While converting 1099 balances is a fairly rare occurrence, you may find it useful for other scenarios as well. (For example, in many systems, I use “bank accounts” for clearing things that aren’t real payments.)

But before we start, there are three things you need to remember about NAV and 1099s:

1. If you have paid invoices in the system for the year, it’s easy to update 1099 amounts.

Just go to the vendor ledger, find the appropriate invoice, and adjust the IRS-1099 amount and/or the IRSS 1099 Code. Note that this is done at the invoice or credit memo level, not at the payment level. The fields may need to be added through choose columns:

2. 1099 amounts are specified on the invoice/credit memo and/or purchase journal line level. 

You cannot issue a payment to a vendor without a prior document and have the balance affect 1099 output.

3. You must pay a document for it to affect the 1099 balance.

If you just convert the invoice and don’t pay it, you won’t have any 1099 effect.

The Basic Point

To understand this process, you need to remember your T-Accounts. Just like when we convert open items, we want the invoice to represent our credit/debit balance for the same account, thereby having no general ledger effect. We’re going to create an invoice and matching payment that will have no G/L effect, yet still update 1099 balances.

With that out of the way, let’s get started.

Step 1 – Create a One-Time Setup

First, we need to choose an account that will be our “clearing account.” If we only have one payables account, we can use that. Or we can create a special one-time clearing account for this purpose.

To make this easy, let’s create a one-time clearing account:

Now, let’s create a dummy bank account to “create our checks.” (We don’t want to create checks in our regular bank accounts because that will mess up our reconciliation.)

To do this, we go to bank account posting groups:

We set up a new one that points to the new account 22300-1 previously set up.

By the way, I like using accounts as part of my codes on many posting groups. It makes it easier to keep things straight if I only use the groups to specify accounts.

Now that we have our posting group, we can set up the bank account. Let’s name it with a “Z” so it will always fall to the bottom of our list (never to be used again).

We only care about No., Name, Last Check No., and Bank Acc. Posting Group. We’re using the Bank Acc. Posting Group Set Up in our previous setup.

One last point: I’m assuming that all your vendors are setup with proper 1099 information. If you’re not sure, confirm and complete if necessary before continuing.

With that, setup is done.

Step 2 – Create the Purchase Journal/Purchase Invoice

Next, we create the invoice that represents the amount we want to have on our 1099s. Here, I used RapidStart, which allows me to enter 1099 codes and amounts for purchase journals. For the purpose of this example, I’m going to enter the 1099 . (Note we can’t do this from the Purchase Journal page.)

We can enter a 1099 code on Purchase Invoice, so let’s do that:

Now, we only care about a few fields beyond the normal invoice stuff:

  1. Invoice Number. We put 1099 in front of the invoice number. This makes it easier to find in our check run.
  2. Due Date. We want to choose a due date that’s before our conversion date. This way we can pay only the dummy 1099 invoices.
  3. G/L account for the line. This should be the clearing account we set up in the above step.
  4. IRS 1099 Code. The point of this exercise.

Once we’re done, we can post our records.

If we look at the register for this posting, we’ll see that the system debited our “clearing” account and credited accounts payable:

Step 3 – Cut the Checks

It’s important we set the due date correctly so when we select payments in our payment journal, we find only the records we care about.

When we reach “suggest vendor payments,” we use our dummy bank account for the check run:

Now here’s the payment we want to make:

And we can see it’s going against our dummy bank account.

Once we print the checks and post them, we can look at the entries for our check:

We see that our accounts net to zero.

Now, we can print our 1099s and close our invoice with no effect on our ledger.

Any questions? If so, feel free to comment below.

 

Get tips and insights delivered to your inbox

Start a conversation with us

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

Request a Consult