The formula field allows you to enter formulas to manipulate text, numbers, and dates stored in fields in a tab and use them to derive a value that should display in the new field. Builders will use the formula editor to enter the function (a piece of instruction designed to calculate specific values) that will return the desired information in the field. Use formulas to:

• Do basic math functions using existing fields in the tab
• Derive a date or, do date-based calculations
• Concatenate data together
• Show a value in a field when certain conditions are met
• And much more

Formula fields are not directly editable and will auto-calculate based on the function instruction of the formula. They display with a light blue background: . The value from a function field can be downloaded, filtered, and looked up via integration using the Block's API.

This article covers:

• Fundamentals, operators, and functions
• Math operators
• Math functions
• Text string functions
• Date functions
• Adding or modifying a formula field
• And lots of important notes and examples

Fundamentals

Many formulas will be built by looking up information in another field on the tab. The field name will be referenced in the formula editor section of the function field using encased curly brackets: {field name} Any time you want to evaluate numbers or text to then output a specific set of information, you will use the CASE function. Below is an example of illustrating how a CASE formula is constructed:

Example formulas:

Example 1:

CASE

WHEN {Assembly Cost} > 1000 THEN 'Large'

WHEN {Assembly Cost} BETWEEN 500 AND 999 THEN 'Medium'

WHEN {Assembly Cost} BETWEEN 0 AND 499 THEN 'Small'

ELSE 'Unknown'

END

Result:

Given that the tab has a field named Price. When this field has a value under 1000 then show the text Large, if it's between 500 and 999 then show the text Medium, and when the process is between 0 and 499, show the text Small, otherwise, show the value of Unknown.

Example 2:

CASE
WHEN {Job Code}='MFAS1' then TO_CHAR({Hourly Pay Rate}*1.35,'L999D99')
WHEN {Job Code}='FAAC1' then TO_CHAR({Hourly Pay Rate}*2,'L999D99')
ELSE 'enter a valid code'
end

Result:

Given that there are two fields in the tab, Job Code and Hourly pay rate (which is a number). If the job code is MFAS1, then multiply the hourly pay rate times 1.35, then convert it to a text string and output it in the format that is like $999.99. If the job code is FAAC1, then multiply it times 2, then convert it to a text string that is output in the format that is like $999.99. For all other job codes found including null values, output the string: enter a valid job code.

Example 3:

TO CHAR ({Social Security Number}, '000 - 000 - 0000')

