Question. In the codeunit below does the COUNT work? rec.reset rec.setrange(field,value) if rec.count()=1 then begin etc… etc… Answer. I don’t know. i have a situation now (NF2.6D+NT4+SQL7) where the count works most of the time, but sometimes fails. when i precede it with a rec.find(’-’) statement it seems ok. Does anyone have an explenation for thes problem? (Other than checking my data. cause the table is the same every time) Regards Mario,
Hello Mario. Typically I wouldn’t recommend using the Count function on SQL. Why, it has to iterate through all records. I would recommend using the CountApprox. As to having to put the find(’-’) I do this always based on similar experience to yours.
I would like input here. What exactly is failing? Is the count incorrect or is there an error message? Have you found a pattern to the problem? What are you achieving with the FIND(’-’) prior to the count? A FIND(’-’) against SQL server is relatively expensive because a cursor is opened to implement it. Thats fine if you intend to read through the filtered set, but if not then you have introduced quite an overhead. COUNT is only expensive if the filtered set defines many records (1000s), if it is a small set (e.g. Sales Lines for a particular header) the count is fine and CountApprox is not necessary.
Hi robert, micheal as i block the error statement from displaying (Actually i don’t block it but navision webshop does (which is a neat trick by the way)) i don’t know if an error is generated. i used the following code select rec.COUNT 0: no records exist so i need to add one. 1: 1 single record exists so i need to modify this one. else multiple records exist. this can’t be bail out and display error. i could not determine a pattern but it would sometimes return 0 when actually there was 1 record already in the database. i would then add one and the next time it would drop into the else statement. But i changed the entire code especially after the statement that a COUNT is more expensive then a FIND(’-’). The code works now. I upgraded the client from 2.6B to 2.6E but i didn’t check if the above problem still occured or was fixed in version 2.6C,2.6D or 2.6E cause i already changed the code and didn’t have time to check it out. so for now i will only do a count when i previuosly did a FIND(’-’) just to be on the safe side. Best regards,
I have a similar problem in the Shipment Document Report, we modified to get always 3 copies of that document when printed. Sometimes, fortunately not very often, we get the 3 copies with a different number of lines on each, and none of them with the right number of lines. When printing the document again, just after seeing the error, we get a right copy, with the total number of lines. As an example 1st copy is printed with 5 lines 2nd copy is printed with 9 lines 3rd copy is printed with 13 lines and the total number of lines on the document is 25. When asking for a second print we get 3 copies with 25 lines on each. As a debug, I wrote some code on the OnBeforeDataItem with .COUNT and a MESSAGE(…) when getting different Counts. When the printing fails I get a different number of records on each loop. After that I create a SQL View as a SELECT … FROM and create a Table on Financials based on that View. I added new debug COUNT over the ViewTable (same filters as DataItem). When I get the error I was surprised that the COUNT on the ViewTable always return the same number of records. Example when getting the error: 1st copy: DataItem.COUNT: 5, ViewTable.COUNT: 5 2nd copy: DataItem.COUNT: 9, ViewTable.COUNT: 5 3rd copy: DataItem.COUNT: 13, ViewTable.COUNT: 5 and the right number of lines is 25. Environment: NT 4 + SQL 7.0 + Financials 2.60D Got in contact with our NSC and get as answer that: “Navision Spain suggest that we upgrade to SQL 2000”. Does it means that SQL 7.0 is failing? Any other comments? Regards, Jesús Soage
I would solve the problem 0, 1, many records like this (avoiding the expensive COUNT)
If not myRec.Find('-') Then
message('Zero')
Else
If myRec.Next = 0 then
Message ('One')
else
Message ('Many');
------- With best regards from Switzerland Marcus Fabian
Mario: So the FIND is actually being used in place of a COUNT, rather than prior to it. I thought it was having a side-affect on a subsequent COUNT. Don’t use COUNT if you don’t need a record count but just need a 0 1 or more status. If your data is such that in most cases there are records present, then use the FIND solution. If there are mostly no records, use ISEMPTY first, and then a FIND if the set is not empty - ISEMPTY is much faster than FIND(’-’). The report problem sounds like a separate issue, and you can be fairly certain that going from 7.0 to 2000 will not change matters.
Hi all, sounds familar. It looks like a Problem with SQL. Please see link: http://www.cdi.org/nuclear/nukesoftware.txt Any comments?
The message below was found on the Dansih nsc support site. A short us/uk resume: If a table is empty when the users login, he will not see new records in the table created by others until he creates one himself. Only a problem on native, not on sql. The “workaround” is to do an if insert then delete Full msg in danish: Nye records i en ny tabel kan ikke ses fra andre klienter Spørgsmål Hvis jeg opretter en ny tabel og indsætter en ny record, kan denne ikke ses fra en anden klient. Hvis jeg derimod genstarter klienten, kan man se den indsatte record. Hvad er grunden til dette? Svar Når der oprettes en ny tabel i en Navision Financials database, markeres denne først som værende inaktiv. Første gang der indsættes en record i tabellen, ændres tabellens status til aktiv. Når en klient logger sig på databasen, læses først oplysningerne om, hvilke tabeller der pt. er aktive. Indsættes der derefter en record i en inaktiv tabel, aktiveres denne og den udførende klient opdateres. Men allerede forbundne klienter, får ikke besked herom, hvorfor indholdet ikke kan vises. Dette kan bl.a. føre til, at objekttabellen låses, når der via en dataport, indlæses i en inaktiv tabel. En mulig workaround på dette problem er, at indsætte følgende kode på dataportens OnPreDataItem: IF NOT Rec.FIND(’-’) THEN BEGIN Rec.INSERT; Rec.DELETE; COMMIT; END; Ligger databasen på en SQL Server, eksisterer problemet ikke, da tabeller her altid er aktive! Regards Peter
There is a requirement to use .FIND(-/+) after a setrange to build the recordset. If you do not, then the set does not necessarily exist properly (as you have found). And if you need to determine if there are any records then a FIND(’-’) is much more appropriate than a COUNT. clear()/.reset record record.setcurrentkey if required. record.setrangexN on record fields if record.find(’-’) then repeat until record.next=0; (or similar) Craig Bradney Technical Manager Navision Solutions & Services, Deloitte Touche Tohmatsu Email:cbradney@deloitte.com.au
Lets not pollute the issue. COUNT does not use any data structure, (recordset or otherwise) and has no relationship to the information used by FIND. This applies to both server platforms. On SQL, there are two types of count - a fast and regular form. The fast is only used for: Browse transactions (and UpdateNoLocks if the table is not locked); when no filter is applied; the table is a user table (as opposed to a view or system table). It uses the sysindexes table to obtain a record count - the COUNTAPPROX also does this if the criteria are met. The regular form uses a SELECT COUNT(*) … WHERE … . Both methods use separate SQL statements and do not rely on cursors used to implement FIND(-+). If there are cases where the count is incorrect then this is most likely a buffering or caching issue in the client, and not a SQL issue. If a stable reproduction can be made then it should be reported through the appropriate channels so that it might be included in an upcomming service pack. If you want you can use SQL Profiler to aid in determining if the statements received by the server are correct (and verify this by copying them to Query Analyzer, with a bit of modification). This should eliminate the presense of badly formed fliters etc and point to client memory or state as the problem. I regard an incorrect count as a serious error. Adding spurious statements to resolve the problem may indeed be a workaround, but should not lead to guess work as to why a problem exists, and should not be done as a new programming ‘standard’. Also, please use ISEMPTY and not FIND to test the existence of records.