Brief overview and comparison of how summary values are stored and calculated in Dynamics GP, Dynamics NAV and Dynamics 365 Business Central

Introduction

There was an interesting conversation on twitter over the weekend with regards summary values and Dynamics 365 Business Central. Much like myself the person asking the question came from a Dynamics GP background and was wondering where in the database Dynamics 365 Business Central stored summary values i.e. GL period balances.

Now this is one area of the system that I really like about Dynamics NAV and Dynamics 365 Business Central so I thought I’d write this up.

The Dynamics GP way

Dynamics GP stores summary values in a variety of different summary tables. For example the GL open years transactional data is stored in one table (GL20000) and its summarised in a summary balance table (GL10110).

This means you can quickly access the summary values in an enquiry window. For example below is a GL enquiry window showing the GL period balances for a cash account.

What’s great is as this window is accessing a summary table rather than summing and sub totalling the main transaction table the performance is great, so you have access to summary data very quickly. i.e. you could 10 million rows of transactional data for this GL code but its all nicely summarised into a handful of rows in the summary table.

As I mentioned this provides fantastic performance however anyone who has worked with Dynamics GP for any length of time will realise the one issue that comes with storing data in this way. Unfortunately if there is an issue when posting a transaction “sometimes” the summary values aren’t updated and therefore they become “out of sync” with the actual transaction data i.e. if you were to manually sum the transaction data it wouldn’t match the summary data. This can cause problems and prompt awkward conversations with clients as they (understandably) ask “why” this is the case, and (even worse) how they can prevent this in the future. (I hate it when this question crops up)

*** A wider discussion here would be why Dynamics GP doesn’t update all the SQL tables (transaction table and summary table) using one SQL transaction which would negate this issue however that is beyond of the scope of this article.

To compensate for this Dynamics GP has “reconcile” features for nearly all ledgers so you can re-align the summary balances with the actual transactions. For example below is the “reconcile” utility for the General Ledger. Running this will recalculate all the summary balances based on the transactional data and update the summary table I mentioned earlier.

The Dynamics NAV \ Dynamics 365 Business Central way

The first thing to say is that Dynamics NAV and Dynamics 365 Business Central doesn’t store summary values in specific SQL tables like Dynamics GP. Instead it uses something called “Flow fields” for the summary totals which are implemented using SQL Indexed views. (therefore technically the summary values are materialised in the database just not in actual SQL tables). In the Dynamics NAV and Dynamics 365 Business Central world they are referred to as SIFT Indexes.

So for example, when you view the GL balances in Dynamics NAV as per the screen shot below the system is running queries using the SQL indexed views to present the summary values and therefore performance is still extremely good. (technically its the SQL optimiser that will choose to use this index when asked to return the summary values for this page because its much more efficient)

Digging a little deeper if you go into the Dynamics NAV development environment you can see how the SQL indexed views (SIFT Indexes) are implemented.

For example if I select the GL entry table and click “Design”

And then select “View > Keys” to look at the indexes

You can now see the keys (Indexes) on the left and some of the keys have an associated “SumIndexField” (i.e. a SIFT Index which is implemented via a SQL Indexed View). In the highlighted example an SQL indexed view is created summing the fields Amount, Debit Amount, Credit Amount, Additional-Currency Amount, Add.-Currency Debit Amount, Add.-Currency Credit Amount and grouping by GL Account No and Posting Date.

Therefore if I open SQL Management Studio and browse to my Dynamics NAV database I can see all the SQL indexed views on the GL Entry table (there are four which matches the number of enabled keys with SumIndexFields columns in my development environment)

Finally if I take a look at the definition of the SQL indexed view named “CRONUS UK Ltd_$G_L Entry$VSIFT$1” we can see this is implementing the key that I highlighted earlier

