Calculated Fields [SOLVED]

Archived from the Xataface Users forum.

cantlep — Tue Mar 16, 2010 11:08 am

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 — Tue Mar 16, 2010 12:31 pm

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 — Tue Mar 16, 2010 4:32 pm

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 — Tue Mar 16, 2010 4:42 pm

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

---

cantlep — Tue Mar 16, 2010 5:19 pm

Fantastic All sorted using money_format()

Cheers

Paul