One of the good things about having the NAV Design Patterns project open to the community, is that we get to hear about the experiences and design considerations of the NAV partners, which brings to light practices from other places than MDCC. Find below a pattern described by Henrik Langbak and Kim Ginnerup, from Bording Data in Denmark.
The NAV Design Patterns team
Meet the Pattern
Date controlled data is expensive to find in the database. This pattern describes how using a view with a sub-select and a linked table object will minimize the returned dataset.
A side effect is reduced and simplified code, increased performance and a more scalable solution that is almost independent of the amount of records in the table.
Know the Pattern
There is no way in Microsoft Dynamics NAV to get a set of records from the database, which all have the newest starting date, that is less than or equal to today’s date. Having an ending date on the record will help, but it introduces some other problems. In Microsoft Dynamics NAV, this is normally done by reading too many records, either at the SQL Server level or in the middle tier and throw away the ones you do not need. That is a waste of resources:
- SQL Server reads too many records
- Too much data sent over the network.
(If SQL Server and Microsoft Dynamics NAV Server are on different machines.)
- The Microsoft Dynamics NAV service tier receives and throw away data.
Ending Date Problem
Ending Date may introduce some problems of its own.
If your design requires you, to have one and only one active record per key in a dataset, then Ending Date introduces the possibility for overlapping or holes in the timeline.
Ending Date creates a dependency between two records. Changing a Starting Date, requires you to update the previous record. Changing the Ending Date requires you to update the next record.
If you add a record in between you will have to update both the before and the after record.
The pattern we describe here will work whether there is an Ending Date or Not.
The pattern is also relevant for other types than date. The pattern is usable whenever you have dependencies between rows in a table.
Use the pattern whenever you read a set of data containing a Starting Date and you need to implement a loop to throw away unwanted records. An example could be Codeunit 7000 “Sales Price Calc. Mgt.”. In this codeunit there are many loop constructs to find prices and discounts.
Use the Pattern
In the following example, we have a fictive table containing: Code, Starting Date and Price. The Primary Key consist of Code, Starting Date. The Database is the Demo Database, and the Company is Cronus.
1. Create the view
You will need to create the view before you define the Table Object.
You will need to create a view for every company in the database.
CREATE VIEW [dbo].[CRONUS$PriceView]
SELECT [Code], [Starting Date], [Price]
FROM dbo.[CRONUS$Price] AS A
WHERE [Starting Date] =
(SELECT MAX([Starting Date])
FROM dbo.[CRONUS$Price] AS B
WHERE B.[Code] = A.[Code] AND
B.[Starting Date] <= GETDATE())
Test the view to ensure that you get the correct result. It is much easier to test now than later.