Return number of records in a range?

[Rick Gerlach]

Can anyone tell me how to return the actual number of records visible after setting a range?

To preface this, I've been using Alpha for a very short time, coming from a Clipper background. I'm attempting the equivalent of a SET FILTER TO expression, but queries and ranges are as close as I can get.

I got a query to work OK, but I thought I'd try a range, since the manual makes it sound like if someone adds a record during the search, the range will be updated.

Consider the UDF below. The "test" tag is indexed on PRODID, which I pass into the UDF as "id". I just want to see records matching "id" that fall between the specified date range.

For the life of me, I can't get this range to work, and I can't even tell how many records it is returning.

I'm beginning to feel like there's a bug in ranges if the first & last values are the same.

Anyone have a clue as to what I'm doing wrong? Or am I just beating my head against a wall?

FUNCTION aging AS N (id AS C )
t= table.open("volume")
i= t.index_primary_put("test")
dstart = date()-150
dend = date()
range.flags = range_index .or. range_first .or. range_last .or. range_filter
range.index_pointer = i
range.filter = "between(entrydate,dstart,dend)"
t.range_add(id, id)
ntot = 0
while .not. t.fetch_eof()
ntot = ntot + t.stockcur
t.fetch_next()
end while
aging = ntot
END FUNCTION

Thanks,
Rick

[Tom Cone Jr]

RE: Return number of records in a range?

Rick,

I think the syntax of your filter expression is wrong.

Try using the Between_date() function instead, it's simpler.
"pre"
range.filter = between("entrydate", dstart, dend)

"/pre"

Another approach, using between() is illustrated and explained in the discussion of the Tablecount() function. Look for the discussion of "Filter a Range of Dates Using Variables" appearing in that material.

It would also be prudent to close the table you open each time the function is run.

-- tom

[Tom Cone Jr]

RE: Return number of records in a range?

Rick,

I think the syntax of your filter expression is wrong.

Try using the Between_date() function instead, it's simpler.

range.filter = between("entrydate", dstart, dend)

Another approach, using between() is illustrated and explained in the discussion of the Tablecount() function. Look for the discussion of "Filter a Range of Dates Using Variables" appearing in that material.

It would also be prudent to close the table you open each time the function is run.

-- tom

[Tom Cone Jr]

RE: Return number of records in a range?

(ONE MORE TIME, ignore the previous)

Rick,

I think the syntax of your filter expression is wrong.

Try using the Between_date() function instead, it's simpler.

range.filter = between_date("entrydate", dstart, dend)

Another approach, using between() is illustrated and explained in the discussion of the Tablecount() function. Look for the discussion of "Filter a Range of Dates Using Variables" appearing in that material.

It would also be prudent to close the table you open each time the function is run.

-- tom

[Rick Gerlach]

RE: Return number of records in a range?

Tom,

Well, I tried this code completely without the range, and it doesn't return an accurate result either.

It returns like 1426 records when there should be 3 on the first pass, then if I run it again it returns 0.

Nobody uses filters in code in Alpha, or is it just that people use queries instead?

Oh, and I tried looking for the information you mentioned re:TABLECOUNT() and I couldn't find it in the Xbasic reference or the Expression Functions Reference either.

I can code around this using a find and a while loop, but it's just amazing to me that something as basic as filtering/ranges is so poorly documented.

FUNCTION aging AS N (id AS C )
	t= table.open("volume")
	i= t.index_primary_put("test")
	dbegin = date()-150
	dend = date()
	range.flags = range_first .or. range_last .or. RANGE_INDEX
	range.index_pointer = i
	t.range_add(id, id)
	ntot = 0
	while .not. t.fetch_eof()
		ntot = ntot + t.stockcur
		t.fetch_next()
	end while
        aging = ntot
	t.close()
END FUNCTION

[Rick Gerlach]

RE: Return number of records in a range?

OK, I think I've figured it out if anyone can make use of this info. The range is undefined until the record pointer is moved.

After fetch_first(), if there are no records in the range, fetch_eof() is .T.

This function works:

FUNCTION aging AS N (id AS C )
t = table.open("volume")
i = t.index_primary_put("test")
dbegin = date()-120
dend = date()
range.flags = range_first .or. range_last .or. RANGE_INDEX .or. RANGE_FILTER
range.index_pointer = i
range.filter = "between(entrydate,dbegin,dend)"
t.range_add(id, id)
t.fetch_first()
if t.fetch_eof()
trace.writeln("No records")
end if
ntot = 0
while .not. t.fetch_eof()
trace.writeln(t.prodid + dtoc(t.entrydate))
ntot = ntot + t.stockcur
t.fetch_next()
end while
aging = ntot
t.close()
END FUNCTION

[Tom Cone Jr]

RE: Return number of records in a range?

Rick, this rings a very faint bell. Wish my memory were better.

It's as though the record pointer has lost its place until you move it to the top of the range. Right?

-- tom

[Rick Gerlach]

RE: Return number of records in a range?

This is actually standard behavior for a filter in xbase dialects - the filter/range is undefined until the record pointer moves, and EOF() is used to see if the filter/range is empty or not.

This probably would have been easier for me to figure out, but I don't know A5's XBasic commands very well yet, early days for me.

Thanks again for the emails.

Rick