Update calculated fields

Calculated fields are updated when the record is opened in Detail View or when the record is updated in Table View. The latter (Table View) carries the risk that certain records are not updated. The built-in implementation cannot guarantee it technically. Sometimes it is therefore necessary to trigger the recalculation manually. How to do this, I show here.

The Problem in Table View

In Table View the calculated fields are calculated row by row. The client (browser) sends a request to the server for each row. The server accepts the request, calculates the field values and writes the result into the database. Afterwards the server answers the query. The answer is again received by the browser and updated in the UI table.

You can see the row by row procedure in the Network tab of the developer tools.

The screen recording above shows very nicely that each request takes about half a second, i.e. two records are updated per second. if, for example, 50 records are visible per page, then it takes 25 seconds until the last record is updated.

If a user does not wait until the end, records may not be updated. The records on other pages (paging) are not updated either, unless you switch to the other page (and wait long enough).

In one of my projects this led to problems when exporting to a downstream accounting system. Since then I check very carefully in advance if I use calculated fields or take alternative ways like SQL commands in TABLENAME_init hook.

Alternatives

SQL in hooks

You can update records in TABLENAME_after_insert, TABLENAME_after_update, or TABLENAME_init hook using the built-in sql()-function. This is in example for init-hook:

// file: hooks/TABLENAME.php

// ...

function TABLENAME_init(&$options, $memberInfo, &$args) {
  $sql = "UPDATE `TABLENAME` SET `FIELDNAME` = 'value' WHERE `FIELDNAME` IS NULL";
  sql($sql, $eo);
  return TRUE;
}

// ...

Trigger update by code

Another possibility is to trigger the function with a php function call. The principle is simple, because AppGini provides functions for this:

$tn = 'TABLENAME';
$pk = 1; // the primary key of the record you want to update

$formulas = calculated_fields();
update_calc_fields($tn, $id, $formulas[$tn]);

You can also have multiple records updated.

function calculations_init(&$options, $memberInfo, &$args)
{
  // your table name
  $tn = "calculation_items";
  // change only records matching the following condition
  $where = "sales_price = 0 OR sales_price IS NULL";

  // get primary key column of table
  $pk = getPKFieldName($tn);
  // get primary keys
  $ids = explode(",", sqlValue("SELECT group_concat({$pk}) FROM `{$tn}` WHERE {$where}"));
  $formulas = calculated_fields()[$tn];

  foreach ($ids as $id) {
    update_calc_fields($tn, $id, $formulas);
  }
  // ...
}

In this example I first determine the primary keys of all records that do not yet have a sales price. The SQL function group_concat returns all IDs with comma as separator. With the PHP function explode I convert this string into an array of IDs. Then I call the update function for each id in a loop.

If you take a closer look at the update_calc_fields function in PHP, you will probably find that it can be further accelerated and optimized so that several, certain or all records are updated "in one go". But this is another topic I cannot go into here.

Summary

I hope this contribution has contributed to the understanding of calculated fields. I can only suggest that, when implementing your projects, you should consider beforehand how critical or important a guaranteed update is. if it is critical, I recommend alternative paths.

Recommendations

Here are a few indicators and recommendations.

If at least one Con-indicator of a table applies, you should choose an alternative to the calculated fields.