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 Basic 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 Equation fields 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, you can utilize another type of function, 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 is the desired output if the condition is true, and the third is the desired output if the condition is false. Every IF statement should follow the same formatting 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 parenthesis. 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 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. Only orders between $100 and $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) Using a single quote and not a double quote is recommended. 

2) Build each equation, validate it, and 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 remember that equations are run in the order 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 will trigger the reorder alert for each product when met.

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 Sum formula field to calculate the sum of the incoming delivered products
2. A 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 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.

1. You can Name this Sum formula field, "Delivered Products".

2. Link it to your Incoming Orders data Table with the linked field Product Name.

3. Select the Incoming Quantity field.

4, 5, 6. Select the Order Status field, then is and finally select Delivered from the status type drop down.

 

Next, add the second Sum formula field to the Products data table

1. You can name this sum complex formula field, "Outgoing Products".

2. Configure it to sum the outgoing products from the Outgoing Products data table.

3. Select the Number field "Outgoing Quanitity". 

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 Equations category.

Then, configure the Basic Formula field to calculate the real-time inventory by adding the Delivered Products Sum field, the minus sign 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.

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.

 

---

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.

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.

Sample Equations

• Date Examples

• Number Examples

• Text Examples

• Multi-Function Examples

Equation Limitations

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

Troubleshooting IF Statements

Other factors to consider. Being that the fields often can be date, number or string, if using math with decimal numbers, wrap the value in quotes. For example: 

This won't work: 
IF({# of Managers} = 2, {Store Quarterly Bonus} * 0.50, "Add Nested IF here")

But this will (notice the "" around the number 0.50:
IF({# of Managers} = 2, {Store Quarterly Bonus} * "0.50", "Add Nested IF here")