how to use a calculated field

[shiraz]

Hi there,

Background:

I have a table called courses and a table called students, and a third join table. I am track course payments by the students in this join table.

In the join table are the requisite two primary keys from the source tables, as well as these fields:

- cost
- amount paid
- amount owing


What I want:

amount owing = cost - amount paying


Two approaches attempted:

1) Added a delegate class trigger for the join table to populate the amount owing field.

Problem #1: Produces an output error, I suspect because delegate classes aren't intended for join tables.
Problem #2: The record object is populated with the "cost" and "amount paid" fields, which is good, but not with StudentID field, so I do not know what to specify in the where clause when updating the record with Mysql.

Both problems would need to be solved.


2) Tried specifying a custom __sql__ query in fields.ini for the join table to do a true mysql calculated field (whereby the field does not exist in the database but is constructed dynamically based on other fields).

Problem: custom __sql__ queries are not allowed in fields.ini.


Does anyone have any suggestions on either approach, or perhaps another?


Many thanks,

Shiraz

[shannah]

Hi Shiraz,

I think that the update to 0.5.4 might improve the stability of the triggers you were trying to write. For what you are trying to do, I think a calculated field is much better than a trigger.

Dataface does allow you to define calculated fields in the delegate class, but they won't automatically appear in the record lists etc.. The will be available for use in your templates via $record->getValue('calcfield') for example.

e.g. Inside your delegate class, define a method as follows:

Code: Select all

// Define a calculated field
function field__FullName(&$record){
    return $record->strval('FirstName').' '.$record->strval('LastName');
}


Then in your php or smarty page you could do something like:

Code: Select all

$record =& df_get_record('Profiles', array('ProfileID'=>23));
echo "Full name: ".$record->val('FullName');


You could have done anything inside the feild_FullName() method including an sql query to return something.

A down-side of this approach, as I mentioned above, is that these calculated fields don't automatically appear in list view. Although, perhaps that would be a good feature to add in future versions.

On the side of using a trigger, if you do an afterInsert() trigger, the Primary key should have been populated in the record that your trigger receives as a parameter (try again in this new release -0.5.4) which should allow you to do the calculation and update the record.

Hope this helps.

-Steve