1. NOTICE: If you are using Fabrik and update to Joomla 3.10, you will need to update to Fabrik 3.10. And, if you are using Fabrik, do not upgrade to Joomla 4, we do not have a supported version ready for release. More information on a release date coming soon. Also, please note that Fabrik 3.10 will not install on any Joomla sites less than 3.8.
    Dismiss Notice

Using the list SUM value in another list

Discussion in 'Community' started by 7webcreator, Jun 26, 2022.

  1. 7webcreator

    7webcreator Member

    Level: Community
    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?
     
  2. troester

    troester Well-Known Member Staff Member

    Level: Community
    A calc element or a php plugin running a SQL SELECT or a MySQL view with a Fabrik list linked to it.
     
  3. 7webcreator

    7webcreator Member

    Level: Community
    :eek: 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;
     
    Last edited: Jun 27, 2022
  4. troester

    troester Well-Known Member Staff Member

    Level: Community
    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}"'));
     
  5. 7webcreator

    7webcreator Member

    Level: Community
    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 ')'
     
  6. troester

    troester Well-Known Member Staff Member

    Level: Community
    {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}"' );
     
  7. 7webcreator

    7webcreator Member

    Level: Community
    Thank you. Yes, both the dbjoins are set up in the recommended way
    .
    upload_2022-6-28_13-36-2.png
    upload_2022-6-28_13-36-39.png

    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 />"
     
  8. troester

    troester Well-Known Member Staff Member

    Level: Community
    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...
     
  9. 7webcreator

    7webcreator Member

    Level: Community
    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.:):):)
     

Share This Page