Alpha Video Training
Results 1 to 15 of 15

Thread: Getting a Quick Sum of a Column

  1. #1
    Member
    Real Name
    Keith Weatherhead
    Join Date
    Nov 2004
    Location
    Braceville, IL
    Posts
    427

    Default Getting a Quick Sum of a Column

    Greetings,

    Is there a way, in A5V7, to get a sum of a column, without creating a report or writing Xbasic to read every record in the file?

    I have a transaction file that has several hundred rows and I have a value of those transactions based on a monetary filed in the data.

    I promise to wash my mouth afterwards, in MS-Access you can do a Quick Query and sum the column and have a total in seconds with designing a report per-sae or writing code... can you do the equivalent in A5?

    Regards,
    Keith
    Keith Weatherhead
    Discus Data, Ltd
    kweatherhead@gmail.com

  2. #2
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Getting a Quick Sum of a Column

    Keith,

    Not sure if this will suffice but on the toolbar next to the excel button is a yellow button for field statistics. Place cursor in the field you are interested in and press the button. The item second from the bottom is total.
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  3. #3
    Member
    Real Name
    Keith Weatherhead
    Join Date
    Nov 2004
    Location
    Braceville, IL
    Posts
    427

    Default Re: Getting a Quick Sum of a Column

    Quote Originally Posted by Tim Kiebert View Post
    Keith,

    Not sure if this will suffice but on the toolbar next to the excel button is a yellow button for field statistics. Place cursor in the field you are interested in and press the button. The item second from the bottom is total.
    Hi Tim,

    THANK-YOU !!

    How in the world that I missed that is unbelievable, the Total and Average are just what I needed! Now, I need to look into what the Expression Tab will allow, this WILL make my life easier !!

    Regards,
    Keith
    Keith Weatherhead
    Discus Data, Ltd
    kweatherhead@gmail.com

  4. #4
    "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 Quick Sum of a Column

    Quote Originally Posted by KeithW View Post
    Hi Tim,

    THANK-YOU !!

    How in the world that I missed that is unbelievable, the Total and Average are just what I needed! Now, I need to look into what the Expression Tab will allow, this WILL make my life easier !!

    Regards,
    Keith
    Field statistics.

  5. #5
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: Getting a Quick Sum of a Column

    Keith, Happy to help. After a bit of a play I found that the result is based on the active record set. So you can filter the records your favourite way and the resulting records will be used in the statistics. This applies to embeded browses as well. Check out this page in the help on the related function
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  6. #6
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Getting a Quick Sum of a Column

    Wonder what that related function might be... Would like to be able to recreate the statistics button on an xdialog browse. But just copying the code from the toolbar doesn't do it - returns an argument incorrect data type error on line 43 using either addin.run("field_statistics") or a5_field_statistics()
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  7. #7
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Getting a Quick Sum of a Column

    Here's a few ideas I've played with.

    Add this table to a workspace and look at the code in the browse.

    testgrptotal.zip
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  8. #8
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Getting a Quick Sum of a Column

    That's pretty cool, but that is a button event that is part of the browse. I just want a stat button to work like it does on the browse toolbar but as part of the xdialog. Alpha auto adds in the navigation buttons, save and refresh in the dlg code when it gives you the xml definition. So it seems a stat button ought be possible too.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

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

    Default Re: Getting a Quick Sum of a Column

    Robin,

    I just want a stat button to work like it does on the browse toolbar but as part of the xdialog.
    You can add the buttons as like the Navigation Buttons and add the events with your code.

  10. #10
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Getting a Quick Sum of a Column

    Hi John,
    As I said in post in #6, I tried that but the code errs. I tried the code from both of the system form and browse toolbars.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  11. #11
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Getting a Quick Sum of a Column

    Quote Originally Posted by MoGrace View Post
    That's pretty cool, but that is a button event that is part of the browse. I just want a stat button to work like it does on the browse toolbar but as part of the xdialog. Alpha auto adds in the navigation buttons, save and refresh in the dlg code when it gives you the xml definition. So it seems a stat button ought be possible too.
    This wasn't meant to be the final answer, but some options to get to it.

    Can you post what you are starting with and info on how you want to improve it?
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  12. #12
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Getting a Quick Sum of a Column

    I found another function that might do it COMPUTE_FIELD_STATISTICS - I will try that then let you know how it went.

    Edit: In using the script recorder on the xd to see what the sort buttons returned, I get this instead of the pointer to the browse object in the script:

    generatedbrowse.quick_sort()

    Well the Wiki is wrong. This function above returns a dot variable and is thus a pointer. How do I get the pointer results from the IW into a list I can display in a msgbox()? I am thinking I need an array? Where is Stan when you need him? But I am now off this thread since I haven't found a way to get a total SIMPLY which is what the OP was about.

    ...More Info from the IW using a numeric field - and it works with the current query:

    Code:
    brw1 = topparent.this
    v = brw1.GetActiveObjParent().active_drilldown()
    ?typeof(v)
    = "C"
    ?v
    = ":ApptList:HH_CNT"
    
    stats = compute_field_statistics(v)
    ?typeof(stats)
    = "P"
    
    ?stats
    = Average = 0.363802559414991
    count = 547
    First = 5
    Last = 1
    Maximum = 6
    Minimum = 0
    Standard = 0.921610634766173
    Total = 199
    Variance = 0.849366162114108
    now with a regular browse open I can use
    a5_field_statistics()
    and it brings up the statistics dialog - from the IW. Notice there is not any arguments for this function or a pointer needed, so when the toolbar uses this function it gets the info it needs on its own just like the other toolbar buttons do. But of course not if the browse is embedded in an xdialog. I noticed the script recorder only showed results from some of the buttons Alpha added.
    Last edited by MoGrace; 01-12-2018 at 04:16 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  13. #13
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Getting a Quick Sum of a Column

    I am getting a bit closer to creating a function:

    Code:
    'Note:  brw1 is the pointer I used in the xdialog for the xml browse definition so
    'defining it here is just for testing purposes
    
    brw1 = topparent.this
    objname = brw1.GetActiveObjParent().active_drilldown()
    t = brw1.Table_Get()
    tname = t.name_get()
    fld = word(objname,-1,":")
    fptr = a5_field_info(tname,fld)
    stats = compute_field_statistics(fld)
    x=properties_enum(stats,"R,O=p: v")
    
    msgbox("Field Stats",ut(fld)+" Statistics:"+crlf(2)+x,0)
    If you open a standalone browse and set a query, you can copy this to the IW and run it. If a numeric field is selected then
    stats.total will return the value.
    Last edited by MoGrace; 01-12-2018 at 04:15 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  14. #14
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Getting a Quick Sum of a Column

    I have since added another embedded xml browse in my XD so I changed the statistics button to read the field stats from either one:

    Code:
    brw = topparent.this
    objname = brw.GetActiveObjParent().active_drilldown()
    t = brw.Table_Get()
    tname = t.name_get()
    fld = word(objname,-1,":")
    fptr = a5_field_info(tname,fld)
    stats = compute_field_statistics(fld)
    x=properties_enum(stats,"R,O=p: v")
    
    msgbox("Field Stats",ut(fld)+" Statistics:"+crlf(2)+x,0)
    Note that now 'topparent.this' returns whichever browse is active.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  15. #15
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,699

    Default Re: Getting a Quick Sum of a Column

    Quote Originally Posted by MoGrace View Post
    Wonder what that related function might be... Would like to be able to recreate the statistics button on an xdialog browse. But just copying the code from the toolbar doesn't do it - returns an argument incorrect data type error on line 43 using either addin.run("field_statistics") or a5_field_statistics()
    Finally got an error message that reveals something - from the IW without setting the session I got:

    a5_field_statistics()

    ERROR: Script:field_statistics line:43
    topparent.child_enum("r,e=this.field_get().fullname_get()"): Argument is incorrect data type

    It seems that the valid pointer for 'topparent' is what that function expects to find.

    Edit: And that valid pointer for an xml browse in an Xdialog is 'generatedbrowse.this'
    so here is my event code for the Statistics button on my XD that actually works:

    Code:
    If a_dlg_button = "b_Stats" then 	
    	if brw1.active() <> "" then	
    		fld = brw1.active()
    		brw = generatedbrowse.this
    	else
    		msgbox("","Please select a column",0)
    		end
    	end if		
    	
    	'get info for the msgbox		
    	t = brw.Table_Get()
    	tname = t.name_get()
    	'get the fully qualified object name	
    	objname = brw.GetActiveObjParent().active_drilldown()		
    	stats = compute_field_statistics(objname)
    	x=properties_enum(stats,"R,O=p: v")		
    	delete stats
    	msgbox("Field Stats",ut(tname)-"->"-ut(fld)+" Statistics:"+crlf(2)+x,0)
    end if
    I couldn't however, get a valid pointer to the 2nd browse in the XD, but didn't really need it anyway. It seems since both browses have an active object, but only the 1st one defined gets used.
    Note that a numeric field gets a Total entry:
    Stat_Btn.jpg
    Last edited by MoGrace; 04-15-2018 at 11:50 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

Similar Threads

  1. grid (brows)-> column -> column title
    By alimrb in forum Web Application Server v6
    Replies: 2
    Last Post: 01-06-2005, 05:54 AM
  2. sum of records
    By dparkins in forum Alpha Five Version 5
    Replies: 4
    Last Post: 09-20-2004, 06:18 AM
  3. Quick Report - Column Width
    By Cyrus in forum Alpha Five Version 5
    Replies: 4
    Last Post: 09-28-2003, 01:37 PM
  4. Quick - Quick Filter Question
    By forskare in forum Alpha Five Version 4
    Replies: 2
    Last Post: 05-08-2001, 10:08 AM
  5. Table Sum Functions
    By robert bannerman in forum Alpha Five Version 4
    Replies: 5
    Last Post: 03-27-2001, 10:00 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
  •