CREATE VIEW [dbo].[CRONUS UK Ltd_$G_L Entry$VSIFT$1]
WITH schemabinding
AS
SELECT “17”.”g_l account no_”,
“17”.”posting date”,
Count_big(*) AS “$Cnt”,
Sum(“17″.”amount”) AS “SUM$Amount”,
Sum(“17″.”debit amount”) AS “SUM$Debit Amount”,
Sum(“17″.”credit amount”) AS “SUM$Credit Amount”,
Sum(“17″.”additional-currency amount”) AS
“SUM$Additional-Currency Amount”,
Sum(“17″.”add_-currency debit amount”) AS
“SUM$Add_-Currency Debit Amount”,
Sum(“17″.”add_-currency credit amount”) AS
“SUM$Add_-Currency Credit Amount”
FROM dbo.”cronus uk ltd_$g_l entry” “17”
GROUP BY “17”.”g_l account no_”,
“17”.”posting date”

I suppose you could argue the one draw back on this is that whenever a transaction is posted in Dynamics NAV and Dynamics 365 Business Central then SQL has extra CPU and disk work to do as it maintains the SQL indexed views associated with the transaction tables.

Conclusion

Although I love so many things about Dynamics GP I do think Dynamics NAV and Dynamics 365 Business Central has the edge here. By allowing the database to handle the maintenance of summary values through the implementation of SQL indexed views the data can never go out of sync with the summary values.

Thanks for reading.

Dynamics NAV \ Business Central – Message “This combination of document number and posting date has been used more than once”

I had this message pop up when using the “Navigate” function on a cash receipt in Business Central when i wanted to view the related GL entries.

In this instance the message does a really good job of explaining the issue. The same document number and posting date have been used on more than one transaction, however coming from a Dynamics GP background I was curious as to how this had happened.

In Dynamics GP you can generally use the same document number for different document types however you can’t use the same document number for the same document type.

I suspect its possible in Dynamics NAV \ Business Central because the index keys on the tables in the background don’t prevent it however I was curious to recreate it myself.

Therefore to recreate you simply add multiple cash receipts (it could be other types of transactions but I’m using cash receipts as an example) to a batch and give them all the same Document Number. As you can see below I’ve given a cash receipt the same document number for different customers.

After posting them go to “Customer Ledger Entries” and highlight one of the entries and click “Actions > Navigate” as per below

You are then presented with the message “This combination of document number and posting date has been used more than once.”

Where its gets confusing is you can now see double the number of entries

Also, if I drill down on the GL entries I see the entries for both of the documents

I must stress this isn’t wrong, its just different than what I’m used to. I’d also personally try and prevent this by recommending users always use unique document numbers per transaction.

Thanks for reading.

Dynamics NAV\Business Central – Review of Location settings – Require Receive \ Require Put-away \ Put-away worksheet

Background

Recently I’ve been testing the various setting on the location and how this affects the processes and steps involved in the receiving and putting away of inventory.

The settings I’ve been paying particular attention to are “Require Receive“, “Require Put-away” and “Put-away Worksheet“.

The different options and combinations you have selected affects how you process the receiving and putting away of the inventory, and which documents are used.

I’ll detail each of these below and each one assumes you have the “Bin code required” option selected as well.

Important note:- while learning and researching this subject I found this excellent post by Olof Simren which goes into great detail on this subject. http://www.olofsimren.com/processing-of-receipts/. This is a truly awesome post that I would highly recommend reading. The post is really well written and easy to understand, and at first it put me off writing this blog, as it covers things so well that I didn’t want it to appear like I was just repeating this information. However I decided to go ahead as the primary purpose of my blog is to assist in my learning and understanding of Dynamics NAV and Business Central, and writing things up really helps me. Of course if others find it useful as well then that is always a massive bonus 🙂

Location Setup

Firstly, the options I’m referring to can be selected on the “Location” page as per below:

Require Receive

If you just have the “Require Receive” option switched on then you are stating that a Warehouse Receipt document should be posted to receive the inventory.

You can create a Warehouse Receipt document in either a push or pull fashion. i.e. you can go to “Warehouse Receipt” and recall the Purchase Order into the Warehouse Receipt or alternatively you can create the Warehouse Receipt from the Purchase Order using the option below:

After posting the Warehouse Receipt the goods are recorded in the Inventory and both item ledger and warehouse entries are created.

Therefore when just using the “Require Receive” on its own the process of recording inventory is as follows:

  1. Create and release Purchase Order.
  2. Create and post Warehouse Receipt.

