Forums

How to display calculated field in table

ilansch 10 Aug, 2015
Hi,

I've been trying to add a column with the average grade of the students but I can't figure out how to do it.

The exams table contains 3 columns with the grades (calificacion1, calificacion2, calificacion3), but the tricky part is that not every student has all 3 grades (some grades are NULL), and if they are NULL then they shouldn't be counted.

What I have tried is:
1- At the model tab, I tried adding the fields I need plus the calculated one in the following way:

Fileds:
 `examen.cf_id`, `examen.estudiante`, `examen.fecha`,(SUM(ifnull(calificacion1,0))+SUM(ifnull(calificacion2,0))+SUM(ifnull(calificacion3,0)) ) / (COUNT(calificacion1)+Count(calificacion2)+COUNT(calificacion3)) as `examen.promedio`


But the query gets completely mishmashed, here is the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ) / (`examen`.`COUNT(calificacion1`)+Count(calificacion2)+COUNT(calific' at line 1 SQL=SELECT `examen`.`cf_id` AS `examen.cf_id`, `examen`.`estudiante` AS `examen.estudiante`, `examen`.`fecha` AS `examen.fecha`, `examen`.`(SUM(ifnull(calificacion1` AS `examen.(SUM(ifnull(calificacion1`, `trabajolibre`.`0))+SUM(ifnull(calificacion2` AS `trabajolibre.0))+SUM(ifnull(calificacion2`, `trabajolibre`.`0))+SUM(ifnull(calificacion3` AS `examen.0))+SUM(ifnull(calificacion3`, 0)) ) / (`examen`.`COUNT(calificacion1`)+Count(calificacion2)+COUNT(calificacion3)) as `examen.promedio` AS `examen.COUNT(calificacion1` FROM `jos_chronoforms_data_examen` AS `examen` LIMIT 30 OFFSET 180


2- Based in some posts I saw I tried also the following code (for simplicity I removed the id and the name of the student, until I find a working solution):

Fileds:
<?php return array( "((SUM(ifnull(calificacion1,0))+SUM(ifnull(calificacion2,0))+SUM(ifnull(calificacion3,0)) ) / (COUNT(calificacion1)+Count(calificacion2)+COUNT(calificacion3)))" => "examen.promedio");


But the error is similar:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ) / (`examen`.`COUNT(calificacion1`)+Count(calificacion2)+COUNT(calific' at line 1 SQL=SELECT `examen`.` "examen.promedio"); AS `examen.COUNT(calificacion1` FROM `jos_chronoforms_data_examen` AS `examen` LIMIT 30 OFFSET 180


3- So I tried by calculating the average by using the PHP Functions at the Front List Settings but also didn't work:

PHP Functions:
examen.promedio: {if( is_null($row["examen"]["calificacion1"])){$divisor = 0; $firstCalif = 0;} else {$divisor = 1; $firstCalif = $row["examen"]["calificacion1"];}; if ( is_null($row["examen"]["calificacion2"])) {$divisor = $divisor; $secondCalif = 0;} else {$divisor++; $secondCalif = $row["examen"]["calificacion2"] ;} if ( is_null($row["examen"]["calificacion1"])){ $divisor= $divisor; $thirdCalif = 0; } else { $divisor++; $thirdCalif = $row["examen"]["calificacion3"] ;} $promedio = ($firstCalif + $secondCalif + $thirdCalif) / $divisor; return $promedio};


but the average column is empty, I did enable error reporting in my .htaccess but I don't see any error messages.

Any suggestions would be greatly appreciated.

Thanks an advance
ilansch 11 Aug, 2015
Answer
1 Likes
Ok I found the solution.

I got it working using the third option (adding a PHP Function at the Front List Settings), I had placed everything inside curly brackets which is wrong, removing them solved the issue:

The working version I have at the PHP Functions is:
examen.promedio: if( is_null($row["examen"]["calificacion1"])){$divisor = 0; $firstCalif = 0;} else {$divisor = 1; $firstCalif = $row["examen"]["calificacion1"];}; if ( is_null($row["examen"]["calificacion2"])) {$divisor = $divisor; $secondCalif = 0;} else {$divisor++; $secondCalif = $row["examen"]["calificacion2"] ;} if ( is_null($row["examen"]["calificacion1"])){ $divisor= $divisor; $thirdCalif = 0; } else { $divisor++; $thirdCalif = $row["examen"]["calificacion3"] ;} $promedio = ($firstCalif + $secondCalif + $thirdCalif) / $divisor; return $promedio;


Now its working as expected.

Hope this helps others.
Ilan
jmahun 30 Aug, 2015
Am interested in your approach as I have something a little simpler, but I can't get it to work. Maybe you can shed some light.

In my table I have one field named "school" and another named "other_school". The form uses a radio box from which the user selects a school ("school"); one choice is "Other" if the school isn't listed. If that's selected, a text box is opened allowing the user to enter the name of a different school ("other_school").

In my CC connection in the Columns list of the Front List Settings, I have an "academic.school:School" column (academic is the model ID). If a user selected "Other" for school, that's shown in the list. Instead, I want to show what the user entered for "other_school".

Following your example, here's what I tried doing.
I created an "academic.institute:School" column - this would show either the user selected school or the school entered by the user.
In the PHP Functions box I entered the following code. It sets $institute to either the school selected or the one entered if "Other" was selected.

  academic.institute:
   if($row["academic"]["school"] == "Other") {
      $institute=$row["academic"]["other_school"];
   } else {
      $institute=$row["academic"]["school"];
   }
   return $institute;
  


Instead of working, I get a number of error statements:
Parse error: syntax error, unexpected end of file in... on line 2
Parse error: syntax error, unexpected 'else' (T_ELSE) in .. on Line 1.
Parse error: syntax error, unexpected '}' in ... on line 2

and the School column is blank in the listing.

If you can see what I'm doing wrong, I'd appreciate you're letting me know.
ilansch 31 Aug, 2015
Hi,

Your code seems fine to me, but I would suggest typing it into one line -you don't need to change the code it self just try removing all the new lines keeping it all as along string.

If that doesn't work, please post the exact error message, that would help identifying the error.

Regards
Ilan
jmahun 31 Aug, 2015
Thanks, that worked.
So much for entering code in a format that's easily readable.
I appreciate the help.
JMahun
ilansch 31 Aug, 2015
I'm guessing that the process of parsing multi line code on the field definitions could have greatly complicated the development of CC.

I'm happy to hear that that worked.

Regards
Ilan
This topic is locked and no more replies can be posted.