G Suite Business customers can preview App Maker. Ask your domain admin to apply for early access.

Calculated Models

A calculated model is a virtual model that computes records on the App Maker server instead of storing them in a database. Calculated models process data from other models or external resources. For example, a calculated model could synthesize a custom report from another model's data or pull data from an external web service.

Calculated models are most useful when you need to restructure data from another model. If your calculated model's fields have significant overlap with an existing model, a query datasource is usually simpler.

Calculated model types

There are two types of calculated models:

  • Query Script: A server-side script that performs some logic and returns an array of records.

  • SQL: A SQL query that's executed against the Google Cloud SQL database configured in the app.

Limitations

Calculated models have several limitations:

  • Their records can't be modified or deleted.
  • Their records can't be created on the client.
  • They can't have relations to other App Maker models including themselves.

Example

The query script below uses an Employee model's Location field to calculate how many employees are at each location:

var calculatedModelRecords = [];
var recordsByLocation = {};
var allEmployees = app.models.Employee.newQuery().run();
for (var i = 0; i < allEmployees.length; i++) {
  var employee = allEmployees[i];
  if (!recordsByLocation[employee.Location]) {
    var calculatedModelRecord = app.models.EmployeesByLocation.newRecord();
    calculatedModelRecord.NumberOfEmployees = 1;
    calculatedModelRecord.Location = employee.Location;
    calculatedModelRecords.push(calculatedModelRecord);
    recordsByLocation[employee.Location] = calculatedModelRecord;
  } else {
    recordsByLocation[employee.Location].NumberOfEmployees++;
  }
}
return calculatedModelRecords;

The script iterates over each record in the Employee model. It creates a record for each new location it finds, and adds to the employee count for any records with repeat locations.

One thing to note is that the script creates calculated model records using newRecord(), but this doesn't actually create a record in a data backend. Instead, the model simply returns the records to the client directly.