Overview

This page contains information about how to use the formula builder within the Calculated control and conditional display areas of nFORM. 

Formula Essentials

Introduction to Formulas

Formulas are powerful tools within Windsor applications that enable you to automate tasks, perform calculations, and manipulate data. They are essential for creating dynamic and interactive forms that adapt to user input and provide meaningful insights.

Formula Syntax

Formula syntax is the structure and rules that govern how formulas are written and interpreted. It consists of elements such as operators, operands, and functions.

Operators

Operators are symbols that perform operations on operands. Common operators include:

(Click an operator to be taken to an example) (ANCHOR)

• Addition (+)

• Subtraction (-)

• Multiplication (*)

• Division (/)

• Equality (==)

• Inequality (!=)

• Less than (<)

• Greater than (>)

• Less than or equal to (<=)

• Greater than or equal to (>=)

• Logical AND (&&)

• Logical OR (||) (jump to example)

• Logical NOT (!)

• Ternary Operator ( ? )
  

Operands

Operands are the values or expressions that operators act upon. They can be numbers, strings, dates, or references to other cells or fields.

Functions

Functions are built-in routines that perform specific tasks, such as calculations, data manipulation, and text processing. They can take arguments (inputs) and return values (outputs).

Formula Building Techniques

Performing Null Checks

Null checks ensure that formulas handle missing or empty values appropriately. Common functions for null checks include:

• ISNULL(): Checks if a value is null.

• ISBLANK(): Checks if a value is null or an empty string.

• COALESCE(): Replaces a null value with a specified alternative value.

Using the select() Function to Select Values Based on Cases

Select() Function Syntax

The select() function is used to select a value from a set of possible values based on a given case. It takes three arguments:

1. str(VARIABLE): The variable that holds the value to be evaluated.

2. case1: An object containing a case identifier and a corresponding value.

3. case2: An object containing a case identifier and a corresponding value.

4. (Optional) caseN: Additional case objects can be added to the function for more possible values.

Example:

select(str(`FEE_CATEGORY`),
  {case: "1", value: "PETROL BULK STORAGE REG 1,101=<2K GAL"},
  {case: "2", value: "PETROL BULK STORAGE REG 2,001=<4,999 GAL"},
  {case: "3", value: "PETROL BULK STORAGE REG 5K=< 399,999 GAL"})

Explanation:

1. The select() function evaluates the value of the variable FEE_CATEGORY.

2. If the value of FEE_CATEGORY matches the case identifier in any of the case objects, the corresponding value from that case object is returned.

3. If the value of FEE_CATEGORY does not match any of the case identifiers, the function returns an empty string.

Utilizing the LOOKUP() Function

The LOOKUP() function retrieves a value from a specified table based on a given identifier. It's commonly used for data lookup and retrieval.

Optimization Strategies

Optimizing formulas enhances their performance and efficiency. Here are some optimization strategies:

• Use CONTAINS() instead of multiple TAG checks: The CONTAINS() function checks if a value exists within a list, while multiple TAG checks involve checking each tag individually. CONTAINS() is generally more efficient.

Rounding Numbers

Rounding involves adjusting numbers to a desired precision. Common rounding functions include:

• ROUND(): Rounds a number to the nearest specified number of decimal places.

• ROUNDUP(): Rounds a number up to the nearest specified number of decimal places.

• ROUNDDOWN(): Rounds a number down to the nearest specified number of decimal places.

Formatting Dates

Date formats control how dates are displayed. The FORMAT() function enables formatting dates according to various styles.

Date Comparison

Date comparison involves evaluating relationships between dates. Common functions for date comparison include:

• DATEDIFF(): Calculates the difference between two dates. (jump to example)

• DATE(): Extracts the date component from a value.

• TODAY(): Returns the current date.

Extracting Substrings

Extracting substrings involves retrieving specific portions of text strings. The LEFT() and RIGHT() functions extract substrings from the left and right ends of a string, respectively.

Data Type Conversion

Data type conversion involves changing the data type of a value. The NUMS() and DATES() functions convert values to numbers and dates, respectively.

This guide provides a foundational understanding of formulas in Windsor applications. For more in-depth explanations and advanced topics, please consult additional resources or seek guidance from experienced Windsor users.

Time/Date Duration Calculation

For Days:

// Days
floor(dateDiff(date(formatDate(`ANTICIP_STRT_DT`,'ddd MMM DD YYYY') + ' ' + formatDate(date(`ANTICP_STRT_TM`, true),'HH:mm:ss [GMT]ZZ'), true),date(formatDate(`ANTICIP_END_DT`,'ddd MMM DD YYYY') + ' ' + formatDate(date(`ANTICP_END_TM`, true),'HH:mm:ss [GMT]ZZ'), true),'hours')/24) + ' day(s)' + ' '

For Hours:

