Equation Fields

What are equation fields?

Equation Fields enable more complex formula functions to be run on your values.

For instance, you can create an equation formula to extract the name of the month from your date values.

Equation fields can be used in combination with Formula fields and can be nested within other equation fields.

Within this article we will explore the different functions you can use to create equations on your values and we will also demonstrate how to create equations such as the sample equation listed above. 

Equation video guide clip

Watch the brief equations overview video below to get a general insight of how you can use equations to empower your field values.

 

Creating An Equation Field

To create an equation field, select the Equation field type from the Number field type category on the Add Field Menu within the data table that you would like to add this equation field.

When creating an equation field, there are several settings you can customize such as those highlighted in the image below.

1. Name- this is where you can name your equation field. For instance, "Day of Month" or "Time Difference".
   
   
2. Description- this is where you can optionally provide a brief description of this field for your own reference. 
   
   
3. Equation- this is where you can define your equation by selecting fields from the Add Field drop-down list, selecting your desired Math, Text, or Date function, and configuring your fields and functions within the Equation Editor. The Equation Editor refers to the box in which you enter your equation (labelled "Enter Equation" with placeholder text). 
   
   
   
   When creating a new equation, there are several formatting rules that apply across all fields and functions:
   
   A. When you select a function from either the Math, Text, or Date function drop-down list, the function will be added to the Equation Editor followed by a pair of parentheses. For instance, if you select the UPPER function from the Text Function drop-down list, it will be added to the Equation Editor as "UPPER()". 
   
   
   
   B. When you select a field from the Add Field drop-down list, the field will be added to the Equation Editor within braces. For instance, if you select the Customers field from the Add Field drop-down list, it will be displayed within the Equation Editor as "{Customer}".
   
   
   
   C. To create a valid equation, all fields must be placed within the parentheses of the function. 
   
   
   
   If you are creating an equation with multiple fields, all fields must be placed within the parentheses. In general, your entire formula must be included within the parentheses.
   
   
   
   Below is a GIF demonstrating successful equation formatting following the above rules. The example used in the GIF is of an equation to display customers' names in Uppercase formatting. As you can see, the UPPER function is added, the Customers Field is added, and then the Customers field is placed within the function parentheses, creating a valid equation. 
   
   
   
   As you can see on the live app, this equation field was successfully created.
   
   
   
   Note: Once you enter your equation formula, you must click on the Validate Equation button that will confirm whether or not your entered equation is formatted correctly and deemed valid. If your equation is deemed invalid, you will see an "Invalid Equation" error message and will not be able to save your equation as entered. Only once you change your equation to a correct formatting and receive a "The Equation is Valid" confirmation message will you be able to save your equation.
   
   
   
   
4. Output Type- this is where you can choose the field output type for the resulting value of your equation. You can choose to display the results of your equation as a text value, number value, or date value. 
   
   The output type you choose is significant as it will affect your ability to filter records within your components. For instance, if you would like to create a record rule to update your current record based on a certain condition, fields saved as a text output will display text-related conditions such as "contains" or "starts with" while fields saved as a date output will display date-related conditions such as "is today" or "is before today".
   
   

Now that we have defined the universal settings you can customize when creating any equation field, let's explore the different functions you can use to create various types of equations. 

Function Types

As discussed above, there are three types of functions you can utilize within your equations:

• Math Functions
• Text Functions
• Date Functions

Additionally, there is another type of function you can utilize specifically when creating conditional equation fields called the IF function. 

Let's discuss each type of function, beginning with the IF function.

IF Statements

The IF function is used within conditional equation fields to generate a result based on a given condition. IF Statements refer to the IF(formula) statement entered within the equation editor.

IF Statements expect three arguments as input with each argument separated by a comma. As illustrated in the image below, the first argument is the condition, the second argument is the desired output if the condition is true, and the third argument is the desired output if the condition is false. Every IF statement should follow the same formatting as illustrated in the image below.

For instance, say you have an Orders data table with a Total Cost field and you would like to create a new field with an updated total cost that adds a $5 shipping fee for every order with a total cost of less than or equal to $100. Meaning, if the total cost of the order is under or equal to $100, add a $5 shipping fee, otherwise keep the total cost as-is. You can accomplish this by creating a new equation field and within the Equation Editor, type "IF" followed by a left parentheses. Then enter the three arguments of (condition, true, false) as illustrated below:

