Examples of Formulas

Modified on Tue, 18 Aug at 10:45 AM

Adding & Subtracting

Use the + and - math operators for numerical and string operations.

Example: 100 + 45.25

Example: TASK.estimatedCost - 350.50

Example: 'The project budget is: ' + PROJECT.fixedCost

Example: ((TASK.fixedCost - TASK.actualCost) + 1000.0)

Multiplying & Dividing

Use the * and / operators for numerical operations.

Example: 50 * 365.0

Example: STAFF.payAmount / 12

Example: (TASK.estimatedCost * 1.5) / TASK.REBATE.rebate

Use the divide() function to divide numerical values and handle errors such as divide by zero.

Example: divide(TASK.estimatedCost * 1.5, TASK.REBATE.rebate, 0.0)

Counting

Use count() to count the number of records matching your filter or with no filter.

Example: count(PROJECT.TASK.name, none)

Example: count(DEPARTMENT.name, none)

Example: count(PROJECT.TASK.name, PROJECT.TASK.STAGE.name eq 'Completed')

Example: count(STAFF.name, STAFF.staffType eq 'Full Time')

Example: count(BOOKING.name, BOOKING.PROJECT.name eq 'Avatar')

Example: count(TASK.name, or(TASK.STAFF.LOCATION.name eq 'Los Angeles',TASK.STAFF.LOCATION.name eq 'Vancouver'))

Summing

Use sum() to total (or add up) the numerical values for records matching your filter or with no filter.

Example: sum(PROJECT.estimatedTimeToComplete, none)

Example: sum(PROJECT.estimatedCost, PROJECT.STAGE.name neq 'Completed')

Example: sum(TASK.actualDuration, TASK.STAFF.name eq 'Jane Doe')

Example: sum(BOOKING.fixedCost, BOOKING.beginDate gte asDate('2026-01-01', 'dt'))

Example: sum(STAFF.payAmount, or(STAFF.SKILL.name eq 'Animation', STAFF.SKILL.name eq 'Effects'))

Working with a set of data points

Get the minimum value from a set of data points.

Example: aggregate(PROJECT.estimatedCost, none, 'min')

Get the maximum vale from a set of data points.

Example: aggregate(BOOKING.duration, BOOKING.PROJECT.CUSTOMER.name eq 'Nike', 'max')

Get the average value for a set of data points.

Example: aggregate(STAFF.payAmount, and(STAFF.staffType eq 'Full Time', STAFF.genericStaff eq false), 'mean')

Get the first matching value for a set of data points.

Example: aggregate(REBATE.name, REBATE.rebate gte 0.30, 'lookup')

Count the number of matching data points.

Example: aggregate(BOOKING.name, BOOKING.PROJECT.LOCATION.name eq 'Los Angeles', 'count')

Sum (or total) the values fo numerical data points.

Example: aggregate(TASK.estimatedTimeToComplete, or(TASK.STAFF.name eq 'Jane Doe', TASK.STAFF.name eq 'John Citizen'), 'sum')

Working with Strings

Concatenate strings together.

Example: 'Project name:' + PROJECT.name + ' LOCATION: ' + PROJECT.LOCATION.name

Example: 'Task start date:' + formatDate(TASK.startTime, 'YYY-MM-DD')

Example: concat('There are ', count(PROJECT.TASK.name), ' tasks in the project: ', PROJECT.name)

Convert data points to strings.

Example: formatDate(BOOKING.beginDate, 'YYYY-MM-DD')

Example: formatDuration(TASK.estimatedDuration, 'days')

Example: formatNumber(ACTIVITY.fixedCost, 'financial')

Example: formatFileSize(STORAGE_FILE.size, 'KB')

Example: formatTime(TASK.startTime, 'hh:mm:ss')

Format strings.

Example: upperCase(PROJECT.identifier)

Example: lowerCase(TASK.TAG.name)

Example: capitalize(STAFF.firstName) + ' ' + capitalize(STAFF.lastName)

Extract text from strings.

Example: textAfter(STAFF.identifier, 'EMPNUM-')

Example: textBefore(RESOURCE.customUnits, ' UNITS')

Example: textBetween(TASK.description, '===START===', '===END===')

Example: textLeft(BOOKING.identifier, 10)

Example: textRight(ACTIVITY.description, 25)

Find text within strings.

Example: textContains(lowercase(PROJECT.description), 'animation')

Example: textMatches(PROJECT.identifier, '^PROJ-\d{5}$')

Working with Numbers

Use abs() to derive the absolute value for a data point.

Example: abs(20000.00 - PROJECT.estimatedCost)

Use round() to round down to the nearest integer.

Example: round(TASK.estimatedDuration * 1.25)

Use round2() to round down to 2 decimal places.

Example: round2(PROJECT.fixedCost / PROJECT.REBATE.rebate)

Use floor() or ceil() to round up or down to nearest integer.

Example: floor(count(STAFF.name, STAFF.TASK.PROJECT.name eq 'Nike') * 1.3)

Example: ceil(PROJECT.fixedCost / 0.85)

Use math functions to analyze numerical data points.

Example: minValue(PROJECT.fixedCost, 5000.00)

Example: maxValue(STAFF.payAmount, 250000.00)

Example: sin((ACTIVITY.estimatedCost + 1000.00) / 12.45)

Example: cos(RESOURCE.resourceQuota / 470)

Example: tan(1000 - (TASK.actualCost / TASK.REBATE.rebate))

Format numerical data points into strings.

Example: 'The project budget is: ' + formatNumber(PROJECT.fixedCost, 'financial')

Example: 'Total staff costs are: ' + formatNumber(sum(STAFF.payAmount, STAFF.genericStaff eq false), 'commas')

Working with Dates and Times

Get today's date.

Example: getDate('now')

Working with fixed dates and times.

Example: asDate('2026-12-17 00:00:00', 'dt')

Example: asDate('2027-05-25 09:00:00', 'ts')

Example: toDate('2027-10-15 09:00:00')

Extracting parts (year, month, day, hour, minute, hour, etc.) from a date

Example: convertDate(BOOKING.beginDate, 'day')

Example: convertDate(TASK.startTime, 'month')

Example: convertDate(STAFF.startDate, 'year')

Example: convertDate(ACTIVITY.closeTime, 'hour')

Example: convertDate(BOOKING.untilDate, 'minute')

Comparing dates

Example: BOOKING.beginDate gte asDate('2027-05-24', 'dt')

Example: PROJECT.scheduleStart lt PROJECT.scheduleFinish

Offsetting dates to time zones

Example: offsetDate(PROJECT.scheduleStart, '-08h')

Count number of units in a date

Example: countDate(TASK.startTime, 'days')

Example: countDate(BOOKING.untilDate, 'months')

Working with Durations

Testing for Null or Zero Values

Use ifNull() to test if a data point does not exist, and provide a substitute value.

Example: ifNull(BOOKING.identifier, 'No identifier')

Example: ifNull(TASK.startTime, getDate('now'))

Use ifZero() to test if a data point does not exist or is zero, and provide a substitute value.

Example: ifZero(STAFF.payAmount, 'No salary set for staff')

Example: ifZero(TASK.fixedCost, 1000.0)

Conditionals (if and switch)

Use if() to conditionally process data points.

Example: if(STAFF.staffType eq 'Full Time', 'Staff is a permanent employee', 'Staff is contractor or freelancer')

Example: if((PROJECT.actualCost - PROJECT.estimatedCost) gt 0.0, 'This project is over budget!', 'This project is currently within budget'

Example: 'The estimated cost for this task is: ' + formatNumber(TASK.estimatedCost * if(TASK.STAFF.LOCATION.name eq 'Los Angeles', 1.0, 1.35), 'financial')

Nest if() functions to process additional conditions for data points.

Example: if (PROJECT.STAGE.name eq 'Bidding', 'Project is in bidding', if(PROJECT.STAGE.name eq 'Completed', 'Project has been completed', if(PROJECT.STAGE.name eq 'Paused', 'Project has been temporarily paused', if(PROJECT.STAGE.name eq 'Greenlit', 'Project has been greenlit', 'Project is currently in progress'))))

Add boolean operators to test for multiple conditions.

Example: if(and(TASK.startTime gt getDate('now'), TASK.startTime lt asDate('2027-06-30 00:00:00', 'ts')), TASK.estimatedDuration, 0.0)

Example: if(or(BOOKING.STAGE.name eq 'Producer Request', BOOKING.duration gt 0.0), BOOKING.PROJECT.name, '')

Example: if(and(STAFF.genericStaff eq false, STAFF.staffType eq 'Full Time', STAFF.LOCATION.name neq 'Los Angeles'), STAFF.payAmount * STAFF.LOCATION.REBATE.rebate, 0.0)

Use switch() as an alternative way to conditionally test for data points.

Example: switch(BOOKING.STAGE.name, 'ST-001 Producer Request', 'Booking has been requested by producer', 'ST-002 Approved', 'Booking has been approved', 'ST-005 Rejected', 'Booking request has been rejected', 'ST-004 Processing', 'Booking is currently being processed', 'Unknown status')

Getting Specific Data Points

Get the value for a specific data point.

Example: lookup(PROJECT.fixedCost, PROJECT.name eq 'Avatar')

Example: 'The budget for this project is:' + convertNumber(PROJECT.estimatedCost * lookup(COMPANY.customMargin, COMPANY.name eq 'Ninety Pixels')), 'financial')