Equation Fields (Category)

Text Formula

Text Formula fields are basic field types that can combine multiple fields into a single output using a text formula.

Sample Text Formula Field:

For instance, if you have a Property Management application with properties and units, you may wish to combine the Property Name field with the Unit Name field to create a new field with the single output of the full Property and Unit name (example: combine "Property A" field with "Unit D5" field to form a new field with the single output of "Property A Unit D5"). 

TIP: Text formulas are similar to "CONCAT" in Excel. 

Let's explore how to create a text formula field such as the example above.

Creating a text formula field

To add a new text formula field to your data table, click on the Equation field type category on the Add Field menu and select the Text Formula field type from the drop-down list.

When adding a new Text Formula field, there are several settings you can customize such as those highlighted in the image below.

1. Name- where you can title your Text Formula field. For instance, "Property and Unit Name" or "Trip Route".
   
   
2. Description- where you can optionally provide a brief description of this field for your own reference. 
   
   
3. Formula- this is where you can define the text formula for this field. The Formula section is divided into three parts:
   
   

Let's discuss how to use the Formula section to create text formulas for your text formula fields.

For instance, in the example listed above we had a Property Name field, a Unit Name field, and we combined these two fields to create a new text formula field titled Property and Unit Name.

The GIF below demonstrates the text formula that was used to create this Property and Unit Name text formula field. As you can see in the GIF, the Property Name field is added to the formula, then a custom text of a space character is added, and then the Unit Name field is added to form a single output of "'Property Name' 'space' 'Unit Name'".

 

Let's walk through another instance of how to create a Text Formula field.

Say you have an Origin field and a Destination field and would like to combine these two fields to create a new field with a single output of your Route (origin to destination). You can accomplish this with a Text Formula field. To create your new Route field, you can create a new Text Formula field, title it "Route", and configure the text formula to combine your Origin and Destination fields into a single output. 

As illustrated in the GIF below, you can create your Text Formula field by first clicking on the +Add Field button to add the Origin field. You can then enter the custom text of "'space character' 'to' 'space character'" and clicking on the +Add button to add this custom text. Lastly, you can click on the +Add Field button once again to add the Destination field for a final output of "Origin to Destination". You can then click Save to save your Route Text Formula field.

To test this Text Formula field in action, we can create a simple form in the Page Builder using the Origin and Destination fields. 

When we click Save, we can see our Text Formula field in action within a table component on the live app.

Basic Formula

Basic Formula fields can be used to calculate automatically generated values by combining numeric fields. 

For instance, say you have an Orders data table with a Quantity field and a Price field. To calculate the Total Cost of each order, you can create a Basic Formula field with a formula of "Quantity x Price". 

Unlike Complex Formulas, Basic Formulas can not be used to calculate values from connected records.

Basic formulas can only be used to calculate numeric fields.

Sample Basic Formula Field:

Creating a Basic Formula Field

To add a new Basic Formula field to your data table, click on the Equation field type category on the Add Field menu and select the Basic Formula field type from the drop-down list.

When creating a Basic Formula 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 Basic Formula field. For instance, "Order Subtotal" or "Remaining Vacation Days".
   
   
2. Description- this is where you can optionally provide a brief description of your Basic Formula field for your own reference. 
   
   
3. Formula- this is where you can create your Basic Formula by combining numeric fields to create a new, automatically calculated field value.
   
   Let's first define the remaining Basic Formula settings of Currency Type, Format, and Decimal Places and then we'll return to this Formula section to demonstrate how to use this section to create a basic formula with numeric fields. 
   
   
4. Currency Type- this is where you can choose a currency type to display within your Basic Formula field values. At default, the currency type for Basic Formula fields is set to No Currency. However, if you are creating a basic formula to calculate a currency value you may wish to change this setting to a currency type of your choice, such as "$- USD" or "€- EURO". 
   
   
5. Format- this is where you can define the location of the currency symbol as it will display within your basic formula currency values. At default, the currency symbol format is set to Prefix, which means that the currency symbol will be displayed before the currency value at default.
   
   
   
   You can change the format of your currency symbol location to either None (will not show currency symbol) or Suffix (currency symbol will appear after the currency value).
   
   
6. Thousands Delimiter- this is where you can define which character you wish to use as a thousands delimiter to separate groups of thousands when viewing your basic formula values. At default, the thousands delimiter setting for basic formula fields is set to None, meaning your currency values will display with no thousands delimiter at default and will display as such:
   
   
   
   If you wish to change your thousands delimiter setting and choose a delimiter character, you can choose either a comma, period, or space character to separate groups of thousands within your currency values. 
   
   For instance, this is how your currency values will display if you change your thousands delimiter setting to Comma:
   
   
   
   
7. Decimal Places- this is where you can define how many digits you wish to display after the decimal within your basic formula values. If None or 0 is selected, values will display as whole digits. At default, the decimal place setting for basic formula fields is set to 0 and values will be displayed and stored as whole digits. 

Creating a Basic Formula

Now that we've defined all the settings that you can customize when creating a new Basic Formula field, let's return to the Formula setting to demonstrate how to create a basic formula. 

The Formula section of the Basic Formula field can be broken down into three key components as follows:

A. This is where you can add numeric fields and custom numeric values to create your basic formula. 

B. This is where you can delete and/or drag and drop fields that have been added to your formula to arrange your desired field order. 

C. This is where you can preview the output of your formula as it will display within your components. 

An example: Creating a Basic Formula field to calculate employees' remaining vacation days