Let's breakdown the 3 arguments of this IF Statement with the following illustration:

The following image displays the results of this conditional IF equation. As you can see, only Total Cost values under or equal to $100 have been calculated to a new value of plus +$5. Total Cost values above $100 remain the same.

 

Argument Formatting

Condition, true, and false arguments can include fields, static values, math operations (addition, subtraction, multiplication, etc.), other functions, and comparison operators.

Adding Comparison Operators to arguments within an IF Statement

Below is a list of common comparison operators you can use within arguments of an IF Statement:

• a>b (a is greater than b)
• a<b (a is less than b)
• a>=b (a is greater than or equal to b)
• a<=b (a is less than or equal to b)
• a=b (a equals b)
• a<>b (a does not equal b)

Adding functions to arguments within an IF Statement

You can add functions to condition, true, and false arguments within an IF Statement.

For instance, if we would like to track which of our customers are using a "tadabase.io" email address, we can create an equation field using the following IF Statement:

Within the condition argument of this IF Statement, we are extracting the value from after the @ symbol in the domain name and if the condition is equal to "tadabase.io", set the value to "Valid", otherwise set the value to "Invalid". 

At this time, spaces can not be used in the True or False output. For example: 'Not Valid' will be converted to 'NotValid'

You can use the html code & nbsp; for spaces instead, but in a drop-down it will show the code and not a space. 

Below are the results of this IF Statement in action. As you can see in the sample table component, customers using a "tadabase.io" domain are tracked as "Valid" within the Valid Domain field (our equation field) while customers using a non "tadabase.io" domain are tracked as "Invalid" within the Valid Domain field.

 

Multiple IF (ELSEIF) Statements

You can now add multiple IF statements within an equation by adding the second if statement inside the False argument. The second IF statement will work and behave exactly like any other IF Statement. 

 

Adding AND/OR in Conditions

If your condition requires you to check for multiple conditions using AND/OR you can do so with the || for OR and && for AND.

For example if we borrow from the example above, let's say we want to add $5 fee if the Total Cost is higher than 100 AND is lower than 200. Meaning only for orders between $100 to $199 should incur the extra fee. 

IF({Total Cost}>=100 && {Total Cost}<200, {Total Cost}+5, {Total Cost})

Here are a few more things to keep in mind:

1) It's recommended to use a single quote and not double quote. 

2) Build each equation validate it then add each equation into the false part of the parent equation. This will help you troubleshoot it. 

 

Nesting existing equations within new IF Statements 

Existing equations using IF Statements can be nested within new IF Statements. However please keep in mind that equations are run in the order that they are displayed in your data table. If the first equation depends on the last equation, your data will not be returned correctly.

 

Using IF Statements to create inventory reorder alerts

You can use an IF Statement equation to create automated inventory reorder alerts when products fall below their custom-defined minimum inventory levels. You can also trigger auto-reorder alerts to send automated emails to connected suppliers to reorder low-inventory products with custom-defined reorder invoices.

For instance, say you have an Inventory Management App with five data tables:

1. Products data table (tracking your products)
2. Incoming Orders data table (tracking incoming product deliveries)
3. Outgoing Orders data table (tracking outgoing products to customers)
4. Customers data table (tracking customers)
5. Suppliers data table (tracking product suppliers)

Within the Products data table, add a number field to track the minimum quantity for reorder amount for each product.

Then, assign each product's minimum inventory quantity level that when met, will trigger the reorder alert for each product.

Real-time inventory = the sum of the incoming delivered products - the sum of the outgoing products. Therefore, you will now add three formula fields to the Products data table:

1. A complex sum formula field to calculate the sum of the incoming delivered products
2. A complex sum formula field to calculate the sum of the outgoing products
3. A basic formula field to calculate the real-time inventory (total incoming delivered products - total outgoing products)

To add the first complex sum formula field, select the sum field from the Add Field menu within the Products data table.

Then, configure the sum formula to calculate the sum of the incoming products for which the order status is "Delivered" from the Incoming Products data table. You can name this sum complex formula field, "Delivered Products".

Next, add the second complex sum formula field to the Products data table and configure it to sum the outgoing products from the Outgoing Products data table. You can name this sum complex formula field, "Outgoing Products".

And thirdly, add the third formula field, the basic formula field to calculate the real-time inventory, by selecting the Basic Formula field from the Add Field menu.

