Calculated Models

A calculated model is a virtual model. It's created by processing data from other models or external resources. The data is processed and stored on the App Maker server or client instead of in a database.

A calculated model can do the following:

synthesize a custom report from another model's data
get data from an external web service

Calculated model types

App Maker has three kinds of calculated models:

Calculated SQL: The model uses a SQL query to the app's Cloud SQL database to populate its records. Learn more about SQL calculated model.
Calculated: The model uses a server-side query script that performs some logic and returns an array of records. Learn more about Query script and an example.
Client-side calculated: The model uses a client-side query script to fetch data synchronously or uses a callback to return asynchronous results.

Client-side calculated models

Client-side calculated models use a custom query script that runs instead of the normal datasources query, passing in any filters from the query parameter.

To use a custom query script:

Go to the Datasources tab of the client-side calculated model.
In the Query code box, enter your custom query script. Your script must include a return statement.
To render the data that is retrieved from third-party services:
1. Map your script query with the third-party API query.
2. Map the returned data to the model records.

Which model should you use?

Review the following table and the calculated model limitations to determine the best model type to use.

Goal	Model to use
Restructure data from a model in your app.	Calculated SQL or Calculated (server-side) model. If your calculated model uses many of the same fields as an existing model, use a query datasource instead of a server-side calculated model. A query datasource is usually simpler.
Fetch data using an API and a client-side library.	Client-side calculated model With this model you can get data using the Google API or third-party services, such as the Facebook SDK or SalesForce APIs.
Quickly display app components, such as charts and dynamic menus, without querying the server.	Client-side calculated model

Limitations

Server-side calculated model limitations:

Their records can't be modified or deleted (records are read-only).
Their records can't be created on the client.
They can't have relations to other App Maker models, including themselves.
Calculated SQL model queries are not subject to the security restrictions set for the queried models. Set security restrictions for the calculated SQL model itself in its Security tab.

Client-side calculated model limitations:

Their records can't be modified or deleted (records are read-only).
They can't have relations to other App Maker models, including themselves.
They don't support paging or sorting configuration.
They don't support events or security configurations.
An error is returned if a result is returned more than once, such as a double callback call or a return and callback call.

Calculated model examples

Calculated SQL model

For examples of calculated SQL models, see Cloud SQL.

Server-side query script

The following query script 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 and does the following:

It creates a record for each new location it finds.
It adds to the employee count for any records with repeat locations.

The script creates calculated model records using newRecord(), but this method doesn't actually create a record in a data backend. Instead, the model returns the records to the client directly.

For more examples, see Query script.

Client-side calculated model

Example 1: Synchronous App Maker Client API query

The following script returns the Fibonacci series. Other App Maker operations wait for the results before continuing.

  var limit = 10;
  var result = [];
  for (var i=0; i < limit; i++) {
    var record =  recordFactory.create();
    if (i < 2) {
      record.Value = 1;
    } else {
      record.Value = result[i-1].Value + result[i-2].Value;
    }
    result.push(record);
  }
  return result;

Example 2: Asynchronous request to a third-party API

The following script uses the Google Books API to search for books that match the user's input (SearchString) and returns the results with a callback function.

// Note: Include JQuery library for the application
// Search for a book matching the user's search string
var searchString = query.parameters.SearchString;
$.getJSON('https://www.googleapis.com/books/v1/volumes?q=' + encodeURI(searchString), function(result){
  var records = result.items.map(function(bookData){
    // Create a record from the book's data
    var record = recordFactory.create();
    record.Title = bookData.volumeInfo.title || null;
    return record;
  });
  // Return results
  callback.success(records);
});

Example 3: Asynchronous App Maker Client API query with success and failure callback functions

The following script returns results asynchronously when results aren't needed immediately.

  externalApi.execute(function(results) {
    if (results.length) {
      сallback.success(mapResultsToRecords(results));
    } else {
      сallback.failure(‘No results were found');
    }
  });