For instance, if you have an Employee Management application and you would like to generate an automatically calculated value of how many remaining vacation days is available for each employee, you can accomplish this with a Basic Formula field.

To create a Basic Formula field, you must first have at least two numeric fields to work with. In this example, imagine you have two fields-"Starting Vacation Days" and "Used Vacation Days"- and you would now like to create a third automatically generated field to calculate employees' remaining available vacation days. To do this, you would create a Basic Formula field with the formula of "Starting Vacation Days" "-" "Used Vacation Days" to calculate a new numeric field value of "Remaining Vacation Days". 

To create this basic formula, select the Basic Formula field type from the Equation field type category.

Name your Basic Formula field "Remaining Vacation Days" and click on the +Add Field button to select your first field from the drop-down menu. 

Select the "Starting Vacation Days" field to add this field to your formula.

You can then select the "-" function to add this function to your formula.

Then click on the +Add Field button again to add the "Used Vacation Days" field to your formula. Now that your formula is complete, click Save to save this new Basic Formula field.

You can test your new Basic Formula in action by creating a simple form in the Page Builder using your Starting Vacation Days and Used Vacation Days fields. On the live app, enter values within the form and click Save to save the form. 

By creating a simple table component within the Page Builder and previewing it on the live app, you can view your basic formula in action. As you can see, your Basic Formula field, titled "Remaining Vacation Days" displays the automatically calculated value of the result of your Basic Formula of Starting Vacation Days - Used Vacation Days.

Nesting Basic Formulas

By default, existing basic formulas can not be nested or added inside another basic formula. However, if you need to reference an existing basic formula within your new Basic Formula field, you can accomplish this by recreating your existing formula within parentheses of your new Basic Formula field. 

For instance, say you have an existing basic formula of Quantity x Price to calculate the Sub Total of an order.

If you now wanted to create a new Basic Formula field to calculate the Total with Shipping for each order, you would not be able to nest your existing Sub Total Basic Formula field within your new Total with Shipping formula. What you could do, however, to accomplish the same goal is recreate your Sub Total formula within parentheses of your Total with Shipping formula. Simply recreate your Sub Total formula of Quantity x Price within parentheses and add $5 shipping to each order by adding the + function and a custom numeric value of 5. 

You have now created a new Basic Formula field referencing an existing formula field. 

Count

Count fields are complex formula fields that are designed to count records of a connected data table.

Unlike Basic Formulas, Complex Formulas such as Count fields work with connected records. 

For instance, say you have an Employees data table, a Tasks data table, and a connection field joining these two data tables which assigns each task to an employee. With a Count field, you can create a complex formula to count the Total Tasks assigned to each employee at any given time.

Sample Count Field:

Creating a count field

To add a new Count Field to your data table, click on the Number field type category on the Add Field menu and select the count field type from the drop-down list.

When creating a Count 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 Count field. For instance, "Total Jobs" or "Assigned Projects".
   
   
2. Description- this is where you can optionally provide a brief description of this field for your own reference.
   
   
3. Table- this is where you can select the data table from which you would like to count records.
   
   
4. Currency Type- this is where you can choose a currency type to display within your Count field. At default, the currency type for Count fields is set to No Currency. However, if you are creating a Count field to calculate a currency value you may wish to change this setting to a currency type of your choice, such as "$- USD" or "€- EURO". 
   
   
5. Format- this is where you can define the location of the currency symbol as it will display within your Count field values. At default, the currency symbol format for Count fields is set to None, which means that no currency symbol will be displayed at default.
   
   If desired, you can change the location formatting of the currency symbol to display either before or after your currency values. For instance, you can change the currency symbol formatting to Prefix which will display the currency symbol before your currency values as illustrated in the image below.
   
   
   
   
6. Thousand Delimiter- this is where you can define which character you wish to use as a thousands delimiter to separate groups of thousands when viewing your currency values. At default, the thousands delimiter setting for Count fields is set to None, meaning your currency values will display with no thousands delimiter at default and will display as such:
   
   
   
   If you wish to change your thousands delimiter setting and choose a delimiter character, you can choose either a comma, period, or space character to separate groups of thousands within your currency values. 
   
   For instance, this is how your currency values will display if you change your thousands delimiter setting to Comma:
   
   
   
   
7. Decimal Places- this is where you can define how many digits you wish to appear after the decimal within your count field values. At default, the decimal place setting for Count fields is set to None, meaning count field values will display and be stored as whole digits at default. 

An example: Creating a Count field to count the total jobs for each customer.

For instance, say you have a Customers data table, a Jobs data table, and a connection field joining these two data tables which assigns each job to a customer. With a Count field, you can create a complex formula to count the Total Jobs belonging to each customer at any given time.

To create this Total Jobs Count field, select the Count field type from the Numbers field type category on the Add Field menu within your Customers data table. 

Name your Count field "Total Jobs" and configure your formula to Count Records from the Jobs data table (where each job is assigned to a customer). Click Save to save your new Total Jobs Count field.

You have just added a Total Jobs Count field to your Customers data table that will count the total jobs for each customer. You can view the results of your count field in action via the GIF below where you can see the Total Jobs field for Customer John Simonds dynamically changing each time a new job is assigned to this customer.

You can further customize your Total Jobs Count field by adding specific data conditions. For instance, you can add a condition to only include completed jobs within the Total Jobs count. 

To do this, click on the Add Data Conditions button within your Total Jobs Count field and create a condition to only count jobs where the "Customer" "is" "Completed". Click save to implement this data condition within your count field.