Then, configure the basic formula field to calculate the real-time inventory by adding the Delivered Products sum complex formula field, the - operator, and the Outgoing Products sum complex formula field to create the formula: Delivered Products - Outgoing Products. You can name this basic formula field, "Real-time Inventory".

Once you have calculated the real-time inventory, it is finally time to create the IF Statement that will calculate the automated reorder alert for each product based on if the real-time inventory is less than the minimum quantity for reorder. To add the IF Statement equation, select the equation field from the Add Field menu.

Then, name this equation field "Reorder" and configure the equation to calculate IF the real-time inventory field is less than the minimum qty. for reorder field, display "yes" is this statement is true, otherwise display "no".

Now, as you can see within the Products data table, the Reorder field automatically calculates whether each product is below the minimum inventory level and needs to be reordered. 

You can then trigger this Reorder field with custom display rules, action links, and record rules to automate reorder alerts to employees, automate emails to connected product suppliers with custom-defined reorder invoices when a product has been added or updated with its minimum inventory level, and automate custom status indicators for products that need to be reordered.

For instance, as you can see in the Products table component on the live app, products that need to be reordered display in red with a red alert icon and the Reorder link directs users to an automated reorder form that sends an automated email to the product's supplier with a custom-defined reorder invoice.

 

Math Functions

For a comprehensive list of equations using math functions, please click here. 

Text Functions

For a comprehensive list of equations using text functions, please click here. 

Date Functions

For a comprehensive list of equations using date functions, please click here. 

 

Equation Types

There are three types of equations you can create:

1. Date Equations
2. Number Equations
3. Text/String Equations

Let's explore each of these three equation types and define the various equations you can create with date, math, text, and conditional IF functions.

Date Equations

Date equations can be created using date functions. 

Date Functions 

Below is a comprehensive list of all the date functions you can use to create date equations.

Date Equation Examples

Listed below are several examples of date equations demonstrating date functions in action.

DAYOFMONTH

The DAYOFMONTH function generates the day number of the month given a date value. 

Below is a sample equation using the DAYOFMONTH function.

Below are the results of this sample equation using the DAYOFMONTH function.

 

DAYOFWEEK

The DAYOFWEEK function generates a numeric representation for the day of the week that the given date falls on. The generated value will be a number from 1-7 where 1 is Sunday and 7 is Saturday.

Below is a sample equation using the DAYOFWEEK function.

Below are the results of this sample equation using the DAYOFWEEK function.

 

DAYOFYEAR

The DAYOFYEAR function generates a numeric representation for the day of the year which the given date falls on. The generated value will be a number from 1-366.

Below is a sample equation using the DAYOFYEAR function.

Below are the results of this sample equation using the DAYOFYEAR function.

 

LAST_DAY

The LAST_DAY function generates the last date of the month of the given date value. The generated value will be a date format.

Below is a sample equation using the LAST_DAY function.

Below are the results of this sample equation using the LAST_DAY function.

 

YEAR

The YEAR function generates the year of the given date. The output will be a single numeric representation of the year value.

Below is a sample equation using the YEAR function.

Below are the results of this sample equation using the YEAR function.

 

MONTH

The MONTH function generates the month of the given date within its numeric value. The output will be a single numeric representation of the month value.

Below is a sample equation using the MONTH function.

Below are the results of this sample equation using the MONTH function.

 

MONTHNAME

The MONTHNAME function generates the name of the month of the given date. The output will be a text representation of the month value.

Below is a sample equation using the MONTHNAME function.

Below are the results of this sample equation using the MONTHNAME function.

 

NOW

The NOW function generates the current time. This function, unlike the other Date Functions, is not to be used with any fields as input (as illustrated in the image below). The value is saved upon record creation and updated upon saving any type of edit.

Below is a sample equation using the NOW function.

Below are the results of this sample equation using the NOW function.

 

WEEKOFYEAR

The WEEKOFYEAR function generates the week number that the given date falls within. The output will be a single numeric representation of the week.

Below is a sample equation using the WEEKOFYEAR function.

Below are the results of this sample equation using the WEEKOFYEAR function.

---

Number Equations

Number equations can be created using math functions.

Number Functions

Below is a comprehensive list of all the math functions you can use to create number equations.

Number Equation Examples

Listed below are several examples of number equations demonstrating math functions in action.

ABS

The ABS function generates the absolute value of your numeric field. 

