xbrowse Footer sum of all numeric fileds in a row

[UD previous posts]

is it possible to show the sum of all fields in one row of a xbrowse

tx Uwe D aus E

[James Bott]

How about something like:

oCol:bStrData := { || str(field1 + field2 + field3) }

[UD previous posts]

Excuse my error

sum of one column

[James Bott]

Uwe,

>sum of one column

This is more difficult. You have to read through the entire database and sum all the columns BEFORE you display the browse. Summing the entire database might be feasible with small databases (or subsets), but it would create an unaccepatble delay with larger ones.

If any record is then edited, you have to change the sum according to the changes made to the record.

If it is a multi-user system, with multi-user access to the browsed database, the totals could become out of date at any time if another user changed a value.

I have done something like this with invoices. The invoice header record is locked, then the detail records are browsed and the totals are updated if the user makes any changes (add or edit). No other user can make changes at the same time so this is not an issue.

I know that TSBrowse supports footers, but I don't know about TXBrowse. With my invoice system I just put the totals below the browse on the dialog.

James

[Otto]

Uwe,

I have the same problem with my xBrowse with „meter“.

http://forums.fivetechsoft.com/viewtopic.php?t=11473

I have to browse thought the whole database to get the maximum value.
Otherwise you can’t show a percentage.
Maybe there is a function to show the max- value of a database field?
Regards,
Otto

[James Bott]

Otto,

>I have to browse thought the whole database to get the maximum value.

To the find the largest value in a field, you can index on that field then do a GO BOTTOM.

James

[nageswaragunupudi]

>
Maybe there is a function to show the max- value of a database field?
>
You can use sql statment to find maximum value.
example:
SELECT MAX(SALARY) FROM EMPLOYEE WHERE AGE > 30
( or some appropriate statement like that).
You will get the result much faster if the SALARY column and AGE column are indexed in the above example. We can achieve more by combining the normal familar ISAM code blended with SQL code where appropriate.

Interestingly, we can use SQL on normal DBF tables also, if we open the connection to the folder in ADO. For connecting to normal DBF tables through ADO, we can use appropriate connection string from http://www.connectionstrings.com
-------------------------------
>
oCol:bStrData := { || str(field1 + field2 + field3) }
>
it is not any more necessary in xbrowse to covert values as character values. Use of bStrData is deprecated according to documentation in whatsnew.

oCol:bEditValue := field1 + field2 + field3.

XBrowse automatically constructs the bStrData block, using oCol:cEditPicture, if assigned. In case of ADO,DBF, etc, XBrowse constructs the picture automatically based on the column characteristics.

------------------
>
I know that TSBrowse supports footers, but I don't know about TXBrowse.
>
TXBrowse supports footers.

oCol:bFooter := { || nTotal }

It is not necessary to convert nTotal into a character value. XBrowse uses the oCol:cEditPicture to format the footer.

If during the course of the browse, if nTotal changes, we do not have to reassign the revised total. oCol:RefreshFooter will do. Or oBrw:RefreshFooters will refesh all footers without refreshing the rest of the browse.

I find the xbrowse footers are extremely convinient to use.

----------------------

About showing totals of the columns in the footers. I do ths in many cases, because users want this.

Obviously, in single user mode it is easy. But here i like to say how convinient it is to handle footers in xbrowse.

XBrowse automatically refreshes the column's footer also, after editing a value in the column, if oCol:lTotal is set to .t.
We can write in the oCol:bOnPostEdit block to change the value of nTotal after successful edit. Then without writing any extra code, XBrowse refreshes the column value and also the footer with new nTotal.

I normally maintain the footer values in an array ( keep updating them on edits ) and let the xbrowse do the appropriate refeshing job.

Now as Mr James points out, the problem is quite complex in multiuser environment. We develop our own techniques depending on the speicifics of the program module, data and environment when and how to sense other user modifications and whether to recalculate totals on the client only or to recompute totals of the table on the server. ( Now SQL does great job when needed : Exampe SELECT SUM(SALARY),SUM(COMMISSION) FROM MYTABLE WEHRE MYCONDITION ). No unique way, but what we do depends on our program and database design.

