>hello,
> I need to get the total on 3 fields for multiple records for a buffered table. What is the best way to do it without issuing a table update command ??? Thank you.
>sherry
Sherry,
If there is no problem that you can temporarily move the record pointer during calculation you can use the commands like calculate, sum and return back to original recno().
If you're using row buffering than moving record pointer would just not only do an implicit tableupdate but cause to triggers fire as well. Also recno() you stored would change from a negative number to its new recno(). You could think it would become reccount() if it's single user.
OTOH if the table is large enough, user should wait slightly for calculation to finish.
Instead I use a different logic. Typically these type of calculations are done in a child that's table buffered, and table is updated (or reverted) before parent changes (one to many forms) :
-When parent changes do an SQL into array (At this moment there is no buffered data). You probably would show it in a textbox control, say myTextbox.
select sum(f1), sum(f2), sum(f3) ;
from mytable ;
where myTable.Linkkey = parentPKValue ;
into array SQLArray
thisform.myTextBox1.value = iif(type("SQLArray[1]") = "N",SQLArray[1],0)
thisform.myTextBox2.value = iif(type("SQLArray[1]") = "N",SQLArray[2],0)
thisform.myTextBox3.value = iif(type("SQLArray[1]") = "N",SQLArray[3],0)-Result is effected only if field values of those 3 fields change.
-In gotfocus of textboxes save the current value to a property - say "tag" of textbox :(Tag is not used by VFP and is a ready storage place)
this.tag = padl(iif(isnull(this.value),0,this.value),20)
-In lostfocus, subtract the stored value from sum and add new value. ie for myTextbox1 :
thisform.myTextBox1.value = ;
thisform.myTextBox1.value + ;
(iif(isnull(this.value),0,this.value) - val(this.tag))
That's it. Isnull checking is not a must depending on it's allowed to have them or not. So for generosity I include it. This approach has noticable advantage if tables are large enough. Otherwise VFP does native calculations fast enough. Another advantage, you could generalize this w/o a fear of moving rec pointer and thus causing triggers to fire (ie: your table valid code would fire otherwise if nothing else).
Cetin