Below is a sample equation using the ABS function.

Below are the results of this sample equation using the ABS function.

 

CEIL

The CEIL function generates the ceiling value of your field. The expected input for this function is a single numeric value.

Below is a sample equation using the CEIL function.

Below are the results of this sample equation using the CEIL function.

 

FLOOR

The FLOOR function generates the floor value of your field. The expected input for this function is a single numeric value.

Below is a sample equation using the FLOOR function.

Below are the results of this sample equation using the FLOOR function.

 

ROUND

The ROUND function generates a value that is rounded, by default, to the nearest whole number.

This function has two different options for input:

1. Input with a single numeric value- value will be rounded to the nearest whole number (ROUND: Use Case 1)
2. Input with two numeric values- the first value in the function will be rounded to the nearest nth decimal place (ROUND: Use Case 2) 

ROUND: Use Case 1

Equation:

Results:

ROUND: Use Case 2

Equation:

Results where n = 3:

Results where n = -1:

 

SQRT

The SQRT function generates the square root of your numeric field value. The expected input for this function is a single numeric value.

Below is a sample equation using the SQRT function:

Below are the results of this sample equation using the SQRT function:

---

Text Equations

Text equations can be created using text functions.

Text Functions 

Below is a comprehensive list of all the text functions you can use to create text equations.

Text Equation Examples

Listed below are several examples of text equations demonstrating text functions in action.

CHAR

The CHAR function generates a character from a numeric value. The result is going to be based on the corresponding character of the numeric input read as an int ASCII code. The input should be a value from 0-255. For a character guide, please click here.

Below is a sample equation using the CHAR function.

Below are the results of this sample equation using the CHAR function.

 

CONCAT

The CONCAT function concatenates the given comma-separated inputs. This function requires at least one value, however, two or more should be present for there to a difference between the input and the produced value.

There are two main types of use cases for this function:

1. Input with a single field combined with static text (CONCAT: Use Case 1)
2. Input with multiple fields combined with static text (CONCAT: Use Case 2)

CONCAT: Use Case 1

Below is a sample equation using the CONCAT function with a single field combined with static text. 

Taking a closer look at this sample equation, we can see that (a) is the field and (b) is the static text. When adding text into your function, ensure that it is within single quotes.

Below are the results of this sample equation using the CONCAT function with a single field combined with static text. 

CONCAT: Use Case 2

Below is a sample equation using the CONCAT function with multiple fields combined with static text. 

Taking a closer look at this sample equation, we can see that (a) and (c) are the fields and (b) is the static text. This example shows the use of CHAR to place a space between the two fields. No more than 254 total fields and static text instances can be used within the CONCAT function.

Below are the results of this sample equation using the CONCAT function with multiple fields combined with static text. 

 

LOWER

The LOWER function generates the lowercase representation of your given values. The expected input for this function is a single field.

Below is a sample equation using the LOWER function.

Below are the results of this sample equation using the LOWER function.

 

UPPER

The UPPER function generates the uppercase representation of your given values. The expected input for this function is a single field.

Below is a sample equation using the UPPER function.

Below are the results of this sample equation using the UPPER function.

 

REVERSE

The REVERSE function generates the reverse representation of your given values. The expected input for this function is a single field. 

Below is a sample equation using the REVERSE function.

Below are the results of this sample equation using the REVERSE function.

 

LENGTH

The LENGTH function generates a numeric representation of the number of characters in your given values. The expected input for this function is a single field. 

Below is a sample equation using the LENGTH function.

Below are the results of this sample equation using the LENGTH function.

 

REPLACE

The REPLACE function replaces a defined set of characters with another defined set of characters within your given values.

The REPLACE function expects three values as input. Referencing the image below, (a) is the field that will display the replacement of value (b) to value (c), (b) is the text to replace, and (c) is the text that will replace the prior (b) value. You may use fields for values B and C or custom text. Custom text must be enclosed within single quotations (') and all values must be separated with a comma. As a reminder, the entire formula must be enclosed within the function parentheses.

For instance, say you have a Sales Rep field and you would like to replace every entry of "Edward" with "Eddie". You can accomplish this with a REPLACE function as illustrated below.

The result of this equation will appear as such:

At this time, the REPLACE function can only be used within field values that do not contain spaces.

 

Equation Limitations

Equation fields are read-only and their values can not be set or updated within a form, import, or record rules.