Rashed Amini

The ara3n weblog

Increasing concurrency in NAV

13th May 2015

NAV uses optimistic Concurrency for all the transaction except for posting. During posting, NAV locks the ledgers and sub ledgers, basically any table that has primary key of “Entry No.”
In Order for posting to work NAV has kept the posting process to as fast as possible. Most partners forget this import part during implementation and modification of the system.
Most of the big projects where the user count is greater than 50 and high transaction volume by the user, NAV system starts to show the weakness of its design. Users get locking error whenthey post a transaction and the system becomes unbearable. The client invests in time consuming sql tuning process that mitigates the problem to a bearable situation and it becomes long term struggle for the client.
MS has done some changes to improve concurrency in NAV. If you are on SQL and still using classic client please do an exe upgrade to the latest 2009 executables. MS has changed the changed the isolation level from SERIALIZABLE to REPEATABLEREAD. After you do the exe upgrade run the following sql command
UPDATE [$ndo$dbproperty] SET diagnostics = 4194304
This reduces the locks on table dramatically on sql server and increases concurrency in NAV.
Here is the MSDN Link.

This is just the start of the increasing concurrency in NAV.
The next step is redesign of some tables in NAV that are bottlenecks. The first table is the “No. series line” table. The “No. Series Line” is using throughout the entire application to grab the next Document Number, for example Order, Invoice No. So if there are two users that are creating documents, they will be locked by each other till they finish their transaction. This is not a major issue when users are creating one document, but for customers that create many documents at a time, the users will be locking each other constantly.
An example of this scenario, A company runs MRP at night splits the results into multiple batches. In the morning the buyers come and look at their batches and create Purchase Orders. I’ve worked with clients where they have more than 10 buyers that daily created purchase orders to replenish inventory.
One way to eliminate the locking on “No. series line” table to add a “User ID” to be part of the primary key. Codeunit “NoSeriesManagement” will need to be modified. In functionSetNoSeriesLineFilter
NoSeriesLine.RESET;
NoSeriesLine.SETCURRENTKEY("Series Code","Starting Date");
//Mod Start
NoSeriesLine.SETCURRENTKEY("User ID","Series Code","Starting Date");
NoSeriesLine.SETRANGE("User ID",USERID);
//Mod End

This is a fairly small modification that should be part of standard NAV. This adds additional setup for number series, but that can be automated. In future blog I’ll write about how to automate that as well. And also make it configurable, so that you can create on some number series and not others.

Here is a screenshot of how it will look once User ID is added to number series.
Number series

Posted in Dynamics NAV | No Comments »

Inventory to GL Reconcile SQL Report

11th March 2015

Here is the sql report for Inventory to GL Reconcile Report. For Large customers the report probably is running out of memory or taking too long to run.


DECLARE @AsOfDate DATE
DECLARE @ItemNo VARCHAR(20)

SET @AsOfDate = '2016-1-26'
SET @ItemNo = 'T10B116'

