Alpha Video Training
Results 1 to 18 of 18

Thread: "count" on records

  1. #1
    Member
    Real Name
    Brent Johnson
    Join Date
    Nov 2009
    Posts
    57

    Default "count" on records

    I realize this has to be the easiest problem to solve, but for some reason I am drawing a blank.

    I have a report that list several fields. I want to count (quantity) the number of times each field appears on the report.

    Ex:

    Total Records = 64
    Doors (appears on record) = 10
    Windows (appears on record) = 5
    Etc…

    When I use Count( doors ), the result is 64…same with all the fields.

  2. #2
    Member
    Real Name
    Brent Johnson
    Join Date
    Nov 2009
    Posts
    57

    Default Re: "count" on records

    Sorry for the confusion -

    Should have read:

    Total Records = 64
    Doors (appears on report) = 10 times
    Windows (appears on report) = 5 times
    Etc…

  3. #3
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Question Re: "count" on records

    Hi Brent,

    Not quite sure what value you are looking for, for Doors.

    The number of times doors are within the 64 records? ie 1-64.

    Or the total number of doors for all 64 records?

    Have you tried to drag the field you want the Count of to the footer and selected Count from the dialog box?
    Last edited by Keith Hubert; 01-14-2010 at 07:41 AM.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  4. #4
    Member
    Real Name
    Brent Johnson
    Join Date
    Nov 2009
    Posts
    57

    Default Re: "count" on records

    Keith,

    Actually, wanting the number of times "Doors" appears on the report. I have 30-35 in the record, only 10 on the report.

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

    Default Re: "count" on records

    Brent, I share Keith's confusion.

    Is "doors" a separate field in each record of your table? If not, is it a field value that appears in some records but not others? What is the name of the field that might contain this value? Is the field value the character string "DOORS" or something else?

    We have no way to know what you mean when you say

    "Doors" appears on the report?

    Try describing it to us as you would to a blind man. After all we can't see your database, your table, your report, and have no context in which to try to interpret what you've written.

  6. #6
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: "count" on records

    Brent,

    Does that mean you have 30-35 records with a value greater that 0 in the report of 64 records?
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  7. #7
    Member
    Real Name
    Brent Johnson
    Join Date
    Nov 2009
    Posts
    57

    Default Re: "count" on records

    Sorry for the confusion:

    I have a table that contains a field “Doors”. Within this table, some of the records have information entered into “Doors”, some do not.

    I have a report that contains the field “Doors”, which list 10 times on the report the information contained in the table.

    I want to create a Count for the number of times this information appears on this report. When I use the Count() function Count (Doors), I get the total amount of records stored within the table.

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

    Default Re: "count" on records

    Much better.

    Permit me to rephrase in alphaspeak...

    You want a count of the number of RECORDS that appear in your report that CONTAIN a NON BLANK value in the DOORS field of each RECORD.

    A good approach to this takes two steps.

    a) create a new calculated field in your report layout. Define an expression that will return 1 if the DOORS field is not blank or that will return a 0 if the DOORS field is blank. Something like this should work:
    Code:
    IF(is_blank("Doors"),0,1)
    b) drag that new calc field into the report footer and tell the genie to TOTAL it across all the records in the report. The effect is to add all the zeros and ones together, toget a count.

    Make sense?

  9. #9
    Member
    Real Name
    Brent Johnson
    Join Date
    Nov 2009
    Posts
    57

    Default Re: "count" on records

    Tom,

    Thank you for your help. I tried entering the function you suggested, and I get an "Invalid or incomplete expression".

    I even copied and pasted your function - same result.

  10. #10
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: "count" on records

    Hi Brent,

    If you can post a small sample of your application you will be surprised at the result.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

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

    Default Re: "count" on records

    IF(isblank("Doors"),0,1)

    I did say "something like this"... :)

    I suppose I imagined you might crack open the help file and look for information on the is_blank function. You would have seen that I mistyped it including an unnecessary underscore.

    Tip: when you get help from the message board that involves new functions or expressions take a couple of minutes, with the help file open beside you, to figure out why and how the suggested expression works (or doesn't). It's how one climbs the Alpha Five learning curve.

    Good luck with your project.

  12. #12
    Member
    Real Name
    Brent Johnson
    Join Date
    Nov 2009
    Posts
    57

    Default Re: "count" on records

    I have searched through the Alpha Help and cannot figure out what I’m doing wrong. The attached should help in explaining my problem.

    The total for door is accounting for the blank record.

    Please help!!!

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

    Default Re: "count" on records

    Quote Originally Posted by brentjohnjohn View Post
    I have searched through the Alpha Help and cannot figure out what I’m doing wrong.
    Please help!!!
    Please help!!! us help you!!!
    Quote Originally Posted by Keith
    If you can post a small sample of your application you will be surprised at the result.
    Pictures of the output are pretty but don't reveal what expressions you are using.
    Last edited by Stan Mathews; 01-14-2010 at 12:01 PM.
    There can be only one.

  14. #14
    Member
    Real Name
    Brent Johnson
    Join Date
    Nov 2009
    Posts
    57

    Default Re: "count" on records

    Stan,

    door_count expression “count(Door)”
    window_count expression “count(Windows)”


    The Door & Window are character fields.

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

    Default Re: "count" on records

    And why are you using those expressions when it has already been explained to you that you need to

    create a calculated field

    door_count = IF(isblank("Door"),0,1)

    create anther calculated field

    window_count = IF(isblank("Windows"),0,1)

    Then drag each in turn into the footer where you want the count to appear and when the genie appears, specify TOTAL.
    There can be only one.

  16. #16
    Member
    Real Name
    Brent Johnson
    Join Date
    Nov 2009
    Posts
    57

    Default Re: "count" on records

    Stan,

    When Tom posted his response, I assumed “Doors” was my field – therefore, I inserted the field.

    Thanks’ to you, as well as Tom and Keith for your help.

  17. #17
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Red face Re: "count" on records

    Hi Brent,

    Can we assume that you have understood how this works and we can now close the DOOR on this one?
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  18. #18
    Member
    Real Name
    Brent Johnson
    Join Date
    Nov 2009
    Posts
    57

    Default Re: "count" on records

    Keith,

    Yes sir. Please close the "Door" on this one.

Similar Threads

  1. "Field Display Formatting" is Lost when "Pre-fetch data for Detailed View = Active"
    By PhillipS in forum Application Server Version 9 - Web/Browser Applications
    Replies: 0
    Last Post: 07-23-2009, 11:02 PM
  2. form.view("fName","","","center","center")
    By reynolditpi in forum Alpha Five Version 9 - Desktop Applications
    Replies: 5
    Last Post: 07-15-2008, 12:37 AM
  3. "Add" and "Save" buttons don't stay on "Self" page
    By Scholin in forum Application Server Version 8
    Replies: 2
    Last Post: 02-05-2008, 08:50 AM
  4. "License count exceeded" problem
    By JayMoskovitz in forum Alpha Five Version 8
    Replies: 3
    Last Post: 07-10-2007, 08:58 PM
  5. Report record count "filtered"
    By Ed Barley in forum Alpha Five Version 7
    Replies: 2
    Last Post: 07-16-2006, 03:07 PM

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
  •