1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

add 3 rows element calc

Discussion in 'Community' started by albertotactik, Oct 17, 2019.

  1. albertotactik

    albertotactik Member

    Level: Community
    Good,
    I have created a “calc” element and it calculates me correctly, the values of each “id” or “row” that represents a year.
    Is it possible that when consulting or editing the form you can have the totals for each year? (each year is a row, of the same person and with ID managers)
    2017 + 2018 + 2019
    Given that they are in the same table ...
    In short, I need to add several rows with different IDs, which belong to the same person.
    I hope I have explained ...
    thanks!
     

    Attached Files:

  2. albertotactik

    albertotactik Member

    Level: Community
    good,
    I have reviewed this link http://fabrikar.com/forums/index.php?wiki/calculation-element/ and I do not see clearly that you can use element "calc" to add several elements of several rows of the same table.
    let's see if you could give me an idea ... to continue researching ...
    if I had not explained myself well ... I would look to send a more clarifying example.
    Thanks again!!

    Alberto
     
  3. albertotactik

    albertotactik Member

    Level: Community
    Good…
    I enclose an example ... I feel that I have not explained myself well anyway.
    I have 2 rows in a list. And I correctly add in a row the elements that I want using “calc”
    But I can't add an element of a row "id10" + another element of the row "id11"
    What are the elements of several different rows (years 2018 and 2019)
    Example “annual deute (id10) + annual deute (id11) = SUMA DEUTE
    Example “240 + 30 = 270
    I have also thought, in creating a while, so that it traverses the data of the total columns and accumulates them ... but I just did not see it clearly ... I would greatly appreciate any ideas !!
    Let's see if with this example, you can indicate me some idea, to be able to advance a little
    A million thanks…

    Alberto
     

    Attached Files:

  4. lousyfool

    lousyfool Active Member

    Level: Community
    You can use calc elements for this again, this time with SQL queries summing up column values of rows depending on your WHERE clause.
    Of course, they'll display in every row, but with some clever row classes and CSS you could hide the "duplicates".

    There may be different ways depending on your table structure. But if all relevant data lives in one and the same table, then I guess this is the way to go.
     
    albertotactik likes this.
  5. lousyfool

    lousyfool Active Member

    Level: Community
    Well, as opposed to hiding duplicates, of course you could also use a condition which only returns the sum e.g. if the year element is equal to the current calendar year.
     
  6. albertotactik

    albertotactik Member

    Level: Community
    good...
    thanks to your comment ...
    I created this code ...

    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query->select('gp_imp_cuotaimpagat');
    $query->from($db->quoteName('gpimpagats'));
    $query->where($db->quoteName('gp_imp_cuotaimpagat')." = ".$db->quote($value));

    $db->setQuery($query);
    $column= $db->loadColumn();
    print_r($column);


    I get a 500 error from the query ...

    500 Fabrik has generated an incorrect query for the list Impagats: <br />
    Do you observe any syntax errors ...?
    say that I am not a database expert ...
    Thanks for helping me get closer to my dilemma.
     
  7. albertotactik

    albertotactik Member

    Level: Community
    the table is called "gpimpagats" and the row element is called "gp_imp_cuotaimpagat"
    I think I'm not doing the query well ...

    I also think that it would not be necessary to write the "where" statement because in principle I want to get all the values of the "gp_imp_cuotaimpagat" columns

    could you do without the where clause?
    I get the feeling ... I'm getting to success.
    Thank you.
     
  8. lousyfool

    lousyfool Active Member

    Level: Community
    Try this -- after replacing Fabrik element and DB columns with yours:

    Code (Text):
    <?php
    $expedient = '{table___name_element}';
    // get "deute anual" sum
    $mydb = JFactory::getDbo();
    $query = $mydb->getQuery(true);
    $query->select('SUM('.$mydb->quoteName('column_name_of_deute_anual').')');
    $query->from($mydb->quoteName('gpimpagats'));
    $query->where($mydb->quoteName('column_name_of_expedient').' = '.$mydb->quote($expedient));
    $mydb->setQuery($query);
    $total_deute_anual = $mydb->loadResult();
    // return result (in all rows)
    return $total_deute_anual;
    ?>
     
    Syntax: https://docs.joomla.org/Selecting_data_using_JDatabase
    As per Wiki it's recommended to use "$mydb" or else; not just "$db" as that may cause trouble in Fabrik.
     
    Last edited: Oct 18, 2019
  9. lousyfool

    lousyfool Active Member

    Level: Community
    You probably figured it out already... I forgot a closing bracket in the $query->from line.
    Sorry. Updated it in my last post here above.
     
  10. albertotactik

    albertotactik Member

    Level: Community

    good...
    I still can't get it.

    <?php​
    $expedient = '{gpimpagats___gp_imp_cuotaimpagat}';
    // get "deute anual" sum
    $mydb = JFactory::getDbo();
    $query = $mydb->getQuery(true);
    $query->select('SUM('.$mydb->quoteName('gp_imp_totaldeute').')');
    $query->from($mydb->quoteName('gpimpagats');
    $query->where($mydb->quoteName('gpimpagats___gp_imp_cuotaimpagat').' = '.$mydb->quote($expedient));
    $mydb->setQuery($query);
    $total_deute_anual = $mydb->loadResult();
    // return result (in all rows)
    return $total_deute_anual;
    ?>


    I get this error ... 0 syntax error, unexpected '<', expecting end of file
    I have reviewed ... and tried several changes and nothing ...

    I have also looked to remove this line ... to limit errors
    // $ query-> where ($ mydb-> quoteName ('gpimpagats ___ gp_imp_cuotaimpagat'). '='. $ mydb-> quote ($ expedient));
    And it gives me the same mistake.

    I have doubts about the data I should have to write in the fields marked in red ...
    I begin to be confused and thick.

    explanatory summary
    database name (I understand that it should not be written)
    table name "gpimpagats"
    element name of the rows to SUM "gpimpagats___gp_imp_cuotaimpagat"

    I also attach another image ... of the elements ... to clarify

    thank you thank you for the time spent ...
     

    Attached Files:

  11. albertotactik

    albertotactik Member

    Level: Community
    ohhh thanks!!!
    I review it
    I'll tell you later
    thanks
     
  12. lousyfool

    lousyfool Active Member

    Level: Community
    In the WHERE clause you must not use the Fabrik placeholder nomenclature but the DB table name.
    So, all corrected, this should work:
    Code (Text):
    <?php
    $expedient = '{gpimpagats___gp_imp_cuotaimpagat}';
    // get "deute anual" sum
    $mydb = JFactory::getDbo();
    $query = $mydb->getQuery(true);
    $query->select('SUM('.$mydb->quoteName('gp_imp_totaldeute').')');
    $query->from($mydb->quoteName('gpimpagats'));
    $query->where($mydb->quoteName('gp_imp_cuotaimpagat').' = '.$mydb->quote($expedient));
    $mydb->setQuery($query);
    $total_deute_anual = $mydb->loadResult();
    // return result (in all rows)
    return $total_deute_anual;
    ?>
    This is assuming that "gpimpagats___gp_imp_cuotaimpagat" is the name element (if it is but doesn't work, try "gpimpagats___gp_imp_cuotaimpagat_raw").
     
    Last edited: Oct 18, 2019
  13. albertotactik

    albertotactik Member

    Level: Community
    good
    I've tried too ...
    with gpimpagats___gp_imp_cuotaimpagat_raw
    and still giving error 0 syntax error, unexpected '<', expecting end of file

    <?php
    $expedient = '{gpimpagats___gp_imp_cuotaimpagat}';
    // get "deute anual" sum
    $mydb = JFactory::getDbo();
    $query = $mydb->getQuery(true);
    $query->select('SUM('.$mydb->quoteName('gp_imp_totaldeute').')');
    $query->from($mydb->quoteName('gpimpagats'));
    $query->where($mydb->quoteName('gp_imp_cuotaimpagat_raw').' = '.$mydb->quote($expedient));
    $mydb->setQuery($query);
    $total_deute_anual = $mydb->loadResult();
    // return result (in all rows)
    return $total_deute_anual;
    ?>
     
  14. lousyfool

    lousyfool Active Member

    Level: Community
    Did you remove "<?php" and "?>" at beginning and end?
     
  15. albertotactik

    albertotactik Member

    Level: Community
    I have removed it ... and it gives error
    500 Fabrik has generated an incorrect query for the list Impagats: <br />
    our head is going to explode ...
     
  16. troester

    troester Well-Known Member Staff Member

    Level: Community
    no _raw here (this is not Fabrik but the DB column name)
    $query->where($mydb->quoteName('gp_imp_cuotaimpagat_raw').' = '.$mydb->quote($expedient));

    You may have to use _raw with the element placeholder
    $expedient = '{gpimpagats___gp_imp_cuotaimpagat_raw}';
     
    lousyfool likes this.
  17. lousyfool

    lousyfool Active Member

    Level: Community
    Thanks @troester ... I didn't see that in OP's post.
    So...........
    Code (Text):
    $expedient = '{gpimpagats___gp_imp_cuotaimpagat_raw}';
    // get "deute anual" sum
    $mydb = JFactory::getDbo();
    $query = $mydb->getQuery(true);
    $query->select('SUM('.$mydb->quoteName('gp_imp_totaldeute').')');
    $query->from($mydb->quoteName('gpimpagats'));
    $query->where($mydb->quoteName('gp_imp_cuotaimpagat').' = '.$mydb->quote($expedient));
    $mydb->setQuery($query);
    $total_deute_anual = $mydb->loadResult();
    // return result (in all rows)
    return $total_deute_anual;
     
     
  18. albertotactik

    albertotactik Member

    Level: Community
    It tastes really bad ...
    but still not working ... error 500 Fabrik has generated an incorrect query for the list Impagats: <br />
    I'm sorry...
    This is the definitive code ...
    I'm about to throw in the towel ...
    Thanks for the patience + dedication + speed!

    $expedient = '{gpimpagats___gp_imp_cuotaimpagat_raw}';
    // get "deute anual" sum
    $mydb = JFactory::getDbo();
    $query = $mydb->getQuery(true);
    $query->select('SUM('.$mydb->quoteName('gp_imp_totaldeute').')');
    $query->from($mydb->quoteName('gpimpagats'));
    $query->where($mydb->quoteName('gp_imp_cuotaimpagat').' = '.$mydb->quote($expedient));
    $mydb->setQuery($query);
    $total_deute_anual = $mydb->loadResult();
    // return result (in all rows)
    return $total_deute_anual;

    /*
    no _raw here (this is not Fabrik but the DB column name)
    $query->where($mydb->quoteName('gp_imp_cuotaimpagat_raw').' = '.$mydb->quote($expedient));

    You may have to use _raw with the element placeholder
    $expedient = '{gpimpagats___gp_imp_cuotaimpagat_raw}';
    */​
     
  19. troester

    troester Well-Known Member Staff Member

    Level: Community
    For debugging:
    In Fabrik Options/Debugging set "Allow Fabrik debug" =yes and then add &fabrikdebug=1 to your URL, this should display the complete SQL error message.

    Or
    in your code do
    echo $query; exit;
    (before $mydb->setQuery )
    this should display the generated query on a blank page
     
  20. albertotactik

    albertotactik Member

    Level: Community
    I have added the following
    echo $query; exit;
    and
    &fabrikdebug=1 in my URL

    for the record, I am trying to follow your steps ... and recommendations.
    I really appreciate it ...

    PS .: Could you send me a simple and simple query ... ??
    to show a single field for example ...
    I'm about to say, it doesn't work either ...

    Thanks again

    and it shows me a totally blank page.
    $expedient = '{gpimpagats___gp_imp_cuotaimpagat_raw}';
    // get "deute anual" sum
    $mydb = JFactory::getDbo();
    $query = $mydb->getQuery(true);
    $query->select('SUM('.$mydb->quoteName('gp_imp_totaldeute').')');
    $query->from($mydb->quoteName('gpimpagats'));
    $query->where($mydb->quoteName('gp_imp_cuotaimpagat').' = '.$mydb->quote($expedient));
    $mydb->setQuery($query);
    $total_deute_anual = $mydb->loadResult();
    // return result (in all rows)
    return $total_deute_anual;
     

Share This Page