Using the list SUM value in another list

[7webcreator]

Hello,

Is it possible to collect the SUM value found at the bottom of the list with a database join?

I have a list displaying a SUM grouped by an element of the same list. I would like to have this result reported in another list.

Any idea how I can do that?

 

[troester]

A calc element or a php plugin running a SQL SELECT or a MySQL view with a Fabrik list linked to it.

 

[7webcreator]

That is far too complicated for my knowledge of PHP! I will not know where to begin

I have tried the following with a calc element, but I got a syntax error

$mydb = JFactory::getDbo();
$query = $mydb->getQuery(true);
$query
->select('SUM(quota_docenti)')
->from($mydb->quoteName('lista_corsi'))
->where($mydb->quoteName('name').' = \'{j25_user___name_raw}\''));
$mydb->setQuery($query);
$sum_total = $mydb->loadResult();
return $sum_total;

 

[troester]

Not so bad. Try
->where($mydb->quoteName('name').' = "{j25_user___name_raw}"'));

Is j25_user___name_raw an element of your form (i.e. your list is linked to the Joomla user table?)

If you have an other list/table with e.g. a user element (set to display the name) I think it must be something like
->where($mydb->quoteName('name').' = "{your-table___your-user-element}"'));

 

[7webcreator]

Thank you for directing me to a better solution.

"Is j25_user___name_raw an element of your form (i.e. your list is linked to the Joomla user table?)": the element is database join to the j25_user and named "join_docenti" in the list having the data I need to collect, and "docente_name" in the other list. They are both join elements to the j25_user list.
The calc element where the total should display is named "saldo_scuola."

So I tried the following:

$mydb = JFactory::getDbo();
$query = $mydb->getQuery(true);
$query
->select('SUM(quota_docenti)')
->from($mydb->quoteName('lista_corsi'))
->where($mydb->quoteName('join_docenti').' = "{lista_corsi___join_docenti}"'));
$mydb->setQuery($query);
$sum_total = $mydb->loadResult();
return $sum_total;

and got a syntax error
0 syntax error, unexpected ')'

 

[troester]

{lista_corsi___join_docenti}"')); must be only one )

If I get you description right (and both dbjoins are set up in the recommended way with value=id[recommended], i.e. in list_corsi.join_docenti is storing the userID, not the name) it should be
->where($mydb->quoteName('join_docenti').' = "{your-list-name-of-the-form-you are-in___docente_name_raw}"' );

 

[7webcreator]

Thank you. Yes, both the dbjoins are set up in the recommended way
.



I have tried the following:

$mydb = JFactory::getDbo();
$query = $mydb->getQuery(true);
$query
->select('SUM(quota_docenti)')
->from($mydb->quoteName('lista_corsi'))
->where($mydb->quoteName('join_docenti').' = "{dettagli_degli_docenti___docente_name_raw}"');
$mydb->setQuery($query);
$sum_total = $mydb->loadResult();
return $sum_total;

and got now this error message: "Fabrik has generated an incorrect query for the list Stato del conto: <br />"

 

[troester]

Use the debug tools:

Enable Fabrikdebug in Fabrik Options, then append &fabrikdebug=1 to your list URL. You should get a more detailed error message.
And/or
Joomla system debug on
and/or
echo $query;exit; to see what you get as query

No idea where list Stato del conto is coming from...

 

[7webcreator]

The result is 1054 Unknown column 'quota_docenti' in 'field list'.

Thank you so much. It works perfectly now that I inform the field with the correct name.