Incredibly strange error from COUNT function

Dakkon

I'm running into a bizarre situation where calling COUNT on a table is generating a very unexpected error. The error is as follows:

The Dynamic Move Line already exists. Identification fields and values: ...

As you can see, this is the typical error that you would get when attempting to insert a duplicate key entry into a table. The error is raised from the following code line:

IF DynamicMoveLine.COUNT > 1 THEN

What makes this yet even stranger is that prior to this code (in my test case) I have called DELETEALL before inserting a single line. Obviously if all the records are deleted and one entry is then inserted, there should be no key collision. I have never in my life seen Navision generate a "line already exists" error from a call to the COUNT function. I'm assuming this must be a bug in the new RTC nav engine. Has anyone else seen this happen? Does anyone know what is going on here?

Kishorm

This is caused by the buffered insert functionality which basically delays INSERTs and then add them all in one go. The code before this statement is adding the ducplicate record but it's only when this statement is run that NAV decides to actually do the bulk insert and you get the error.

In later versions of NAV you can disable this bulk/delayed insert functionality, there is an option called "Enable Buffered Insert" which you can untick in the Administration tool to stop this delayed behaviour - you'll then see exactly where the error is coming from (i.e. the INSERT statement)

Kishorm

This is caused by the buffered insert functionality which basically delays INSERTs and then add them all in one go. The code before this statement is adding the ducplicate record but it's only when this statement is run that NAV decides to actually do the bulk insert and you get the error.

In later versions of NAV you can disable this bulk/delayed insert functionality, there is an option called "Enable Buffered Insert" which you can untick in the Administration tool to stop this delayed behaviour - you'll then see exactly where the error is coming from (i.e. the INSERT statement)

Dakkon

Thank you Kishorm, the explanation is much appreciated. I'm still puzzled as to how it is encountering a duplicate record on insert (given that I'm calling DELETEALL prior), but that is a separate issue.

Kishorm

No problem!

After your DELETEALL you must be INSERTing some records (otherwise there'd be no point doing a COUNT?) so it's during these INSERTs that you are adding 2 records with the same key.

Dakkon

Actually it turned out to be an issue in the filters applied before the DELETEALL. I was actually only inserting one record because it was a test scenario, the COUNT check is there for production situations, but I was simply trying to test a specific scenario. The issue was that the incorrect filters resulted in not all of the existing records being deleted that I had expected.