Alpha Video Training
Results 1 to 8 of 8

Thread: Getting a record count

  1. #1
    Steve Sawyer
    Guest

    Default Getting a record count

    Hi

    Hope someone can help. Having a problem with getting a record count back into a variable. All I want to do is query a table for a value, return a count pop that into a variable and then update a text field on the form. No pop up queries.

    i.e
    RecCount = count(select * from transaction where collected = false)

    Nice & easy in SQL & it probably is in Alpha!

    Thanks
    Steve.

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

    Default RE: Getting a record count

    tbl = table.current()
    query.description = ""
    query.order = ""
    query.filter = "collected = 'false'"
    'or query.filter = ".not. collected"
    'depends whether collected is character or logical field
    query.options = "I"
    i = tbl.query_create()
    RecCount = i.records_get()
    end

  3. #3
    "Certified" Alphaholic Scott Emerick's Avatar
    Real Name
    Scott Emerick
    Join Date
    Dec 2000
    Location
    Central Virginia/ North Carolina Border
    Posts
    2,154

    Default RE: Getting a record count

    Steve,

    Assuming your running the script from the form you want to update, there is no need for a variable.

    Try this:

    textfield.value = dbcount("yourtable.dbf","collected",".f.")

    This will put the result of dbcount() directly into your field called "textfield"

    Hope this helps.

    Scott

  4. #4
    Member
    Real Name
    Barry Rochford
    Join Date
    Apr 2000
    Posts
    452

    Default RE: Getting a record count

    As another option you can use "A5_Get_Records_In_Query()"

    It provides a one stop query & count.

    -Barry

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

    Default RE: Getting a record count

    After a little side discussion, Scott and I might suggest

    tablecount("yourtable.dbf",".not. collected")

    or

    tablecount("yourtable.dbf","collected = 'false'")

    if the answer is required in an area where a script cannot be run, such as field rules. Tablecount() does not require that an index be present for the field in the filter.

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

    Default RE: Getting a record count

    Good one Barry, I'm still assimilating the new functions into my memory banks.

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

    Default RE: Getting a record count

    IAII

    Not over a network, table of 4,781,102 records, complex query filter of five character string contained in thirteen character string field.

    Using tablecount() returned 60,179 records in 36 seconds
    Using A5_get_records_in_query() returned 60,179 records in 34 seconds.

    Over a network, same table and query, three passes with tablecount() yielded times of 134, 117, and 140 seconds.
    Three passes with a_get_records_in_query() yielded times of 143, 172, and 155 seconds.

    Go figure.

  8. #8
    Steve Sawyer
    Guest

    Default RE: Getting a record count

    Thanks guys!
    Very helpful. I did get this to work with a query using the silent option. But it did seem a little OTT for such a simple task.

    Thanks again for your help.

    Steve.

Similar Threads

  1. Queryrun retuns 0 record count
    By Ohlen Cartmell in forum Alpha Five Version 5
    Replies: 0
    Last Post: 07-25-2005, 05:02 AM
  2. Index on form record count
    By chadg@epix.net in forum Alpha Five Version 5
    Replies: 3
    Last Post: 01-04-2005, 05:10 PM
  3. Why is there a wrong record count?
    By michaelklein in forum Alpha Five Version 5
    Replies: 15
    Last Post: 08-05-2004, 06:50 AM
  4. Record count in filtered form
    By crimmelcp in forum Alpha Five Version 5
    Replies: 3
    Last Post: 03-22-2004, 07:39 AM
  5. query and count record
    By ken tjia in forum Alpha Five Version 4
    Replies: 0
    Last Post: 01-22-2004, 09:24 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
  •