Alpha Video Training
Results 1 to 25 of 25

Thread: Total() function in a form???

  1. #1
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Total() function in a form???

    I'm trying to create a calculated field in a form to add up all of a students class hours based on an ID number. The form I created have has an embedded browse that shows all the students classes and hours. How can I create a field to add up all the hours ONLY for the student/ID Number who has focus???

    I've tried to use the Total() function but its giving me the total for the entire table, my problem is getting it to only count the hours for the ID that has focus. Please Help!

  2. #2
    Member
    Real Name
    Doron
    Join Date
    Dec 2011
    Location
    NJ, USA
    Posts
    174

    Post Re: Total() function in a form???

    Hi Ivan,

    You can create view to handle that in the database side and use that in the component.
    Here is something similar I did and re-use that all over the No Interest Loan Application.

    Code:
    SELECT       
        LoansFK, SUM(VoucherAmount) AS TotalDisbursedAmount
    FROM
        dbo.Vouchers
    GROUP BY LoansFK, Status
    HAVING  (Status = 'Printed')
    You can have code like that to create the actual view:
    Code:
    CREATE VIEW [dbo].[vStudentsTrans]
    AS
    SELECT       
        StudentPK, SUM(Hours) AS TotalHours
    FROM
        dbo.Students
    GROUP BY StudentPK
    My view had more calculations and the benefit is that the back end calculations is always faster from the front end.
    You can use just a grid and then use the Detail View to show each student record in details.

    Regards,

    Doron
    The Farber Consulting Group, Inc.

    Web site: http://www.dFarber.com
    MS SQL Blog: http://www.dfarber.com/computer-consulting-blog.aspx
    Convert Ms Access to Web
    MS SQL Remote DBA
    Alpha Five Development
    No Interest Loans Application
    Last edited by DoronF; 02-18-2014 at 12:45 PM.

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

    Default Re: Total() function in a form???

    Ivan, is this a desktop app or web based?

  4. #4
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Re: Total() function in a form???

    Currently desktop, but will be going web based in coming months

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

    Default Re: Total() function in a form???

    Ok,

    Check the Invoice form in the AlphaSports sample database that ships with Alpha Five. The "Total" calc field on that form will be of interest, I think.

  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: Total() function in a form???

    Most use tablesum() or dbsum() anywhere such a total is needed (outside a report).
    There can be only one.

  7. #7
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Re: Total() function in a form???

    Thank you Stan, Once again you saved my life...

  8. #8
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Re: Total() function in a form???

    Im currently using this:

    TABLESUM("courses.DBF", "ssn = '" + ssn + "' ", "Completed_hrs")

    Which is working perfectly for adding up all the hours a student has, however, is there a way I can make it not count the completed hours for a specified class such as class "ABL"? I have multiple classes that I would like it to ignore the count on, is there an expression for that or do I need to rethink my table structure?

  9. #9
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    662

    Default Re: Total() function in a form???

    TABLESUM("courses.DBF", "ssn = '" + ssn + "' " + ".AND. Class<>ABL", "Completed_hrs")

  10. #10
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Re: Total() function in a form???

    I entered the following

    TABLESUM("courses.DBF", "ssn = ' " + ssn + " ' " + ".AND. Course_Name<>ABL", "Completed_hrs")

    however I'm getting <invalid or incomplete expression>

  11. #11
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Re: Total() function in a form???

    Additionally, I'm trying to use/come up with a way to have it add all the hours a student has, while excluding certain classes and also making sure that it does not count duplicate classes (a class that a student has taken repeatedly).

    I really need to find something to sharpen my knowledge when it comes to writing expessions,

    Thanks in advance for all your help.

  12. #12
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Total() function in a form???

    Quote Originally Posted by David Boomer View Post
    TABLESUM("courses.DBF", "ssn = '" + ssn + "' " + ".AND. Class<>ABL", "Completed_hrs")
    looking at that - ABL should be within quotes

    TABLESUM("courses.DBF", "ssn = '" + ssn + "' " + ".AND. Class<>'ABL'".... should work

  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: Total() function in a form???

    Which can be compressed to

    TABLESUM("courses.DBF", "ssn = '" + ssn + "' .AND. Class<>'ABL'", "Completed_hrs")
    There can be only one.

  14. #14
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Re: Total() function in a form???

    So I typed this and all works fine:

    TABLESUM("courses.dbf", "ssn=' "+ssn+" ' .and. Course_Name<>'Mandatory Orientation' ", "Completed_hrs")

    but I need to exclude about four courses, so wrote the following:

    TABLESUM("courses.DBF", "(ssn = '" + ssn + "') .and. (Course_name<>'Mandatory Orientation' .or. Course_Name<>'Sponsored Mandatory Orientation' .or. Course_Name<>'Sponsored Mandatory Orientation (lcm)' .or. Course_Name<>'Sponsored Mandatory Orientation (lif)') ", "Completed_hrs")

    and this expression does not produce the result I would like, Im checking a student that only has the Course "Mandatory Orientation" and when I use the first expression above his hours come out to zero which is correct. But when I use the second expression above, his hours come out to 40 which is not what im looking for.

    What am I doing wrong?

  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: Total() function in a form???

    TABLESUM("courses.DBF", "ssn = '" + ssn + "' .and. Course_name<>'Mandatory Orientation' .and. Course_Name<>'Sponsored Mandatory Orientation' .and. Course_Name<>'Sponsored Mandatory Orientation (lcm)' .and. Course_Name<>'Sponsored Mandatory Orientation (lif)' ", "Completed_hrs")

    or

    TABLESUM("courses.DBF", "ssn = '" + ssn + "' .and. Course_name != 'Mandatory Orientation' .and. Course_Name != 'Sponsored Mandatory Orientation' .and. Course_Name != 'Sponsored Mandatory Orientation (lcm)' .and. Course_Name != 'Sponsored Mandatory Orientation (lif)' ", "Completed_hrs")

    or, guessing here

    TABLESUM("courses.DBF", "ssn = '" + ssn + "' .and. ('Mandatory' !$ Course_Name)", "Completed_hrs")
    Last edited by Stan Mathews; 03-27-2014 at 11:55 AM.
    There can be only one.

  16. #16
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Re: Total() function in a form???

    TABLESUM("courses.DBF", "ssn = '" + ssn + "' .and. ('Mandatory' !$ Course_Name)", "Completed_hrs")

    What does the !$ mean? how does that work?

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

    Default Re: Total() function in a form???

    Last edited by Tom Cone Jr; 03-27-2014 at 12:29 PM. Reason: fix goof

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

    Default Re: Total() function in a form???

    Actually, the substring exclusion operator, but that's just a technicality.
    There can be only one.

  19. #19
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Re: Total() function in a form???

    IS there a way to tell this not to count duplicate Course_name?

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

    Default Re: Total() function in a form???

    No.

    We've been down this road before.

    http://msgboard.alphasoftware.com/al...368#post668368

    You would need an expression like we developed earlier that returned the unique completed hours values but instead of line_count() you would use *TOTAL().

    Yes, that is in the documentation.
    There can be only one.

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

    Default Re: Total() function in a form???

    You might consider designing and running a summarize operation, then building a report on the result table.
    There can be only one.

  22. #22
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Re: Total() function in a form???

    Im sorry, I don't think I understood you completely, did you say that there is NOT a way to use the expression below and also have it ignore duplicate course_name when calculating the completed hours?

    TABLESUM("courses.DBF", "ssn = '" + ssn + "' .and. ('Mandatory' !$ Course_Name)", "Completed_hrs")


    Building a Summarize operation would be under the operation tab correct.
    Last edited by Gustavo1478; 03-27-2014 at 07:27 PM. Reason: saw post #21 after I posted #22

  23. #23
    Member
    Real Name
    Ivan Lozano
    Join Date
    Apr 2013
    Location
    Azusa, CA. LA County
    Posts
    400

    Default Re: Total() function in a form???

    Would you know where I can find an example of the summarize operation? This is a new concept for me.

  24. #24
    "Certified" Alphaholic
    Real Name
    John Koh
    Join Date
    Jan 2004
    Location
    Maryland, USA
    Posts
    1,084

    Default Re: Total() function in a form???


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

    Default Re: Total() function in a form???

    Quote Originally Posted by Gustavo1478 View Post
    Would you know where I can find an example of the summarize operation? This is a new concept for me.
    A recurring theme, it seems.

    Have you discovered the Alpha documentation, specifically the User Guide?

    http://wiki.alphasoftware.com/Alpha+...er+Guide+Title
    There can be only one.

Similar Threads

  1. Change form calc from function Total to dbsum and dbcount
    By George Corder in forum Alpha Five Version 10 - Desktop Applications
    Replies: 9
    Last Post: 01-14-2013, 05:12 PM
  2. Total fails to total on the form
    By Chris A in forum Alpha Five Version 5
    Replies: 1
    Last Post: 07-28-2004, 01:07 PM
  3. Running total function
    By Dr Alok Modi MD in forum Alpha Five Version 5
    Replies: 7
    Last Post: 05-01-2004, 01:04 PM
  4. Total () Function
    By Martin Konieczny in forum Alpha Five Version 5
    Replies: 3
    Last Post: 07-28-2003, 08:06 AM
  5. TOTAL function
    By David Patterson in forum Alpha Five Version 4
    Replies: 2
    Last Post: 06-20-2001, 09:09 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
  •