But nonetheless, our coding to refresh footers in xbrowse is the simplest.

[Otto]

Hello James, hello NagesWaraRao,

thank you very much for your help.

As I never tried using a dbf through a SQL statement I would like to ask about performance and speed?

Best regards,
Otto

[James Bott]

NageswaraRao,

I am not clear on how xBrowse updates the footer totals. Does it scan through the entire database and add up all the values every time you refresh the footers?

I can see how it might update the footer for one column if you are editing a column value within xBrowse (spreadsheet style) because that is internal, but normally my edits are in a dialog box called from the browse. So, it would seem to be much more difficult for the browse to know to change all the totals based on changes to the underlying record. Does it do that?

James

[nageswaragunupudi]

Mr Otto

>
As I never tried using a dbf through a SQL statement I would like to ask about performance and speed?
>

There are definite speed advantages when using server based databases, like Advantage and other RDMSs. The reason is the entire iteration through the table is performed on the server and only the result is read from the server.

In case of using ADO for normal DBFs, there will not be much speed advantage, but it is not slower. Advantages include whatver speed advantage and simpler code.

[nageswaragunupudi]

Mr James

>
I am not clear on how xBrowse updates the footer totals. Does it scan through the entire database and add up all the values every time you refresh the footers?
>

I am sorry if I my post gave such an impression. No it does not. As usual it is the programmer's reesponsibility to recompute the totals after each edit.

Normally after inline edit of a cell, we need to write code for (1) save the data, (2) alter the total, (3) assign the formatted value to cfooter (4) redraw the footer and ( 5 ) if the column is indexed, refersh the entire browse or else refresh the current row only.

What xbrowse helps us is by avoiding the 3rd, 4th and 5th steps.

PostEdit method refreshes the browse if the column is sorted ( using inbuilt sort facilty ) or refreshes the row only otherwise. We do not have to write code to decide whether full browse is to be refreshed or only edited row is to be refreshed.

If oCol:lTotal is true and oCol:bFooter is not empty the xbrowse automatically refreshes the footer also. We do not have to write code calling oCol:RefreshFooter.

Since it is possible to refresh footer of a single column alone, it is very fast.

When using the dialog edit. naturally we need to write code to call oBrw:Refreshfooters also after changing the totals if necessary.

[James Bott]

NageswaraRao,

Thanks for clearing that up.

James

[Antonio Linares]

With XBrowse 8.06, coding for recalculation of totals and refreshing footers is made simpler:

Example:

Code: Select all  Expand view

#include 'fivewin.ch'
#include 'xbrowse.ch'

//----------------------------------------------------------------------------//

function main()

   local oDlg, oBrw, oFont
   local nTotal := 0

   USE CUSTOMER ALIAS CUST

   DBEVAL( { || nTotal += CUST->SALARY } )
   GO TOP

   DEFINE FONT oFont NAME 'TAHOMA' SIZE 0,-12
   DEFINE DIALOG oDlg SIZE 600,400 PIXEL ;
      TITLE 'Easy totals with XBrowse';
      FONT oFont

   @ 10,10 XBROWSE oBrw ;
      COLUMNS "First", "Last", "Salary" ;
      SIZE 280,180 PIXEL ;
      OF oDlg ;
      ALIAS "CUST" ;
      FASTEDIT FOOTERS LINES CELL

   WITH OBJECT oBrw:oCol( "Salary" )
      :nTotal     := nTotal
      :nEditType  := EDIT_GET
   END

/*
   // To use add column command for the same
   // facility

   ADD TO oBrw DATA FieldWBlock( "SALARY", SELECT( "CUST" ) ) '
      HEADER "Salary" ;
      PICTURE "99,999,999.99" ;
      EDITABLE ;
      TOTAL nTotal
*/

   oBrw:CreateFromCode()

   ACTIVATE DIALOG oDlg CENTERED

   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//


Inline edit of salary column automatically recalculates and shows the revised total. This works perfectly for single user environments or where there is no chance of other users modifying the same set of data. Where such modifications are expected, appropriate code can be added to change the variable nTotal and use oCol:RefreshFooter()