Calculated Fields [SOLVED]

[cantlep]

Hi All,

I know calculated fields have been mentioned in the past on the forum but a lot of them relate to old versions and I'm hoping some more functionality exists now-a-days. I'm running the latest BETA version.

Here's what I'm trying to do.

I have a table called "BillData". Within it are various fields. The ones of concern are BillTotalExVAT and BillTotalIncVAT. (I think you can see where I'm going with this). I want to manually populate BillTotalExVAT but I want BillTotalIncVAT to update accordingly.

I've tried it like this (Perhaps I'm being a bit too simple) but it doesn't work via Dataface (with mysql Cmnd line, it works fine).

in tables/BillData/fields.ini I have this

Code: Select all

[BillTotalIncVAT]
widget:label = "Bill Total (Inc VAT)"
visibility:browse = hidden
visibility:find = hidden
vocabulary = BillTotalIncVATCalc


in tables/BillData/valuelist.ini I have this

Code: Select all

[BillTotalIncVATCalc]
__sql__="SELECT BillTotalExVAT*0.175+BillTotalExVAT FROM BillData"


Sadly, the column titled BillTotalIncVAT is just left blank

I'm probably going about this the wrong way. Can anyone assist at all?

Thanks

Paul

[shannah]

Here's how I would do this:

Suppose I have a field (subtotal) and I want to add a calculated field (totalAfterTax).

Step 1: Add a dummy calculated grafted field using the __sql__ directive in the fields.ini file (not to be confused with the __sql__ directive in the valuelists.ini files).

e.g.

Code: Select all

__sql__ = "select c.*, c.subtotal as totalAfterTax from items c"


Step 2: Use the xxx__display() delegate method to override the display of the totalAfterTax field with your calculation:

Code: Select all

function totalAfterTax__display(&$record){
    return $record->val('subtotal')*1.07;
}


The reason I don't do the calculation in the __sql__ part is because of deficiencies in the SQL parser that causes it not to handle inline arithmetic very happily... it's on the list.

[cantlep]

Hi Steve, Fantastic as usual. All works like a charm.

You've been pretty busy this eve haven't you! Well appreciated, I'm sure.

Is there anyway that I can restrict the output to 2 decimal places for this dummy field? (equiv of say decimal(10,2)) ?

Thanks again

Paul

[shannah]

Check out the money_format function.
http://ca2.php.net/money_format

[cantlep]

Fantastic All sorted using money_format()

Cheers

Paul