mibuso.com

Microsoft Business Solutions online community
It is currently Mon Jul 28, 2014 8:15 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: [Solved] Same filter (logical) but different result in Report
PostPosted: Wed Aug 21, 2013 2:09 pm 
Offline

Joined: Wed Aug 21, 2013 12:58 pm
Posts: 19
Country: Hungary (hu)
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:


Code: Select all
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?


Last edited by tothszabolcs on Thu Aug 22, 2013 8:32 am, edited 1 time in total.

Top
 Profile E-mail  
 
 Post subject: Re: Same filter (logical) but different result in Report
PostPosted: Wed Aug 21, 2013 3:38 pm 
Offline

Joined: Fri Nov 09, 2007 1:20 pm
Posts: 434
Location: Germany
Country: Germany (de)
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?


Top
 Profile  
 
 Post subject: Re: Same filter (logical) but different result in Report
PostPosted: Wed Aug 21, 2013 4:35 pm 
Offline

Joined: Wed Aug 21, 2013 12:58 pm
Posts: 19
Country: Hungary (hu)
Hi vaprog!
Ty for replying!


Quote:
Are you on SQL or native?

-SQL

Quote:
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)

Quote:
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.

Quote:
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)

Quote:
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?


Top
 Profile E-mail  
 
 Post subject: Re: Same filter (logical) but different result in Report
PostPosted: Wed Aug 21, 2013 5:54 pm 
Offline

Joined: Fri Nov 09, 2007 1:20 pm
Posts: 434
Location: Germany
Country: Germany (de)
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.


Top
 Profile  
 
 Post subject: Re: Same filter (logical) but different result in Report
PostPosted: Thu Aug 22, 2013 8:25 am 
Offline

Joined: Wed Aug 21, 2013 12:58 pm
Posts: 19
Country: Hungary (hu)
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!


Top
 Profile E-mail  
 
 Post subject: Re: Same filter (logical) but different result in Report
PostPosted: Thu Aug 22, 2013 9:42 am 
Offline

Joined: Wed Aug 21, 2013 12:58 pm
Posts: 19
Country: Hungary (hu)
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:
Code: Select all
gRecItem.SETFILTER(gRecItem."No.", Item.GETFILTER("No."));

or
Code: Select all
gRecItem.SETFILTER(gRecItem."No.", 'mr*');


than i set up FINDSET for gRecItem:
Code: Select all
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


Top
 Profile E-mail  
 
 Post subject: Re: Same filter (logical) but different result in Report
PostPosted: Thu Aug 22, 2013 10:56 am 
Offline

Joined: Thu Sep 09, 2004 2:04 pm
Posts: 1077
Location: Switzerland
Country: Switzerland (ch)
Is this a bug in finsql.exe? :?:
Regards
Thomas


Top
 Profile  
 
 Post subject: Re: Same filter (logical) but different result in Report
PostPosted: Thu Aug 22, 2013 11:21 am 
Offline

Joined: Wed Aug 21, 2013 12:58 pm
Posts: 19
Country: Hungary (hu)
I dont know yet, but research in progress and if I find something i will post here.


Top
 Profile E-mail  
 
 Post subject: Re: Same filter (logical) but different result in Report
PostPosted: Thu Aug 22, 2013 11:57 am 
Offline

Joined: Wed Aug 21, 2013 12:58 pm
Posts: 19
Country: Hungary (hu)
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!


Top
 Profile E-mail  
 
 Post subject: Re: Same filter (logical) but different result in Report
PostPosted: Mon May 12, 2014 9:48 pm 
Offline

Joined: Wed Aug 21, 2013 12:58 pm
Posts: 19
Country: Hungary (hu)
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?")


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: divyesh10 and 15 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: