What am I doing wrong with my calc fields 2

[yomyom]

I have created 3 calcfields in a table. They are credit, debit and balance.
The on calcfield event of the table is to set the amount in the record to either debit and credit based on the entry in the type field.
The balance is then incremented based on the credit value and reduced based on the debit value.
This worked fine on an un-filtered dataset.
However, when tried on a filtered dataset, it gives strange results. Basically, it appears to be calculating the balance field before applying the filter to the table that returns the filtered dastaset.
How do I overcome this, i.e. can I controll the filtering of the table before oncalcfields calculates the balance.
Below is the code i'm using :
Procedure Tform2.TableCalcFields(Dataset: TDataset);
begin
//place amount in debit or credit field
if table2Type.text = 'Debit' then
table2Debit.text := table2Amount.text;
else
table2Credit.text := table3Amount.text;
//calculate the balance field
table2Balance.asInteger := aBal +
table2Credit.asInteger - table2Debit.asInteger;
aBal := table2Balance.asInteger;
end;

//aBal is declared as a public variable and assigned a value in the before open event of table2
procedure TLedger.Table2BeforeOpen(DataSet: TDataSet);
begin
aBal := 0;
end;
I've ommited the filtering procedure.
Very long. I'm sorry. Please help.
yomyom.

 

[towerbase]

I don't think that you should be updating a global variable (such as aBal) in your OnCalcFields event.

I suspect that Delphi calls the OnCalcFields event before the filtering because you may wish to filter on the basis of a calculated field.

Andrew

 

[bbegley]

Can you find a way to set the appropriate filter inside the oncalc event, or does setting the filter call oncalc again?

Do you need this to be a calc field? Whenever we deal with any complex calculation, we always do it in real field values and/or in object structure. We have run into trouble using the calc fields for anything more than concatenating strings or doing math that nothing else hinges on. "The difference between practice and theory is that in theory, there is no difference between practice and theory" - Somebody's tag line I stole

 

[yomyom]

hi,
How would I go about using an object structure?
would i create a class of tDataset? In fact I can't think ...please point me in the right direction....
yomyom

 

[bbegley]

We are working on a complex timekeeping system for police officers, and we created a class for a time calculation, and created subclasses for elapsed time values and for time stamp values, and then implemented classes that equate to the field values for the database. So that when we want to get an elapsed time value we say:
O_Time.TotalElapsedRegularTime.AsDateTime := ...
or
s := O_Time.ShiftStart.AsMilitaryTime; ...
We put the various calculations to provide us that value inside the class. That way, we don't trigger another oncalc event when we want to change a real field value in that table, and we don't need to call Table.edit/Table.post every time we want to edit a value. As an added benefit in our case, it enables us to encapsulate all of the checks for valid dates and times, and allows us to give the class properties that enable us to call the value as a date, time, datetime, hour, minute, military time, etc...

"The difference between practice and theory is that in theory, there is no difference between practice and theory" - Somebody's tag line I stole

 

[yomyom]

Thanks, I'll try something similar. perhaps a class of type tDataset with additional methods for achieving my calculations - java style - and i'll report back.

 

[yomyom]

Solved!
By using a TQuery component.
The form wizard created a TTquery master detail form. TQuery2 contains the user defined fields (calc fields)'debit', 'credit' and 'balance'.
The onCalcFields of The tQuery contains:
if Query2Type.text := 'Debit' then
Query2Debit.text := Query2Amount.text
else
Query2Credit.text := Query2Amount.txt;
Query2Balance.asInteger := aBal + Query2Credit.asInteger - Query2.Debit.asInteger;
aBal := Query2Balance.asInteger;

The beforeOpen method of Query2 contains:
aBal := 0;

note aBal is a public (global) variable of type integer.
The result is an accounting style listing for each of the members of Query2 which are linked to the field 1 of Query1 showing Debit column, credit column and Balance column with correct values.

The above works because TQuery returns a dataset that is already a subset of the entire table based on account number of table 1.(Therefore there is no further filtering required!). Further calculations on the calc field of Query2(a stable dataset) are therefore posible.
yomi.