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
  • Fundamentals
  • Math operators
  • Math functions
  • Text string functions 
  • Date functions
  • Adding or modifying a formula field

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 {Price} >'1000' then 'Large'
WHEN {Price} BETWEEN '500' AND '999' then 'Medium'
WHEN {Price} = '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},  '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.

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:    round ( ( {Over Max or Under Min} / CAST({Market Median} as numeric)

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

Example Result:

🔔 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.

🔔 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.