1. Fabrik 3.8.1 has been released. It is mostly bug fixes and feature enhancements, but does include two new plugins (push notifications, and the sequence element). As usual we strongly recommend testing the new release on a sandbox if your application is mission critical, and always do an Akeeba backup before updating.
    Dismiss Notice

Calculation element

Jul 6, 2018
Calculation element
  • The Calc element can be used to calculate the value of a read-only field using PHP. The PHP can do something as simple as a calculation based on other fields in the same list / form (using placeholders) or use more complex algorithms that use external data from an SQL database or potentially a synchronous web-service call.

    The calc is run when the form is saved and also:

    • When a form is loaded (enabled by default)
    • When a row is displayed in a list (enabled by default)
    • When another field in a form is changed by the user using Ajax (disabled by default)

    If your PHP uses external data then you need to be aware of the performance implications of having to wait for this data to be requested and received because the requests are synchronous and Fabrik execution is paused until the response is received - so if there are a large number of calc fields in a form, or if the user is displaying a lot of records in a list, then the cumulative elapsed time for handling all these calc fields can easily take several seconds. Some ideas for mitigating this are provided below.


    Calc settings(top)


    upload_2016-2-27_20-58-4.png

    Options:
    • Hidden - Is the field hidden
    • Width - The width of the field
    • Height - The height of the field
    More:
    • Calculation - The calculation PHP expression that returns the value. This can use placeholders to use the values of other fields.
    • Format string - Applies PHP's sprintf function to the data, using this fields value as the format field.
    • Only Calc on Save
      • If set to yes then the calculation is performed only when the form is saved (or if using Ajax calculations when the form's values change)
      • If set to no then the calculation is additionally applied to the data when viewing the table. This option produces an additional overhead when rendering your table's data, but is useful if you add a calculation to an existing large data set.
    • Ajax calculation
      • If set to no then the calculation is run when the form is saved
      • If set to yes then the calculation is run whenever any element whose placeholder is referenced in the 'Calculation' field is updated. The calculation is run via an Ajax call and the element's value is updated with the new calculated value
    • Observe Fields - In addition to the element placeholders listed in the 'Calculation' field, you can supply a comma separated list of elements placeholders that you want to observe. Changing these element's values will trigger the Ajax calculation

    Simple examples(top)

    Calculate:
    PHP:
    $myCalc = (int)'{table_name___elementname}' * 5;
    return $myCalc;
    Concatenate two fields:
    PHP:
    return '{table_name___element1}' . '{table_name___element2}';
    Summing elements 1 + 2 then multiplying with the value of element 3:
    PHP:
    $myCalc = ((int)'{table_name___element1}' + '{table_name___element2}') * (int)'{table_name___element3}';
    return $myCalc;

    Accessing data(top)

    For elements such as radiobuttons, checkboxes and dropdowns you can use {table_name___elementname_raw} to access the value as opposed to the label.

    E.g.
    PHP:
    return (int)'{table_name___elementname_raw}' * 5;
    All posted data is stored in the array $data, this is useful if you wish to retrieve repeat group data. To get a debug output of this data you can use this php:
    PHP:
    echo '<pre>';
    print_r($data);
    echo '</pre>';
    exit;

    When are calculations run?(top)

    The calculation is always run on form submission.

    If "Calc on save only" is set to No, the calc is run every time the element is rendered, ie. in list and details views, in addition to being calculated on submission.

    If "Ajax calculation" is set to Yes then the calculation runs each time an observed field's value changes.
    Note: For valid reasons, the Calc php can be run multiple times per element - you should not use PHP that makes changes that are not repeatable an arbitrary number of times.

    Retrieving data from complex elements(top)


    Json decode helper

    Using Fabrik 3.2, let's say you want to retrieve the value of DBjoin element which is in a joined repeated group (n-m relationship). As you may have multiple values, the result is a Json encoded string.

    The following syntax will return an array of values with the correct encoding structure (accent characters) whatever the number of repeated joined records you have :
    Code (Text):
    $foo = FabrikWorker::JSONtoData($data['tablename___elementname'], true);
    In order to return a string with the list of the joined values just do :
    Code (Text):
    return implode(',', $foo);

    IMPORTANT NOTES(top)


    • You should almost always put quotes around placeholders in your code, to avoid syntax errors if the element has an empty value. Remember that placeholders are replaced by the values by Fabrik before handing your code to PHP to evaluate. So if your code is "if ({yourtable___foo} > 1) {", and 'foo' is empty, the code handed to PHP is "if (> 1) {", which is a syntax error. Also, where possible, "cast" your quoted string to the appropriate type, like "if ((int)'{yourtable___foo}' > 1) {". If foo is empty in that situation, the resulting code will be "if ((int)'' > 1) {", which is syntactically correct, as (int)'' is 0.
    • The calc element should only be used to return a value. It should not be used to "do things", like update database tables. If you need to do "stuff" other than just return a value for the element, you should use a PHP form submission plugin.
    • You cannot use the values of other calc elements in your code. This creates a chicken and egg situation, as there is no guarantee that another calc element's code has been run yet. The order of execution of calc elements is "arbitrary" - we make no guarantees about what order they run in.
    • If your calc is in a repeat group, for other elements in the same group you will only be able to access data for the same repeat instance. So for a calc my_repeat___calc, if you use {my_repeat___foo}, each repeat instance of the calc will see the value of 'foo' for it's own repeat instance.
    • In details views (as opposed to form views), raw element placeholders are not available for AJAX mode. This is especially relevant to the "Calc on Load" feature.

    Performance Tips(top)


    Calc fields are executed depending on their settings. The default is on load and save of a form, and on load of a details view or list. But for a form, you can make it calculate only on save with an option - or you can make it ajax in which case it calcs whenever you change any of the other fields in the form.

    If you have a large list, then calculating each calc field on each row individually when you display the list can be very slow. So a few rules of thumb (and you need to listen carefully, because I vill say zis only vonce):
    1. If the value of the calc field depends only on other elements in the list which can only be changed by editing the form (i.e. when you calc on save then the calc value will be always stay correct), then set Only Calc on Save to Yes. (Or alternatively, if the calcs are simple, then you might want to consider a different approach where instead of using ajax Calc elements you instead use read-only field elements and javascript change events to calculate the new values in the browser using javascript rather than using calc elements to calculate them on the server using php.)

    2. If the value of the calc element depends on data which can change after the form has been saved - i.e. because it is in another list or something else changes it, and you are going to have the calc fields visible in the list, then you do need to calculate this when you view the data. If the number of rows you are going to display on your list is small, you can set Only Calc on Save to No. But if you are going to display lots of rows or need to have Only Calc on Save set to Yes then you need to do both of the following:
      1. Set Ajax Calculation to Yes and set Calc on Load to Yes also; and

      2. Use a List Plugin PHP events to run an SQL update on your table to update any rows which need recalculating before you load the data into the list - you should be able to do this as a single UPDATE statement which will run efficiently, and for every SET clause (e.g. SET calc_field1 = stuff) you should have a WHERE clause (e.g. WHERE calc_field1 <> stuff) so that you only update records where the value has actually changed. NOTE: I would classify this as an advanced use of Fabrik - you need to be proficient at SQL, though there are some examples in the forums you can crib from.

    Performance example(top)

    Here is a real life performance example - which counts the number of people allocated to an application group:

    Calc PHP
    PHP:
    // Count the number of people in the application group

    $db = FabrikWorker::getDbo();
    $query = $db->getQuery(true);

    $query->clear()
      ->select('COUNT(*)')
      ->from($db->quoteName('rp_user_stats'))
      ->innerjoin($db->quoteName('rp_users') . ' ON ' . $db->quoteName('rp_user_stats.user_id') . ' = ' . $db->quoteName('rp_users.user_id'))
      ->where(array(
        $db->quoteName('rp_users.blocked') . ' = 0',
        $db->quoteName('rp_users.lapsed') . ' = 0',
        $db->quoteName('rp_user_stats.estate') . ' = ' . $db->quote('{rp_estates___estate_id}'),
        $db->quoteName('rp_users.name') . ' <> ' . $db->quote('Webmaster')
      ));

    $db->setQuery($query);
    $count = $db->loadResult();
    return $count;
    List PHP Events Plugin - onPreLoadData event
    PHP:

    // Update the number of people in the application group

    $db = FabrikWorker::getDbo();
    $query = $db->getQuery(true);
    $subQuery = $db->getQuery(true);

    $subQuery->clear()
      ->select(array($db->quoteName('estate'),'COUNT(*) AS count'))
      ->from($db->quoteName('rp_user_stats'))
      ->innerjoin($db->quoteName('rp_users') . ' ON ' . $db->quoteName('rp_user_stats.user_id') . ' = ' . $db->quoteName('rp_users.user_id'))
      ->where(array(
        $db->quoteName('rp_users.blocked') . ' = 0',
        $db->quoteName('rp_users.lapsed') . ' = 0',
        $db->quoteName('rp_users.name') . ' <> ' . $db->quote('Webmaster')
      ));

    $value = 'IFNULL(' . $db->quoteName('user_fealties.count') . ', 0)';

    $query->clear()
      ->update($db->quoteName('rp_estates'))
      ->leftjoin('(' . $subQuery . ') AS ' . $db->quoteName('user_fealties') .
                  ' ON ' . $db->quoteName('rp_estates.estate_id') . ' = ' . $db->quoteName('user_fealties.estate'))
      ->set($db->quoteName('rp_estates.fealties') . ' = ' . $value)
      ->where($db->quoteName('fealties') . ' <> ' . $value);

    $db->setQuery($query);
    try
    {
      $db->execute();
    }
    catch (Exception $e)
    {
      JFactory::getApplication()->enqueueMessage('Update of group counts failed: ' . $e->getMessage(), 'error');
    }
  • Loading...
samani, Bauer, ae2c2 and 2 others like this.