An advantage of using this method is the ability to pull, and therefore receipt, more than one Purchase Order at once.

One limitation of using this method is you can’t easily record the inventory in more than one bin for the same line while receiving. (i.e. if you wanted to put the inventory into more than one bin for the same line item you’d have to be creative and re-open the Purchase Order and split the line and then use the Warehouse Receipt to receive)

** Please note during my testing I’ve found that although the option “Require Receive” is ticked ON you can still post the receipt from the Purchase Order by clicking “Post > Receive”

Require Put-away

If you just have the “Require Put-away” option switched on you are stating that you must first put-away the goods via a Inventory put-away document. You can create an Inventory Put-away in either a push or pull fashion. i.e. you can go to Inventory Put-away and recall the Purchase Order onto the Inventory Put-away or alternatively you can create the Inventory Put-away from the Purchase Order using the option below:

After posting the Inventory Put-away the goods are posted into inventory and both item ledger and warehouse entries are created.

Therefore when just using the “Require Put-away” on its own the process of recording inventory is as follows:

  1. Create and release Purchase Order
  2. Create and post Inventory Put-away

One benefit of using the Inventory Put-away is you can “split line” to record the inventory in more than one bin.

One limitation with Inventory Put-aways is you can only process one purchase order at a time. As you see below the Purchase Order number is stored on the header of the Inventory Put-away thus restricting the lines to one Purchase Order:

** Please note that as with the “Require Receive” option I’ve found that although the option “Require Put-away” is ticked ON you can still post the receipt from the Purchase Order by clicking “Post > Receive” thus bypassing this option. However this is mentioned as expected behaviour in this Microsoft document https://docs.microsoft.com/en-gb/dynamics365/business-central/warehouse-pick-items i.e. the snippet below is taken from that document

Require Receive and Require Put-away

When you have both the “Require Receive” and “Require Put-away” options ticked in combination you must now complete a two step process before the inventory is available. The first step is to create and post a Warehouse Receipt, which records the inventory in a receiving bin, and posts Item Ledger and Warehouse Entries. On posting the Warehouse Receipt a Warehouse Put-away is automatically created which enables you to register the movement of the inventory from the receiving bin to another bin, which then makes the inventory available. (this part posts additional Warehouse Entries)

Therefore when just using the “Require Receive” and “Require Put-away” together the process of recording inventory is as follows:

  1. Create and release the Purchase Order.
  2. Create and post a Warehouse Receipt. (automatically creates Warehouse Put-away)
  3. Register the inventory using the Warehouse Put-away

An important thing to note here is we are now using the “Warehouse Put-away” rather than the “Inventory Put-away” which means we can process more than one Purchase Order at once. (i.e. if the warehouse receipt we posted had more than one Purchase Order a “Warehouse Put-away” would be created for all Purchase Orders)

** Please note as with the previous configuration I found i could still post the receiving of the inventory from the Purchase Order as long as the bin code was populated on the purchase order line.

Require Receive \ Require Put-away \ Use Put-away Worksheet

With all three options ticked in combination you first create and post the “Warehouse Receipt” however posting the “Warehouse Receipt” doesn’t automatically create the “Warehouse Put-away“. You use the “Put away worksheet” to recall the “Warehouse Receipt” and then create a “Warehouse Put-away“.

Therefore when using the “Require Receive“, “Require Put-away” and “Use Put-away Worksheet” in combination the process of recording inventory is as follows:

  1. Create and release the Purchase Order.
  2. Create and post the Warehouse Receipt.
  3. Use Put-away Worksheet to create a Warehouse Put-away.
  4. Register the inventory using the Warehouse Put-away.

** Please note I also found even with all three of the options switched on I could still post the receiving of inventory from the Purchase Order as long as the bin code was populated on the purchase order line.

Conclusion

Interestingly in every scenario I could bypass the processes by simply posting the receipt directly from the Purchase Order. I found the only way the system forces you to follow the warehouse processes is when “Directed Put-away and pick” is switched ON on the location. I’d be interested to know if i’m missing something here or if this is expected behaviour as i do find this a little surprising.

Thanks for reading.