Given that there is a number field in the tab called Social Security Number, format the social security number as prescribed. In this example, the social security number stored in the number field (hence losing it's leading 0) as 89889989 would be output as a string that adds back the leading 0, like this: 089 - 88 - 9989.

🔔 Formulas can not mix the output of numbers and strings. This means, if you put a TO_CHAR function on one line of your formula, every scenario included in your formula must result in a string output.

🔔 If you format a string using TO_CHAR to an output that is incompatible with the result, the expected format for the field will display instead. Using example 2, if the resulting calculation was greater than $999.99 for the first two WHEN statements, then instead of showing the calculation it would show: $###.##

CASE statement operators

When building a CASE statement, the following operators are available and can be used when evaluating text strings, numbers and or dates.

CAST

The CAST function converts one datatype into another datatype. For example, if a field is a string, but you'd like to use it in a calculation, you will want to use this function. This will convert the value into a number that can be calculated.

The syntax format for this function is CAST({field to convert} as datatype). In the syntax examples below, you see a number in quotes at the start '1234'. This string can be replaced with a field from the tab in curly brackets. For example the second format option below (which would be used for SSN formatting where you have a field of the name SSN in the tab. This would be written as: CAST({Market Max} as numeric).

For the cast function to work, the data in the expression being evaluated must consist of the datatype specified. For example, you can not use CAST to convert the value A into the value 1. You can convert the string value of 1 into a numeric value of 1, so that it can be used for calculations.

🔔 It's important to note that any lookup fields that look up numeric fields are technically strings in GraceBlocks; they will need the CAST function applied to use this number in a formula.

There are two syntax options for this function:

Option 1: CAST (expression AS target_datatype ) --- example: CAST({Max salary} as numeric)

Option 2: expression :: target datatype --- example: '15 minute'::interval

TO_CHAR

The TO_CHAR function converts a date or number value into a text string. The syntax format for this function is TO_CHAR({field to convert}, 'format to apply'). In the syntax examples below, you see a number in quotes at the start '1234'. This string can be replaced with a field from the tab in curly brackets. For example the second format option below (which would be used for SSN formatting where you have a field of the name SSN in the tab. This would be written as: TO_CHAR({SSN}, '000-000-0000')

TO CHAR DATE FORMATS

In all examples, the date used as the {Updated on} value is 24-May-2023 11:41:12 am EST.

Math Operators

Where any number field, or lookup that outputs a number value, residing in the tab where the formula is being added, can be inserted as a number using the format {fieldname}. For example, {Salary} + {Annual Bonus}.  The result of this formula, where these two fields in a tab are number values, will produce a numeric result summing these two values for the record same record in that tab.

🔔 Once have created a numeric formula and saved it, you can edit it to another type of number formula, but you can not change it to display text or interval formats.

Math functions

Where any number field, or numeric lookup residing in the tab can be inserted as a number using the format {fieldname}. For example, ROUND({Price},2). The result of this formula, where the price is a numeric field in a tab, will produce a rounded number that is 2 decimal places for the same record in that tab. For example, a price of $3.46587222 would be rounded to $3.47.

Text string functions

Where any text-based field or text-based lookup residing in the tab can be inserted as a text for the function using the format {fieldname}. For example, CONCAT-WS(' - ',{Job Title},{JobID}). The result of this formula, where the Job Title and Job ID are both fields in the tab, will produce a result such as Project Manager - 019181 for the same record in that tab.  Note that when text is written into the formula that will be displayed, it needs to be enclosed in a single quote, but field names do not.

🔔 To specify a line break as your delimiter in functions you can use the following syntax: E/n. For example: STRING_AGG({Locations}, E'\n') could return an output that looks like this:

Texas
Idaho
Alabama

Date functions

Where any date-based field or date-based lookup residing in the tab can be inserted as a date for the function using the format {fieldname}.

Adding or modifying a function field

You can view the available properties for short text fields on the Edit field properties modal when you select Field type of Function. 

Properties of Function fields

After specifying a Field name (which controls how this field is referenced inside the tab) and that it will be of the type Function, you will specify the function command where you can leverage any supported operators and functions listed below. Then toggle on More field attributes to manage any additional settings which might apply. Complete the transaction at the end by clicking Save.

Important notes

🔔 Any lookup fields that look up numeric fields are technically strings in GraceBlocks;  they will need the CAST function applied to use this number in a formula. Here's an example using the CAST Function inside a larger formula.

Here are two examples using the CAST function with other functions to achieve a more complex result:

Example scenario: Let's say we've done market analysis for job titles in a tab, and we now want to pull that data into the employee's tab so we can compare the employee's current salary {Annual Salary} to where it falls relative to the market.  We want to first identify anywhere that the employee's salary is either over the {Market max} or under the {Market Min} value, and by how much.  Then we want to calculate the value for how much the employee is over by as a % of the {Market Median}. For this example, the market max, median, and min values are all specified in a market analysis tab and looked up in the Employees tab where this example is being built).

Step 1: We need a new field {Over or Under} that identifies how much are we over or under the market in cases where the employee's salary is outside the market range identified. 

This will be done as a case statement that looks like this:

CASE
WHEN {Annual Salary} > CAST({Market Max} as numeric) then {Annual Salary}-CAST({Market Max} as numeric)
WHEN {Annual Salary} < CAST({Market Min} as numeric) then {Annual Salary}-CAST({Market Min} as numeric)
end

CASE
WHEN {Number to compare} > CAST({Field_8} as numeric) then {Number to compare}-CAST({Field_8} as numeric)
end

Step 2: Now we'll add a field {Percent Over or Under Median} that calculates the {Over or Under} value as a % of the median salary and is formatted as a % value.  

       Part 1:  the formula:    {Over Max or Under Min} / CAST({Market Median} as numeric)

Part 2: format the field: Then once this value is calculated as a number, formate the property of this field using the Numeric format options to be displayed as a Percent value with # of decimals = 2.

Example Result:

🔔It is possible to use formulas to construct web forms that are pre-populated with values by following the steps below.  This can be done to create new records or update existing records.  The steps for both scenarios are outlined below:

Creating new records: This approach works well when you want users to easily create records in a different but related tab. 

1. Build the web form you wish to pre-populate with values.  Copy the URL to the form and put it into notepad.
2. Create a Concat formula that includes the values that you need as tokens in notepad.  
3. Paste it into a formula field for a tab where you'd like this action to be available.
4. Select URL under More field attributes > Formatting Options.
5. Toggle on Convert URL to button.
6. Enter the name for your button.

🔔Related records in the form can be pre-selected and populated using the system ID token. 

Example Formula:

Concat('https://dev.graceblocks.com/form?x=ODl8Z3JhY2VibG9ja3MtMXwyMTM=&field-5=',{System ID})

Example Result:

https://dev.graceblocks.com/form?x=ODl8Z3JhY2VibG9ja3MtMXwyMTM=&field-5=1

Updating existing records: This approach works well when you want users to easily update a record while you are working with it

1. Build the web form you'd like to use for updating your record.  Copy the URL to the form and put it into notepad.
2. Go to web form settings and toggle on: Update vs. create new record when duplicates are identified.
3. Select System ID as your DUPLICATE CRITERIA and save the updated settings.
4. Create a Concat formula that adds this text to the end of your record: GENERATE_FORM_ID({System ID}),'&prefill=true'
5. Paste the resulting formula it into a formula field on the tab where you will be updating records.
6. Select URL under More field attributes > Formatting Options.
7. Toggle on Convert URL to button.
8. Enter the name for your button.

Example Formula:

CONCAT('https://dev.graceblocks.com/form?x=MTAxfGdyYWNlYmxvY2tzXzAxfYV5Mg==&id=',GENERATE_FORM_ID({System ID}),'&prefill=true&internal=true')

Example Result:

https://my.graceblocks.com/form?x=MTAxfGdyYWNlYmxvY2tzXzAxfDY5Mg==&id=MTU4Nzgy&prefill=true&internal=true 

(Note, only include &internal=true if the form is to be clicked while the user is logged into GraceBlocks. This added syntax in the url will strip the outer boarders from the form as well as the form icon from the tab.) 

🔔 Formula fields can not be converted into other field types.  You must always create them from scratch.  Other field types be converted into functions, however, doing this will result in abolishing all of the data in that field and repurposing it to display the value resulting from the formula.

🔔 Editing functions associated with a formula field is possible, however, the new formula must match the data type of the original formula.  For example, number formulas can be converted into other number formulas provided they are the same type of number. For example, an ABS number which is an integer format can not be converted into a SQRT formula which would have decimals.

🔔 Calculating the time in hours to a 2-decimal number precision can be done using a formula that subtracts the time between two time periods.  

ROUND(CAST(EXTRACT(EPOCH FROM({Stop}-{Start})) / 3600 AS NUMERIC), 2)

Explanation breakdown: We have two timestamps {Stop} and {Start} in hours, rounded to two decimal places. Let's break down the components step by step:

1. EXTRACT(EPOCH FROM({Stop}-{Start})): This part calculates the difference between the two timestamps {Stop} and {Start} in seconds. The EXTRACT function extracts a specific component (in this case, seconds) from a timestamp difference.
2. / 3600: The result obtained from the previous step, which is the time difference in seconds, is divided by 3600 to convert it to hours. This is because there are 3600 seconds in an hour.
3. CAST(... AS NUMERIC): The result of the division is then cast (converted) to a numeric data type. This might be necessary depending on the data types of the {Stop} and {Start} timestamps. 
4. ROUND(..., 2): The numeric result from the previous step, which represents the time difference in hours, is rounded to two decimal places using the ROUND function.

The entire formula is then used to check if the calculated and rounded time difference in hours is equal to something (not provided in your example). Presumably, you would replace the ... with a specific value to which you're comparing the calculated time difference.

🔔 If is possible to calculate a duration of time by subtracting one date from another. If the date is a date/time field - the interval will show in hh.mm.ss format eg: 5:22:21 which represents 5 hours, twenty-two minutes, and twenty-one seconds.  If the duration is two whole date fields then the value will return a number of days, eg: 2 days.  When exporting this data via API, durations will export as text values.  A formula to convert this value to something numeric should be used if a number value is desired via API export.

🔔 Functions reference the text name of the field.  If the name of the field changes, the formula will still work.  For example, if you have a field called "Cost" and you change the field name to "Price," the formula will still work. However, if you try to edit this formula, you will need to change the field label to the new value. If you try to save a function that names a field that has been changed, the system will throw an error and will not let you save your changes.

🔔 The period character is special in the formatting of a number to a string when using the TO_CHAR Function. It can only be used once in the formatting. You can use other characters however, like a comma or a hashtag, but not more than one period.

🔔 Most functions are Postgres native functions, but some are custom-built inside of GraceBlocks. Where provided, click the hyperlink in the first column of these tables to learn more about a specific function directly from the Postgres documentation for more examples.