Using calculated fields

Tutorial

Calculated fields provide a simple way to perform standard SQL calculations (count, sum, min, max, etc.) in an API call.

Try these examples in the 'API Docs' tab of the DreamFactory Admin Console or from the command line with cURL.

API Endpoint

GET https://{url}/api/v2/{api_name}/_table/{table_name}?fields={calculated_field}

API Docs Screenshot

Example - Use a calculated field to calculate the sum of the 'amount' field in a transactions table.

Say we have a 'transaction' table with an 'amount' field. We need to perform an API call on a set of records in the transaction table that returns the total amount.

All we need to do is set up a "virtual" calculated field that defines the calculation. To set up a calculated field, navigate to the Schema tab and click to add a new field. In this example, we will add a field called 'amount_total' and fill in the field definition as shown in the screenshot below.

Now we can make API calls to the 'transaction' table and GET the total amount for the returned set of records.

• Table name: transaction
• Fields parameter in API call:

  amount_total

• Request URL:

  https://foo.com/api/v2/mysql/_table/transaction?fields=amount_total

Example - Use a calculated field to calculate the sum of the 'amount' field and group by email address.

This example is similar to the example above, except we need to group 'amount_total' by another field in the transaction table, say 'email'. We want the API call to return distinct email addresses and calculate total_amount grouped by each email address.

The "virtual" calculated field set up is identical to the example above.

To group amount_total by email address, you simply request both 'email' and 'amount_total' in the fields parameter and 'email' as the field to group by.

• Table name: transaction
• Fields parameter in API call:

  email, amount_total

• Group parameter in API call:

  email

• Request URL:

  https://foo.com/api/v2/mysql/_table/fields=email%2C%20amount_total&group=email