Same filter (logical) but different result in Report

tothszabolcs

First of all, sorry for bad english. I try to describe clearly.

I don't need another solution to avoid this issue, I only need answer what is wrong with it, how can I fix it. Please help me.



Introduction:

I have two DataItem in my Report: "Vendor Ledger Entry" and under this with same level an "Integer".

I want to show records from "Purch. Inv. Header" (gRecPIH) table in Integer OnAfterGetRecord trigger with "Vendor Ledger Entry"."Document No." filter.

I have only one required filter which is "Document No.". ("Vendor Ledger Entry"."Document No.")

In Integer OnPreDataItem trigger I use setfilter of gRecPIH:

Integer - OnPreDataItem()

gRecPIH.RESET;

gRecPIH.SETFILTER(gRecPIH."No.", "Vendor Ledger Entry".GETFILTER("Document No."));



gCounter := 0;



IF gRecPIH.FINDSET THEN BEGIN

  REPEAT

    gCounter += 1;

    gRecTmpVLE.INIT;

    gRecTmpVLE."Entry No." := gCounter;

    gRecTmpVLE."Document No." := gRecPIH."No.";

    gRecTmpVLE."Document Type" := 2;

    gRecTmpVLE.INSERT(FALSE);

  UNTIL gRecPIH.NEXT = 0;

END;



SETRANGE(Number, 1, gRecTmpVLE.COUNT);

I have a temp table (Vendor Ledger Entry) and i save No-s into it from filtered Purch. Inv. Header table.

What is interesting in?



If i use different filter formats:

Interval: SB3/0000..SB3/9999

than gRecPIH.COUNT is 1952

gRecTmpVLE.COUNT is 1952. It is correct! Every record is saved, showed.



If i use another format:

Star: SB3/*

than gRecPIH.COUNT is 1952

gRecTmpVLE.COUNT is 501 !!! Incorrect result!

First five hundred records are shown than no more record. Where is the next index pointer? Why its gone?

I tried to use setcurrentkey and ascending but nothing helps.



Please help, how can i fix this filter/result problem?

vaprog

Are you on SQL or native?

Does it happen with the exact code you posted?

Try to find a set of documents as short as possible that causes the anomaly to occur and post those Document No.s.

Are you sure you are getting the first 501 document numbers and the rest is missing? or are there missing some that are sorted between included ones? Tell us the first missing document no. and the preceding one.

Does it happen the same when you use FIND('-') instead of FINDSET?

tothszabolcs

Hi vaprog!
Ty for replying!

Are you on SQL or native?

-SQL

Does it happen with the exact code you posted?

-Yes, i made a test report to figure what happening, and this anomaly is continouing with this report to, so this code is the test, and works with it. (not works actually, just the anomaly)

Try to find a set of documents as short as possible that causes the anomaly to occur and post those Document No.s.

- my filter is: SB3/* OR SB3/0000..SB3/9999
Interval gives back no-s from SB3/0001 to SB3/2069 this is the last one from SB3/ filter. Interval has 1952 records.
The other (SB3/*) filter start indexing from SB3/0001 to SB3/0506. This has 501 records. First 501 records. You can recognize that some nos is missing, but not this is the problem. Before 0506 is 0505, after that is 0507.

Are you sure you are getting the first 501 document numbers and the rest is missing? or are there missing some that are sorted between included ones? Tell us the first missing document no. and the preceding one.

- Above, BUT:
If i try to group them by Vendor, than the sorting is different, its not start from first record and not finished with last one!
Between them. What is this?
I dont understand why is that difference between this two way of filter usage.

Another Remark:
If I set more filter on request form without "*", for example: Vendor No.: 00000.. AND Doc no.: SB3/0000..SB3/9999 and Due Date, and i show them with getfilters function than sequence is fine: vendor no, doc no, due date (second place), but if i use sb3/* filter to Doc No than getfilters result sequence is this: vendor no, due date, doc no. (last one)

Does it happen the same when you use FIND('-') instead of FINDSET?

- with FIND('-') works interval and * and xxx|xyx other formats to.

What do u think, what is wrong with findset and indexes?

vaprog

tothszabolcs wrote:

I dont understand why is that difference between this two way of filter usage.

It really depends on your data and the sort order of the records (but actually, the version with * should return possibly more, but never less records).

tothszabolcs wrote:

Another Remark:
If I set more filter on request form without "*", for example: Vendor No.: 00000.. AND Doc no.: SB3/0000..SB3/9999 and Due Date, and i show them with getfilters function than sequence is fine: vendor no, doc no, due date (second place), but if i use sb3/* filter to Doc No than getfilters result sequence is this: vendor no, due date, doc no. (last one)

I never payed any attention to this. I don't think it has any bearing. It might have to do with some internal optimizations or maybe the fact that the filter with the wildcard is a true filter, the others are actually ranges.

tothszabolcs wrote:

- with FIND('-') works interval and * and xxx|xyx other formats to.

Do i understand correctly, that when using FIND('-') instead of FINDSET, you are getting correct results with any type of filter?
FINDSET retrieves a configurable number of records in one batch. If you later request more records using NEXT, NAV should then fetch more records from SQL SERVER using a different method. With FIND('-'). NAV sets up a cursor and fetches records one by one. The configurable number used to default to 500 with NAV 5.0 and later this default number was changed to 50. You may try to reconfigure this and see, whether this influences the number of records you get with the *-filter. You find it in File | Database | Alter... | Advanced | Caching | Record Set.

I suggest you search Microsofts knowledge base and the internet to find out whether this issue is caused by a bug in NAV (-> use a newer build) or SQL-Server (use a supported version, also check for unusual trace flags) or your software environment/configuration.

tothszabolcs

Yes, with find('-') it works fine with any type of filter.
I will check that configurable number in NAV.

Thank you for your help, advice!

tothszabolcs

I checked it.

I modified the cache size and with higher value it works fine with * filter. Otherwise "wrong" result. Actually not wrong, its only cach size.

So anomaly works only indirect mode, i mean if i use * filter in a request form and i show records from that dataitem than it works fine.

If i use filter indirect:

gRecItem.SETFILTER(gRecItem."No.", Item.GETFILTER("No."));

or

gRecItem.SETFILTER(gRecItem."No.", 'mr*');

than i set up FINDSET for gRecItem:

IF gRecItem.FINDSET

now repeat until steps till it reaches cache size limit.

With other fields * filter is works fine, i get cache size limit only with PK.

So
Not recommended:
PK + Filter(*) + FINDSET(cycle) => cached record(cache size limit) - less than expected size

Recommended:
PK + Filter(interval or anything else expect *) + FINDSET(cycle) => expected size
PK + Filter(*) + FIND('-')(cycle) => expected size
Fields + Filter(anything) + FINDSET/FIND('-')(cycle) => expected size

ta5

Is this a bug in finsql.exe? :?:
Regards
Thomas

tothszabolcs

I dont know yet, but research in progress and if I find something i will post here.

tothszabolcs

UPDATE:
I checked it in different clients:

This anomaly is only in NAV 5.0 SP1!
In 2009 R2 (6.00.32012) PK filter with Wild Card and FINDSET gives correct results!

It might be a BUG in NAV 5.0 SP1!

tothszabolcs

UPDATE:
Behind the scenes. Maybe it is not work properly in nav 5.0 and a bug occurs this behavior. Anyway, that link shows how works cal in sql. (my problem is below this "Now, what about the number of records? Does that count?")