Skip to main content

Write calculations that extend your database apps

Calculation fields do the math for you. Learn how they work, then copy six examples: totals, counts, days between dates, text, progress, and labels.

Written by Leo
The calculation editor of the Total field in the Order lines app with the calculation Unit_price * Quantity, and under Preview with the result $2,274.00 for the order line Solar panel 400 W

Let a calculation field do the math for every record.

Some information isn't typed in, it follows from other fields: a total price, the days left until a deadline, the progress of a project. A Calculation field works it out for every record and updates when the fields it uses change. No more math by hand, and no more outdated numbers.

How a calculation field works

A calculation is a short piece of JavaScript. You don't need to be a developer: most calculations are one line, and the examples below cover the common cases.

  1. Open the app menu, select Customize record layout, click + next to a field, and choose Calculation under New field.

  2. Click the handle (⋮⋮) of the field, select Edit field, then Edit calculation.

  3. Type @ or start typing a field name to insert a field. Field names appear with underscores instead of spaces, for example Unit_price.

  4. Check the result under Preview with on a real record.

  5. In Edit field, set Format to Number for numbers or to Date for dates. None shows the result as text.

The last line of a calculation is its result. A calculation returns a number, a text, or a date. Learn more about the calculation field →

Typing Unit in the empty calculation editor of the Total field and selecting Unit_price, then typing * Qua and selecting Quantity, after which Preview with shows $2,274.00

Start typing a field name to insert it into a calculation.

Six calculations to get you started

1. Add up or multiply values

The total price of an order line:

Unit_price * Quantity

With 19% tax, rounded to two decimals:

Math.round(Unit_price * Quantity * 1.19 * 100) / 100

2. Add up values from linked records

The total of all line items of an invoice, linked with a Relation field called Line items:

Line_items.Amount.sum

Use .avg for the average, .min and .max for the smallest and largest value, and .all for a list of all values. Learn more about the relation field →

3. Count linked records

The number of tasks of a project, linked with a relation field called Tasks:

Tasks.Title.all.length

4. Days between two dates

Days left until a deadline, using today's date:

moment(Due_date).diff(Today, 'days')

Days between two dates of the same record, such as how long a deal took:

moment(Closed_on).diff(Created_on, 'days')

5. Progress of a project in percent

The share of tasks that are done, with a check for projects without tasks:

Tasks.Status.all.length ? Math.round(Tasks.Status.all.filter(s => s === 'Done').length / Tasks.Status.all.length * 100) : 0

A status field gives the name of the option, so replace 'Done' with the name your team uses. Learn more about the status field →

6. Combine text and add a label

A full name from two fields:

First_name + ' ' + Last_name

A label that changes with the date:

moment(Due_date).diff(Today, 'days') < 0 ? 'Overdue' : 'On track'

With Format set to None, a text result can use simple HTML, for example '<b>' + Status + '</b>' for a bold status. Learn more about calculation syntax →

Tips for reliable calculations

  • Check for empty values. An empty field gives an empty value. Use || to fall back, for example (Discount || 0).

  • Test before you save. Preview with shows the result for a real record, or an error if something's wrong.

  • Convert text to numbers. Some values arrive as text. Use Number() before you calculate.

  • Need it in an automation? The Perform calculation action does the same in an automation and saves the result in a variable. Learn more about the Perform calculation action →

Learn more

FAQs

Can a calculation use fields from another database app?

Yes, through a Relation field. Write the relation field name, then the field of the linked records, then what you want, such as .sum or .all. Learn more about calculation syntax →

Why does my calculation stay empty?

The result may be true or false, a list, an empty value, or not a number. A calculation must return a number, a text, or a date. Check the result under Preview with. Learn more about calculation syntax →

Can I edit the value of a calculation field?

No. Tape works it out from the other fields. To change the result, change the fields it uses or the calculation itself.

Still have more questions? Find answers and get help from community experts in the Tape community →.

Did this answer your question?