SELECT *
FROM (
SELECT cast(Detail.[Item No_] AS VARCHAR(20)) AS ItemNo
,cast(Detail.[Variant Code] AS VARCHAR(10)) AS VariantCode
,Detail.[Location Code]
,CAST(sum(InventoryValuation) AS FLOAT) AS InventoryValuation
,CAST(sum(ReceivedNotInvoiced) AS FLOAT) AS ReceivedNotInvoiced
,CAST(sum(ShippedNotInvoiced) AS FLOAT) AS ShippedNotInvoiced
,CAST(sum(TotalExpectedCost) AS FLOAT) AS TotalExpectedCost
,CAST(sum(ReceivedNotInvoicedPostedtoGL) AS FLOAT) AS ReceivedNotInvoicedPostedtoGL
,CAST(sum(ShippedNotInvoicedPostedtoGL) AS FLOAT) AS ShippedNotInvoicedPostedtoGL
,CAST(sum(TotalExpectedCostPostedtoGL) AS FLOAT) AS TotalExpectedCostPostedtoGL
,CAST(sum(InvoicedValue) AS FLOAT) AS InvoicedValue
,CAST(sum(CostPostedToGL) AS FLOAT) AS CostPostedToGL
,CAST(sum(InvValueToBePosted) AS FLOAT) AS InvValueToBePosted
FROM (
SELECT ile.[Item No_]
,ile.[Location Code]
,ile.[Variant Code]
,(
(
SELECT sum(ve.[Cost Amount (Actual)])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] <= @AsOfDate
) + (
SELECT sum(ve.[Cost Amount (Expected)])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] <= @AsOfDate
)
) AS InventoryValuation
,(
SELECT isnull((
SELECT sum(ve.[Cost Amount (Expected)])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] 0
), 0)
) AS ReceivedNotInvoiced
,(
SELECT isnull((
SELECT sum(ve.[Cost Amount (Expected)])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] <= @AsOfDate
AND ve.[Valued Quantity] < 0
), 0)
) AS ShippedNotInvoiced
,(
SELECT isnull((
SELECT sum(ve.[Cost Amount (Expected)])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] <= @AsOfDate
), 0)
) AS TotalExpectedCost
,(
SELECT isnull((
SELECT sum(ve.[Expected Cost Posted to G_L])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] 0
), 0)
) AS ReceivedNotInvoicedPostedtoGL
,(
SELECT isnull((
SELECT sum(ve.[Expected Cost Posted to G_L])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] <= @AsOfDate
AND ve.[Valued Quantity] < 0
), 0)
) AS ShippedNotInvoicedPostedtoGL
,(
SELECT isnull((
SELECT sum(ve.[Expected Cost Posted to G_L])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] <= @AsOfDate
), 0)
) AS TotalExpectedCostPostedtoGL
,(
SELECT sum(ve.[Cost Amount (Actual)])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] <= @AsOfDate
) AS InvoicedValue
,(
SELECT sum(ve.[Cost Posted to G_L])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] <= @AsOfDate
) AS CostPostedToGL
,(
(
SELECT sum(ve.[Cost Amount (Actual)])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] <= @AsOfDate
) - (
SELECT sum(ve.[Cost Posted to G_L])
FROM [CRONUS USA, Inc_$Value Entry] ve
WHERE ve.[Item Ledger Entry No_] = ile.[Entry No_]
AND ve.[Posting Date] <= @AsOfDate
)
) AS InvValueToBePosted
FROM [CRONUS USA, Inc_$Item Ledger Entry] ile WITH (NOLOCK)
WHERE ile.[Posting Date] <= @AsOfDate
--AND ile.[Item No_] = @ItemNo
) AS Detail
GROUP BY [Item No_]
,[Variant Code]
,[Location Code]
) AS Summary
WHERE (
(InventoryValuation 0)
OR (ReceivedNotInvoiced 0)
OR (ShippedNotInvoiced 0)
OR (TotalExpectedCost 0)
OR (ReceivedNotInvoicedPostedtoGL 0)
OR (ShippedNotInvoicedPostedtoGL 0)
OR (TotalExpectedCostPostedtoGL 0)
OR (InvoicedValue 0)
OR (CostPostedToGL 0)
OR (InvValueToBePosted 0)
)
ORDER BY ItemNo
,VariantCode
,[Location Code]

Posted in Dynamics NAV | No Comments »

AP Aging Report in SQL For Dynamics NAV

1st July 2014

I had posted the AR Aging report that customer can use to run their Aging reports that take a lot of time to run in NAV to run them on sql. Here is the AP Aging report on SQL.

Here is the file Link

Here is a screenshot of the running the query in SQL SMS

SQL SMS

Posted in Dynamics NAV | No Comments »

AR Aging Report in SQL For Dynamics NAV

1st July 2014

I had posted a while back the Inventory Valuation sql query that can be used to run the report on sql server. SQL reports run much faster than NAV report because sql is doing all the data crunching and aggregation. Below is the sql statement for AR Aging report.
As you’ll notice the query return 8 columns instead of 4 columns that standard NAV returns.
The columns Based on Aging Days parameter. You can add the parameters on sql report and publish the query.
Enjoy.

Here is the file Link

Here is a screenshot of the running the query in SQL SMS

SQL SMS

Posted in Dynamics NAV | No Comments »

Storing and editing Large Text in NAV 2013

28th February 2014

As you probably know NAV has a limit on the length of text field size. It is set at 250 characters. There are scenarios where the client needs to store more data that is longer than 250 Character. If you look at any website that sells items, you’ll see detailed description that is made up of many paragraphs. In older version of NAV, partners were using the following solutions. They stored the data in blob field and opened the data to be edited in external application, e.g. Notepad, or automation, e.g. WaldoPad. The problem with this issue is that you need to install the automation on each machine and support it.
The other solution was to store it in text field but split the data across multiple records. For example in the comment line table or Extended Text line table. The problem with extended text or comment line is that editing these data becomes harder. If you changing a line in the middle of the paragraph then the all the lines bellow need to be moved as well.
The third solution is a mix of the two solutions above where you allow doing the editing in external application or automation and splitting the data and storing it in the table.
In NAV 2013 NAV has still the text field size limit, but there is no limit and text data type. You can create a text variable and add as much data into it and display set the source expression of a text box to the text variable and the user can modify it like a paragraph. Then at the end you can store the data either into a blob field or split the string into a table. The advantages of this solution is that you are not using any external application or COM component and you are doing it all within NAV.
Below is a demo solution that you can use in your projects and replace existing solution if you are upgrading.
In this demo I’m going to use the first record in Item table and store the data. Either in Comment line or Picture field, which is a blob field. You can change it based on your requirements and use one or the other solution to store the data.

