# Thread: Getting a Quick Sum of a Column

1. ## 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

2. ## 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.

3. ## Re: Getting a Quick Sum of a Column

Originally Posted by Tim Kiebert
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

4. ## Re: Getting a Quick Sum of a Column

Originally Posted by KeithW
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. ## 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

6. ## 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()

7. ## 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

8. ## 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.

9. ## 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.

10. ## 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.

11. ## Re: Getting a Quick Sum of a Column

Originally Posted by MoGrace
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?

12. ## 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.

13. ## 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.

14. ## 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.

15. ## Re: Getting a Quick Sum of a Column

Originally Posted by MoGrace
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
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

#### Posting Permissions

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