Selecting Records to Print

[Charles Hoens]

I am having a problem selecting records to print. I have a checkbook, where I need to print only the last 50 records of the database on a page. The checkbook database is sorted by Checkdate+Checknum.

I have looked in this forum and in the Help file, but can't find a good solution, although it seems obvious that it should be fairly straightforward.

Any help would be greatly appreciated.

Charlie

[Tom Cone Jr]

Charlie, how do you define "last 50 records" ?

-- tom

[Stan Mathews]

I am having a problem selecting records to print. I have a checkbook, where I need to print only the last 50 records of the database on a page. The checkbook database is sorted by Checkdate+Checknum.

I have looked in this forum and in the Help file, but can't find a good solution, although it seems obvious that it should be fairly straightforward.

Any help would be greatly appreciated.

Charlie

I think you could do this with a button on a form based on the checkbook table. Something like

tbl = table.current()
query.filter = ""
query.order = "cdate(Checkdate)+alltrim(str(Checknum))"
qry = tbl.query_create()
recs = qry.records_get()

rep.filter = "between(recno(),"+(recs-49)+","+recs+")"
rep.order = "cdate(Checkdate)+alltrim(str(Checknum))"

report.print("reportname",rep.filter,rep.order)

[Charles Hoens]

Records to Print

Thanks Stan. I was working along those lines, but got stumped on the query. Will this select the last 50 records (to answer Tom's query) that are in the index or just the last 50 in recno() order?

What we need (as an aside) is a good dBaseIII command called Skip(). Within the parens you used to put a number and the Skip() command would take you to that record. i.e. Skip(50) would move you forward 50 records even in an indexed database. Also Skip(-50) would take you back 50 records, so in this case it used to be easy because you would fetch_eof() then Skip(-50) and there you were and could print from there.

I also found this to be useful when opening a browse/form. Oftentimes you do not not to see the first record of a browse, but the last 50 or so records.

Alas, I haven't been able to achieve this feat. Maybe it should go into the "Wish List" unless I can find another way to do it. I've tried several long ways, but they are all inefficient and slow. (i.e. fetch_prev() 50 times)

Thanks,

Charlie

[Stan Mathews]

One simple approach may be to let Alpha do the work for us.

rep_filter = "bottom_records(50,\"\")"
rep_order = "Checkdate+Checknum"
Report.print("reportname",rep_filter,rep_order)

[Charles Hoens]

Running Total

Stan,

This is all working now. Thanks so much!

One more complexity though. Becasue this is a checkbook listing, I have a column in the far right showing the Running Total. If I run the report with all records, this total runs and displays fine, however if I filter it for just the last 50 records, the total is wrong.

What I am thinking about is a way to run some code to run the report, find the 50th from the bottom record, record in a variable the Running Total at that point, then re-run the report for real and use this variable to then calculate what the real Running Total should be on the report.

If you have any ideas, please let me know. Or is my approach wrong? I'm working on it now.

Many thanks as always,

Charlie

[Charles Hoens]

Problem Solved?

Stan,

I played around with it for awhile and came up with the following code, which for me, at least, solves the problem. It's brute force, but it seems to do the job and I added the code just before I went to print.

t= table.open("checkreg")
query.filter = ""
query.order = "cdate(paiddate)+alltrim(Cknum)"
qry = t.query_create()
recs = qry.records_get()

dim totbal as n
dim runbal as n
dim recnum as n
dim global ckstart as n
recnum=0
runbal = 0
totbal=round(tablesum("checkreg",".t.","paidamt")*-1,2)
t.fetch_last()
while recnum < 48
runbal=runbal+t.paidamt
t.fetch_prev()
recnum=recnum+1
end while
t.close()
ckstart=round(totbal+runbal,2)

In the body of my report I defined the field I wanted to have the running total in as follows: runbal = (run_total(Paidamt)*-1)+var->ckstart

What do you think???

Charlie

PS: Everything with me is brute force! That's why I love Alpha. It allows me to bang away at things, yet admire the elegance of others!

[Jerry Hartness]

Checkbook

Charles, There has been alot of inquiries on how to create a workable checkbook that goes back to (I think) ver 4. Maybe you can give us a sample program on how you managed to do it. I've tried several times but can't get one to work exactly live Quicken.

Jerry

[Charles Hoens]

Checkbook

Jerry,

I think the best rendition on how to make a checkbook (along with explanations) was Dr. Wayne's book on Alpha. I still use it as a reference guide!

My personal solution to it was to put all my information into a browse on a form. On the side of the form I placed several calculated fields, such as total balance, unreconciled balance, reconciled balance, running total (which changes when I change the browse position) and bank balance. In my app, I calculate the bank statement balance so all I have to do is check off the checks and deposits from the statement and then compare the bank statement balance to what I have on the screen.

Doing it this way, gets around the massive problem of having to redo the database just in order to get a display on a screen browse. On the report, the running balance works fine.

Let me know if you need the calculated fields.

Charlie

[Jerry Hartness]

Checkbook

Charles, since you're still on line. I would be interested in the calculations. I have trouble with the running balance. In ver 3 (dos) it was easy, but haven't figured out exactly how to do it. Tom Cone gave some suggestions but couldn't get that either. (on another app)

thanks

jerry