nav notepad

Here is the link to the object. It is 2013 R2 object but can be implemented in 2013 as well.

Posted in Dynamics NAV | No Comments »

JobQueue in NAV 2013

26th February 2014

I had posted a while back a NAS solution to use two NAS instances to run jobs for unlimited companies. This solution worked great and saved money on buying NAS licenses for each company. In NAV 2013 NAS now runs on service tier. The old solution will not work. I had also written a second solution that used NAS and web service. It used MS xmldom COM automation object that could be used in NAV 2013 R2 but need to change the data type to dotNET data type. Automations are no longer supported on service tier. In NAV 2013 you no longer need these solutions. It comes by default with NAS. When you enable NAS in NAV Service Tier in 2013, it loops through all the companies and looks at Job Queue Entry table. You need to create a record in Job Queue record in each company and make sure the “Start automatically from NAS” is checked.

Job Queue Image

NAS will look at each company for Job queues and start a background session. These background sessions are run separately in each company and process any jobs that are setup in that company. So there is no need to install multiple NAS Service tier. The NAS Service is just used to start these background sessions and monitor them and make sure they are running, if one of them stops the NAS Service will start another background sessions. So if you have hundreds of companies, you will have in each company a background session running based on job queue record created in that company. These background sessions will process jobs concurrently, which is much better scalable solution. And the best part is that you no longer need licenses for these background sessions. So make sure you are running only one NAS windows service instance for JOB queue. If you need multiple sessions, create multiple Job Queue records with different category filters. People might also get confused with the table names. Job Queue = Background Session. Job Queue Entry = Codeunit/report that will perform a certain task such as run Adjust cost. The startup codeunit for nas is 450

Job Queue Image

Posted in Dynamics NAV 2013 | No Comments »

Automatic Lock Objects in NAV 2013 and 2013 R2

20th November 2013

Several years ago I had written a blog on checkout tool that written in SQL CLR. The solution was based on a sql table trigger on the object table and would write the data into a Checkout table. If another user tried to modify the same object, the user would get an error that somebody else had modified the object. In NAV 2009 Microsoft introduced the checkout functionality. They added a new field in the object table called Locked and Locked by and In Tools->Option Microsoft added the property “Auto-Lock on Design”. This option isn’t usefully since a lot of times a developer just designs objects for research purposes and doesn’t want to lock the object. A lot of NAV development involves finding where the code is and how it works. The original tool I had written still works in NAV 2013. The issue is that in order to check in an object you have to run the table and delete the record. As you know data manipulation is done from RTC only in NAV 2013. I’ve built a new solution that uses the new fields: Locked, Locked By, in the Object table. The solution does not use any new objects. It’ s just a SQL trigger on Object Tracking table. When an object is modified in NAV, the Development Client write/updates the Object Tracking table. The sql trigger then updates the object table Locked and Locked By fields.
Here is the script.


CREATE TRIGGER [AutoLock] ON [dbo].[Object Tracking]
AFTER INSERT, UPDATE
AS
DECLARE @Type int
DECLARE @ObjectID int
DECLARE @ChangeType int
DECLARE @LockedBY VarChar(132)

Set @Type = (select [Object Type] from inserted )
Set @ObjectID = (select [Object ID]from inserted )
Set @ChangeType = (select [Change Type] from inserted )

Set @LockedBY = (select [Locked By] from [Object] where Type=@Type and [ID] = @ObjectID)

if (@Type > 0) and (@ChangeType =0) and (@LockedBy = '') begin
UPDATE [Object]
set [Locked By] = SYSTEM_USER,
[Locked] = 1
where Type=@Type and [ID] = @ObjectID
end

This trigger only fires when the developer modifies/insert an object. This way you can design an object without worrying that it would lock the object. To release the Lock, the developer simply select one or more objects and selects Function->releases locks. If you are deleting objects, make sure you Lock them first. This solution can work with 2009 R2 as well. Enjoy.

Posted in Dynamics NAV, Dynamics NAV 2013 | Comments Off

Using Enumerators in C/AL to iterate through files in a folder

26th March 2013

Enumerators in .NET allow you to iterate through arrays and collections in your class. You can see this used for example in FOREACH Loop. Here is an example.

