Alpha Video Training
Results 1 to 10 of 10

Thread: Selecting Records to Print

  1. #1
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,171

    Default Selecting Records to Print

    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

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default

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

    -- tom

  3. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default

    Quote Originally Posted by 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
    I think you could do this with a button on a form based on the checkbook table. Something like

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

  4. #4
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,171

    Default 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

  5. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default

    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)
    Last edited by Stan Mathews; 12-27-2005 at 11:46 AM.

  6. #6
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,171

    Default 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

  7. #7
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,171

    Default 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!

  8. #8
    Member Jerry Hartness's Avatar
    Real Name
    Jerry Hartness
    Join Date
    Apr 2000
    Location
    Knoxville, TN
    Posts
    427

    Default 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

  9. #9
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,171

    Default 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

  10. #10
    Member Jerry Hartness's Avatar
    Real Name
    Jerry Hartness
    Join Date
    Apr 2000
    Location
    Knoxville, TN
    Posts
    427

    Default 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

Similar Threads

  1. Selecting records for a report
    By Stephen Tierney in forum Alpha Five Version 6
    Replies: 2
    Last Post: 09-12-2005, 08:16 AM
  2. selecting records
    By Ernest Paushter in forum Alpha Five Version 5
    Replies: 1
    Last Post: 02-13-2003, 07:55 AM
  3. Selecting records for Print
    By Rick Hamilton in forum Alpha Five Version 4
    Replies: 3
    Last Post: 06-23-2002, 10:55 AM
  4. SELECTING RECORDS TO PRINT
    By Mark Donnison in forum Alpha Five Version 4
    Replies: 2
    Last Post: 10-30-2001, 09:16 PM
  5. Selecting Primary Index at Report Print TIme
    By Jimmy Johnson in forum Alpha Five Version 4
    Replies: 3
    Last Post: 07-27-2000, 01:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •