Formula Fields
Introduction
Formula Fields are a new type of calculated fields that allow mathematical and logical operations on top of your existing field data, bringing more robust customization to your reporting needs. After a Formula Field is created, it is available for all filtering and grouping in reporting across Ashby.
The standard mathematical operations for addition, subtraction, multiplication, and division are supported in addition to specialized functions such as if statements and date difference or addition functions.
Looking for information on setting up calculated fields? Check out Calculated Fields. For more on offer formula fields, check out Offer Formula Fields.
General formula fields are only available on the Enterprise plan.
Foundations | Legacy Plus | Plus | Enterprise |
|---|---|---|---|
❌ | ❌ | ❌ | ✅ |
Offer formula fields are available on the Plus and Enterprise plans.
Example Use Cases
Formula Fields unlock a variety of potential use cases, but as motivation to think about applications, some conceptual examples are given below:
- Calculate bonus as a percentage of salary (multiplication)
- Calculate total compensation of base salary and bonuses (addition of fields)
- Calculate hourly rate from annual salary (division)
- Check if an offer salary is within the opening's comp range (logical comparison)
- Show time-to-fill in days (date difference)
- First interview to offer decided duration (date difference)
- Hired before target date or Started before target date (logical comparison of dates)
- Field to flag new hire hitting 90 day mark (date offset)
How to create a formula field
Creating a formula field follows the calculated field creation flow:
- Click on Admin in the top navigation bar, then Data Management, then Calculated Fields.
- Click + New to create a new calculated field.
- When creating a new calculated field, add in the object type, a title and select the Formula calculated field type from the Type dropdown.
- Click Create. You will then be taken to the formula field editor:
In the above example, we have a formula field that creates a new field returning a true/false value depending on whether the job consideration was hired prior to the associated opening’s target hire date.
The formula editor has a few noteworthy components:
- The title and description of the new formula field
- The editor space wherein formulas, functions, and field references are combined
- Insert Field button: click to add a new field or press $ as a hotkey
- Insert Function button: click to add a specialized function
- Cancel/Save buttons: save or cancel any changes.
After the field is saved, it can be used in filters and groupings.
If you have errors in your fields, you will not be able to save them. You can hover over areas of the formula highlighted in red to see the issue.
An example use case for formula fields
In this example, we'll create a formula field that track time-to-fill from the opening's Opened At date right up to the new hire's start date.
Firstly, we'll create our formula-type calculated field via the steps aboveabove.
When in the formula field editor, the datediff function is used to calculate the difference between two date fields. The Opening's [Opened At] date field and the offer's [Start Date] fields have been specified.
Finally, as part of the datediff functionality, the interval needs to be specified. For this calculated formula fields, we've specified the interval to be in days.
When building reports, this field should then appear as an option for filtering or grouping reports. It can also be used to display the calculated results as part of a custom list report.
Formula Field Syntax Reference
This initial early access release of formula fields has a limited set of functionality, but more functions and operators will be added throughout the development phase of this feature.
Referencing fields
Click the Insert Field button below the editor or type $ to choose a field and insert a field reference. This substitutes the value of the chosen field into the expression at that position. Field references appear in {braces} in the expression editor.
Booleans
The boolean values are true and false.
Numbers
Numbers may be integers or decimal numbers. Positive and negative values are supported:
- 123
- -4
- 5.6
Ranges
A range is written range(start, end) :
- range(1, 5)
Both of a range’s endpoints must be literal constant numeric values; more complex constructions like range(Field, 1 + 2) are not accepted.
Strings
String values are single- or double-quoted.
- “hello world”
- ‘i am text’
If you need to use a single or double quote in a string, you can either wrap the string with the other type of quote, or you can prefix it with \\:
- 'Jane "Example" Doe'
- "Jane \Example\ Doe"
- "John O'Example"
- 'John O\'Example'
To include a literal backslash in a string, use a double backslash:
- "example\\\\string"
Arithmetic
Numeric values may be added, subtracted, multiplied, or divided with +, -, *, / respectively:
- 1+1
- 2 * 3
Parentheses may be used for grouping:
- ({Score A} + {Score B})/ 2
Comparisons
Some operators have multiple accepted spellings, to maximize the chance of someone guessing correctly (for example, = and == ). Either form is accepted; they will be normalized to the first (“canonical”) form as part of autoformatting.
Values can be compared for equality with =and for inequality with != or <>:
- {Interviewer} = {Referrer}
- {Feedback Submitted By} != {Hiring Manager}
Numeric values can additionally be compared with >, <, <=, and >= :
- {Number of Job Considerations} >= 3
- {Overall Recommendation} < {Average Overall Recommendation}
Comparisons may be chained together:
- 1 <= {Score} < 5 (equivalent to 1 <= {Score} and {Score} < 5 )
Some fields contain numeric ranges. Use the inrange operator to check whether a value is within a range.
- {Salary Expectation} inrange {Compensation Range}
- {Score} inrange range(2, 8)
Conjunctions
Boolean variables can be combined with and (&, &&), or (|, ||), and not.
- not {Extended Offer?}
- {Candidate's Location} != {Job's Location} and not {Willing to relocate}
Available functions
The initial set of supported functions is as follows for formula fields:
Operation | Description | Function |
|---|---|---|
Date | Constructs a constant date value from a string in YYYY-MM-DD format. | date( value: A date string in YYYY-MM-DD format, e.g. "2026-05-01". ) |
Date difference | Take the difference between two date fields and returns a number. The interval may be "days", "weeks", "months", "quarters", or "years". | datediff(from: Date Field 1, to: Date Field 2, interval: "days") |
Date addition | Calculates an offset from a date. Returns a date. | dateadd(date: Date Field 1, amount: X, interval: "days") |
Today | Returns the current date | today () |
Ceiling | Rounds a number up to a given precision (number of decimal places). | ceiling (value: The value to round. precision: The precision to round to.) |
Floor | Rounds a number down to a given precision (number of decimal places). | floor (value: The value to round. precision: The precision to round to.) |
Minimum value | Returns the lowest of a set of numbers or the lower bound of a range. | min() |
Maximum value | Returns the highest of a set of numbers or the upper bound of a range. | max() |
Mean value (average) | Returns the mean of a set of numbers or the midpoint of a range. | mean() |
Round | Rounds a number to a given precision (number of decimal places). | round (value: The value to round. precision: The precision to round to.) |
To number | Attempts to convert a value to a number. Returns null if it is not possible. | tonumber ( value: The value to convert. ) |
If statements | Returns one of several values depending on conditions. Accepts one or more condition/value pairs followed by a fallback value: if(cond1, value1, cond2, value2, …, elseValue). | if (condition: The condition to use. if_true: The value to return if condition is true. if_false: The value to return if no condition is true, or the next condition if additional condition/value pairs follow. arguments...: Additional condition/value pairs followed by a fallback value. ) |
Coalesce | Returns the first value from its arguments that is not null. Allows you to set other values to fall back on if the first value is null. | coalesce ( value: The first value. arguments...: Additional values to use if the first value is null. ) |
Option | Creates an ad hoc option as if from a multiple-choice field. | option ( label: The name of the option. Must be constant. ) |
In range | Check if value is within a range, e.g. {Salary} within {Compensation Range} | inrange |
Range creation | Constructs a numeric range from two constant numbers. | range( min: The start of the range. max: The end of the range.) |
Concatenate | Combines multiple strings into one. | concatenate ( arguments...: The strings to concatenate together. ) |
Contains | Returns true if a string contains a substring. | contains ( string: The string to search within. substring: The string that may be found within that string. ) |
Ends with | Returns true if a string ends with a substring. | endswith ( string: The string to look at the end of. substring: The string that may be at the end of the other string. ) |
Starts with | Returns true if a string starts with a substring. | startswith ( string: The string to look at the start of. substring: The string that may be at the start of the other string. ) |
Split | Splits a string on a delimiter and returns the element at the given index. | split (string: The string to split. delimiter: The delimiter to split on. index: The zero-based index of the element to return.) |
Substring | Extracts a portion of a string. Uses zero-based indexing; start is inclusive and end is exclusive. | substring (string: The string to extract from. start: The zero-based start index (inclusive). Defaults to 0 if omitted. end: The zero-based end index (exclusive). Defaults to the end of the string if omitted. ) |
To string | Returns a text representation of a value. | tostring ( value: The value to convert to text. ) |
Function calls are written functionname(arg1, arg2, ...) or functionname(name1: value1, name2: value2, ...).
Function names are case-insensitive and ignore underscores: datediff, DateDiff, and DaTe_DiFf are all the same function (and will be autoformatted to datediff).
All functions come with inline syntax definitions and guidelines in-app. For example, here is the guidance on how to use the date difference operator:
Operator precedence
Operations are evaluated in the following order, with operations in the same group evaluated in the order they appear in the expression:
- (parenthesized groups)
- function_calls()
- x
- a * b, a / b
- a + b, a - b
- a inrange b
- a = b, a ≠ b, a is b, a isnot b, a < b, a > b, a ≤ b, a ≥ b
- not x
- a and b, a or b
Copying fields to build a formula outside of Ashby
You can copy fields within the formula field editor and paste them into a spreadsheet or other script to generate your formula prior to pasting it back into the editor.
When copying and pasting your field, it will appear in a different format to how it shows in Ashby. This ensures that when you paste your formula back into the editor, that the correct field is selected.
So, for example, if you’re copying the {Employment Type} field, it would show as follows when you paste it: $(ValueSelect,job.employment_type). When pasting your formula back into the formula field editor, it be auto-detected and show as a linked Ashby field.
If you were instead to paste in {Employment Type} without copying the field from Ashby, the field wouldn’t show as a linked Ashby field. It would be treated as plain text.
