Calculated Fields

QuestionsCalculated Fields
Gary Brett asked 5 years ago

Hi there, in my query I have 2 fields, 1 is 'Payment', the other '% Amount', id there a way to calculate this and display in table?

EG: Payment | % Amount | Total Paid
100 | 30 | £70

Calculation – Payment*%Amount/100 = 70

Thank you

9 Answers
Abu Ghufran answered 5 years ago

Please refer demos/appearance/calc-column.php

Step1: define virtual column on grid to show calculated data.
Step2: In on_data_display event handler, set it's value in loop.

You must have all source fields as part of your select query.

Gary Brett answered 5 years ago

Thanks Abu, I am trying to check the demo but it doesn't load, says 'Unable to resolve the servers DNS address'.

Not sure how to define the virtual column as it does not exist in table?

Thank you

Gary Brett answered 5 years ago

Hi Abu, I tried this but it returns 0 in the virtual column?

// virtual column
$col = array();
$col["title"] = "Paid";
$col["name"] = "col2-virtual";
$col["width"] = "200";
$col["editable"] = false;
$col["hidden"] = false;
$col["default"] = "{col2}";
$col["on_data_display"] = array("do_maths","");
function do_maths($data){
return $data["PropertyValue"] / $col["Cost"] *100;}
$cols[] = $col;

If I simplify it to simply deduct one from the other it returns the PropertyValue field only?

Thank you

Abu Ghufran answered 5 years ago

Perhaps, a typo.
$col should be $data inside function.
on line: return $data["PropertyValue"] / $col["Cost"] *100;

Gary Brett answered 5 years ago

Hi Abu, sorry could you expand on that one if you have time? Removing the } doesn't work, neither does moving it to $col?

$col["on_data_display"] = array("do_maths","");
function do_maths($data){
return $data["PropertyValue"] / $col["Cost"] *100;
$cols[] = $col;

Sorry, I xan find any demo example as it doesnt load on your site?

Gary Brett answered 5 years ago

Sorry I mean to paste this instead, I tried this with no joy;

$col["on_data_display"] = array("do_maths","");
function do_maths($data){
return $col["PropertyValue"] / $col["Cost"] *100;}
$cols[] = $col;

Gary Brett answered 5 years ago

Hi Abu, please disregard above, now I sort of have it working but not using the % maths which is odd as this works in another site of mine [not datagrid]

//This returns correct result
return $data ["PropertyValue"] – $data["Cost"];}
100 – 40 = 60

//This still doesn't return correct result
return $data ["PropertyValue"] / $data["Cost"] *100;}
100/40*100 = 250, correct calc should be 40

Any ideas?

Abu Ghufran answered 5 years ago

Try using brackets.
return ( floatval($data["PropertyValue"]) / floatval($data["Cost"]) ) *100;

This can be checked independently on some php code and then used in grid.

Gary Brett answered 5 years ago

Hiya Abu, I think its the maths function that may be wrong, I just copied if from my other php apps where it works fine but I have it now & its working great..

If it helps anyone going forward the working function is;

return $data ["PropertyValue"] * $data["Cost"]/100;}

Thanks again for your help.

Gary

Your Answer

0 + 16 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?