Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread Date filter problem
Sat, May 9 2009 1:26 AMPermanent Link

"Jerry Clancy"
I'm on DBISAM 4.27 Build 4, D5.

I have this (result) filter set on a table with the BILL index set:

((DATETIME >= '2009-05-04') AND (DATETIME <= '2009-05-08') ) and (HOUSE='H')

Note that DATETIME is a field in the table.

The index set is as follows:

if FindField('BILL') <> nil then
AddIndex('BILL','BILL;DATETIME;SESSION;HOUSE;ROLLNO;QUESTION',
[ixCaseInsensitive]);

SESSION, HOUSE, and ROLLNO are common to all records.

Five records for one particular bill satisy this condition, with the
DATETIME field varying between '5/7/2009 2:45:00 PM' and '5/7/2009 3:43:00
PM'. However, when I do a FindKey for the bill with only the bill as an
argument, only the last bill with the '5/7/2009 3:43:00 PM' timestamp
results.

Why is this filter failing me? Why do I not see the first four records for
that bill?

Jerry
Sat, May 9 2009 5:09 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Jerry


I must be missing something here. FindKey simply repositions the cursor to the first record that matches the criteria so I don't see what it has to do with a filter.

Can you explain a bit more.

Roy Lambert [Team Elevate]
Sat, May 9 2009 11:04 AMPermanent Link

"Jerry Clancy"
The filter scopes the file by a date range and therefore has a bearing on
whether or not a FindKey is successful. The scope should include five
records for a bill, all on the same date. However, FindKey([bill]) only sees
the last one. Without the filter set all five records are "visible". This
tells me that either the filter isn't working properly or, more likely
usually, I'm not setting it correctly.

There is an active index set on these fields:
'BILL;DATETIME;SESSION;HOUSE;ROLLNO;QUESTION'

At the time I execute the FindKey([Bill]), with no other parameters, this is
the filter set:
((DATETIME >= '2009-05-04') AND (DATETIME <= '2009-05-08') ) and (HOUSE='H')

This should resolve to the first record in that bill set but instead moves
to the last one (and the filter is rendering the other four invisible). I do
not use the time in the filter and I'm wondering if this is somehow causing
my problem, yet it does find the last record of the five that meet this
criteria. It should find the first. All five records are dated '2009-05-07'
and so should all be visible regardless of the time setting because the
dates are all in the middle of the filter range. Only the last one is. (Used
DBiDesktop to verify this.)

I'm hoping someone sees something I don't or, perhaps, there was a bug in
that version (4.27). Guess I'll apply the latest later today.

Jerry


"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:9B80A4AE-883A-4556-9407-53D31873419D@news.elevatesoft.com...
> Jerry
>
>
> I must be missing something here. FindKey simply repositions the cursor to
> the first record that matches the criteria so I don't see what it has to
> do with a filter.
>
> Can you explain a bit more.
>
> Roy Lambert [Team Elevate]
>
Sat, May 9 2009 11:44 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Jerry


I don't think its a bug (could be wrong though); I think its just the way FindKey works, can you try a .Locate('BILL',whatever,[]) and see what it does?

Roy Lambert [Team Elevate]
Sat, May 9 2009 5:12 PMPermanent Link

"Malcolm"
I could be way off, but I thought that FindKey() would find the first
'exact' match or end up on the next (nearest) record after failing to
find a match.  So if it winds up on the final record it may mean the
find failed?  Does it return True?

What is the data type of "Bill"?

--
Sat, May 9 2009 8:33 PMPermanent Link

"Jerry Clancy"
The field BILL is a string and it's not the problem. In fact, all the fields
are type string except rollno, which is integer. The FindKey does return
True but sits on the last, not the first record. FindNearest seems to find
the first one.

I'm still playing with this and will report back in a while.

Jerry

"Malcolm" <malcolm@spam.will.bounce> wrote in message
news:1725A597-C907-44B8-9B58-805D02FD3230@news.elevatesoft.com...
>I could be way off, but I thought that FindKey() would find the first
> 'exact' match or end up on the next (nearest) record after failing to
> find a match.  So if it winds up on the final record it may mean the
> find failed?  Does it return True?
>
> What is the data type of "Bill"?
>
> --
>
Sat, May 9 2009 8:39 PMPermanent Link

"Jerry Clancy"
Take that back. FindNearest also finds the last one.

Jerry

"Jerry Clancy" <jclancy@billtrak.com> wrote in message
news:E8C9C296-63E1-4B29-A153-6876FCF4156E@news.elevatesoft.com...
> The field BILL is a string and it's not the problem. In fact, all the
> fields are type string except rollno, which is integer. The FindKey does
> return True but sits on the last, not the first record. FindNearest seems
> to find the first one.
>
> I'm still playing with this and will report back in a while.
>
> Jerry
>
Sat, May 9 2009 11:03 PMPermanent Link

"Jerry Clancy"
Got it. Turns out it's a data error: four of the five records for H.R. 1728
had a null HOUSE field in them (should have been 'H'), so the filter
correctly removed them from view. Didn't spot it until I made direct tests
on parts of the filter and, bingo, there it was. Sorry for not finding this
earlier and hope I've not wasted much of anyone's time.

My concern was that this was happening in one of our most complex reports, a
composite voting report for Congress. The user can slice and dice it by date
range, party, house, state(s), district(s) and even their position on bills
(Y and N replaced by S(upport) and O(ppose), and can view it in alpha order
or legislator within state order. Here's the proper result, if anyone cares,
for votes in the House last week with the last 39 pages deleted, as they are
simply dups with new sets of legislators:

 http://www.billtrak.net/us111/docs/VR090504ff.pdf

All but the last ("Passed") vote for H.R. 1728 were missing before. The bill
numbers are actually links to the bill text.

Now on to find why those House fields were blank, clearly my problem. My
thanks to Roy and Malcolm for responding.

Jerry


Sat, May 9 2009 11:28 PMPermanent Link

"Jerry Clancy"
Got it. Turns out it's a data error: four of the five records for H.R. 1728
had a null HOUSE field in them (should have been 'H'), so the filter
correctly removed them from view. Didn't spot it until I made direct tests
on parts of the filter and, bingo, there it was. Sorry for not finding this
earlier and hope I've not wasted much of anyone's time.

My concern was that this was happening in one of our most complex reports, a
composite voting report for Congress. The user can slice and dice it by date
range, party, house, state(s), district(s) and even their position on bills
(Y and N replaced by S(upport) and O(ppose), and can view it in alpha order
or legislator within state order. Here's the proper result, if anyone cares,
for votes in the House last week with the last 39 pages deleted, as they are
simply dups with new sets of legislators:

 http://www.billtrak.net/us111/docs/VR090504ff.pdf

All but the last ("Passed") vote for H.R. 1728 were missing before. The bill
numbers are actually links to the bill text.

Now on to find why those House fields were blank, clearly my problem. My
thanks to Roy and Malcolm for responding.

Jerry


Sun, May 10 2009 4:08 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Jerry


I hope the hiccoughs clear up soon Smiley

Roy Lambert
Image