iff(formatDate(`ANTICP_END_TM`,'HH:mm:ss')<'12:00:00',
// Hours - when AM is after PM
dateDiff(date(formatDate('01/01/2001','ddd MMM DD YYYY') + ' ' + formatDate(date(`ANTICP_STRT_TM`, true),'HH:mm:ss [GMT]ZZ'), true),date(formatDate('01/02/2001','ddd MMM DD YYYY') + ' ' + formatDate(date(`ANTICP_END_TM`, true),'HH:mm:ss [GMT]ZZ'), true),'hours') + ' hour(s)' + ' '
,
// Hours - when PM is after AM
dateDiff(date(formatDate('01/01/2001','ddd MMM DD YYYY') + ' ' + formatDate(date(`ANTICP_STRT_TM`, true),'HH:mm:ss [GMT]ZZ'), true),date(formatDate('01/01/2001','ddd MMM DD YYYY') + ' ' + formatDate(date(`ANTICP_END_TM`, true),'HH:mm:ss [GMT]ZZ'), true),'hours') + ' hour(s)' + ' '
)

For Minutes:

// Minutes
(dateDiff(date(formatDate('01/01/2001','ddd MMM DD YYYY') + ' ' + formatDate(date(`ANTICP_STRT_TM`, true),'HH:mm:ss [GMT]ZZ'), true),date(formatDate('01/02/2001','ddd MMM DD YYYY') + ' ' + formatDate(date(`ANTICP_END_TM`, true),'HH:mm:ss [GMT]ZZ'), true),'minutes') - 60*dateDiff(date(formatDate('01/01/2001','ddd MMM DD YYYY') + ' ' + formatDate(date(`ANTICP_STRT_TM`, true),'HH:mm:ss [GMT]ZZ'), true),date(formatDate('01/02/2001','ddd MMM DD YYYY') + ' ' + formatDate(date(`ANTICP_END_TM`, true),'HH:mm:ss [GMT]ZZ'), true),'hours')) + ' minute(s)'

Simple Conditional Formula Examples

Conditional formulas are powerful tools in Windsor applications that allow you to display different content based on certain conditions. They are like decision-making statements that evaluate whether a particular condition is true or false, and based on the outcome, they determine what information or action should, or should not, be displayed.

`SECT_D:D_COLOR`=="Blue"

num(`SECT_D:D_NUM`)>10

`SECT_D:D_YN`=="Yes"

`SECT_A:LTR_SINGLE`=='A'||`SECT_A:LTR_SINGLE`=='D'

contains(`SECT_C:MULTI_MULTI_LTR_A`,'M')|| 
contains(`SECT_C:MULTI_MULTI_LTR_B`,'X')

sum(num(`SECT_E:E_VAL1`)+num(`SECT_E:E_VAL2`))>100

count(`SECT_E:E_LTR_CNT`)>=3

contains(toLowerCase(`SECT_E:E_TXT_CONTAIN`),"show")

Advance Conditional Formula Examples

Select Control Selection (Displayed Description) Exact Comparison Example

The following formula evaluates to True if the selected (and displayed) value in the SelectMenu field (Single or Multiple Select Control) is equal to "Yes". Note the ".description" selection to signify that the comparison will be performed on the displayed description, versus the hidden ID. If the hidden ID value needs to be referenced, ".id" can be specified.

Select Control Selection (Displayed Description) If/Then Comparison Example

The following formula evaluates to True if the selected (and displayed) Pending Violations Select Control value is equal to "Yes", otherwise returns False.

Select Control Selection (Hidden ID) Exact Comparison Example

The following formula evaluates to True if the ID for the selected value in the SelectMenu field (Single or Multiple Select Control) is equal to "2".

Select Control Selection (Displayed Description) Contains Comparison Example

The following formula evaluates to True if the selected (and displayed) value in the County (Single or Multiple Select Control) contains the text "Multnomah", "'Clackamas" or "Washington".

Simple Math (Multiplication) Example

The following formula multiplies BoxLength by BoxWidth to calculate area.

Aggregation Function (Summarize) Across Repeating Sections Example

The following formula sums all the Tons values across all Repeaters in a Repeating Section.

Aggregation Function (Average) Across Repeating Sections Example

The following formula averages the Tons values across all Repeaters in a Repeating Section.

Return Cell Value from Advanced Table Based on Value in Another Cell

The following formula returns an [Emissions Value] value from a cell in an Advanced Table where another cell, on the same row, has a matching value [VOC name of Formaldehyde].

Return Cell Value from Repeater Based on Value in Another Field within Repeater

The following formula returns an [Emissions Value] value from a field in a Repeater where another field, on the same repeater, has a matching value [VOC name of Formaldehyde].

Count Values Exceeding Limit in an Advanced Table

The following formula counts the number of rows in an Advanced Table where a [Emissions Value] value exceeds a limit (e.g., 10).

Count Values Exceeding Limit in a Repeater

The following formula counts the number of records in a Repeater where a [Emissions Value] value exceeds a limit (e.g., 10).

Calculating a Date Offset

If the current date is 5/25/2023, the formula below will return -3.

Require a Specific Length for Zip Code or Number Control

The following example looks for a number 5 characters in length utilizing numbers 0-9.

Additional Resources

nFORM Conditional Formula Examples

The form definition file for the form above is provided below (nFORM v4.17 format). It can be downloaded and deployed to a client environment using the nFORM Import Form option.