Xataface Forums

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

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.

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

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

Fantastic All sorted using money_format()

Cheers

Paul