COUNT Function Returns Incorrect Results

SHardy

Please see attached details which came to light while investigating another issue
(http://www.mibuso.com/forum/viewtopic.php?p=43570#43570)

OK, it seems that I panic too much. It would appear that the problem doesn't lie with my code, but with Navision's COUNT function.

On further investigation I found a link between the tables regarding the records that weren't updated... all of these records were at the end of the tables. Therefore the update was getting so far through the table, and then stopping.

Again, looking through this further, I ran a SQL query against one of the tables to get a record count, then I ran a COUNT against the table in Navision.... BINGO!!! Different results for some bizarre reason.

Sticking within Navision I then found the following:

1) The following code does not return the correct record count:

rBACSLedg.RESET;
MESSAGE('Records: ' + FORMAT(rBACSLedg.COUNT));

2) The following code does return the correct record count:

rBACSLedg.SETRANGE(rBACSLedg."Entry No.",0,9999999);
MESSAGE('Records: ' + FORMAT(rBACSLedg.COUNT));

So, in actual fact an unfiltered table has less records than a filtered table!

Am I alone in thinking that this is a tiny bit unusual?

Has anyone come across this before?
Do you know what causes this?

This does not appear to be an issue with every table in every database. Other tables within this database were fully updated. Also, my local database updated every table successfully.

Any further help would be greatly appreciated.

Thanks,
Simon

SHardy

For clarification, we are running Navision over a SQL Server database.

After further investigation, it would appear that when no filter is applied it is taking the record count from the SQL Server statistical information. This is not a true record count.

This is contradictory to the help file:

If no filters are set, the function tells you the total number of records in the table.

Also, there is a function specifically for returning this value - COUNTAPPROX.

I am, therefore, assuming that this is a bug.

Can anyone confirm this?
Is this something that has been identified & fixed in a later version? (not that this will be of any comfort. We are not about to upgrade)

Many Thanks

ara3n

This might help

http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=16683