DirectoryInfo di = new DirectoryInfo("c:\\temp");
FileInfo[] files = di.GetFiles("*.pdf”);
foreach (FileInfo fileInfo in files)
{
Console.WriteLine(FileInfo.Name)
}

There is no way to translate FOREACH loop in C/AL, but there is a workaround using List class.
In the above example, DirectoryInfo returns an array of FileInfo . In FOREACH loop we are looping through each file and printing it to console screen.

So the first line in NAV is initializing DirectoryInfo with constructor

DirectoryInfo := DirectoryInfo.DirectoryInfo('C:\temp\');

The second line is where we are changing and assigning it to a List. The CAL Compiler should error out but it is not. It looks like it’s not doing any type checking. At runtime it is determining the object type returned. DirectoryInfo.GetFiles().ToList() ;


List := DirectoryInfo.GetFiles('*.txt');
enumerator := List.GetEnumerator();

Once we have a list of enumerator class then we can loop through each object

WHILE enumerator.MoveNext DO BEGIN
FileInfo:= enumerator.Current();
MESSAGE('%1',FileInfo.Name);

END;

Here is the whole Code with Variable type

DirectoryInfo DotNet System.IO.DirectoryInfo.’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
FileInfo DotNet System.IO.FileInfo.’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
List DotNet System.Collections.Generic.List`1.’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
enumerator DotNet System.Collections.IEnumerator.’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′

DirectoryInfo := DirectoryInfo.DirectoryInfo('C:\temp\');

List := DirectoryInfo.GetFiles('*.txt');

enumerator := List.GetEnumerator();

WHILE enumerator.MoveNext DO BEGIN
FileInfo:= enumerator.Current();
MESSAGE('%1',FileInfo.Name);

END;

Posted in DotNet, Dynamics NAV 2013 | Comments Off

NAV 2013 Chart Add-in

5th March 2013

NAV 2013 comes with a new Chart add-in. In NAV 2009 we had basic charts that are xml files that could be displayed on a page. In this blog I’ll show the steps required to build a basic Chart add-in.
1. Create a new Page of type CardPart. The source table needs to be Business Chart Buffer table.
2. Click finish. You should see the Page in Design mode with just a ContentArea line.
3. Add a new field line below ContentArea. Enter MyChart in name field. The SourceExp should be blank. Go to property of the line and select the ControlAddIn property to Microsoft.Dynamics.Nav.Client.BusinessChart. You Page should look like the picture below.
Chart1

4. For this Chart I will show sales orders by location as example.
5. Then go to code and add the following OnFindRecord trigger.
Chart2

And that’s it. If you run the Page directly from Object Designer you will get an error.
“The control add-in on control MyChart on page MyChart Example has not been instantiated.”
You will need to add it as a part to view it or run it from another page as an action menu.
Page.Runmodal(Page::” MyChart Example”);
The Page looks like this in Cronus Company with demo data.
Chart3

The above example is a simple chart but you can build any kind of other charts. You have to consider performance when writing your chart. If you are querying any historical tables with large data, I suggest creating a table to store the calculated data and on open page to check if there is any changes and only update the changes. You could also schedule the updates on NAS. There are additional settings that you can set. For further research take a look at “Trailing Sales Orders Chart” Page.
Building charts based on this add-in is quite powerful , simple, and can be developed rapidly.
For example just changing the code a little


AddMeasure('Sales' ,0,"Data Type"::Decimal,"Chart Type"::Pie);
Location.SETRANGE("Use As In-Transit",FALSE);
IF Location.FINDSET THEN REPEAT

AddColumn(Location.Code);
SalesHeader.SETRANGE("Location Code",Location.Code);
SetValue('Sales',I,SalesHeader.COUNT);
I += 1;

UNTIL Location.NEXT = 0;
Update(CurrPage.MyChart);

Gives you a nice Pie Chart.
Chart4

Attached is the object link.

Posted in Dynamics NAV | Comments Off

How to remove Namespaces in XMLport in NAV 2013

7th December 2012

MS in Dynamics NAV 5.0 created XMLPort datatype. It was a great tool to load xml files but it had trouble dealing with namespaces. MS released a workaround code for this that allowed removing all the namespaces of the xml file. The Code looked like this.

OldRemoveNameSpaces

In Dynamics NAV 2013 32 bit com objects are no longer supported on service tier and the suggestion from MS is to move to dotNET data types for automation. I’ve used the above solution in many of my projects and I’m sure many partners have used this for your existing solutions. When you are going to upgrade you will run into this code and will need to be upgraded to System.xml dotNET data type. The problem that partners will have is trying to implement transformNodeToObject function. I’ve provided a solution below that allows you to do the same process for System.xml.

RemoveNameSpaces

So your XML files will look before the transformation like this.

XMLFilewithNameSpaces

And after transformation like this.

XMLFIleNoNameSpaces

Attached is the CU object

Posted in Dynamics NAV | Comments Off