How do I use Formula Logic?

Modified on Thu, 25 Apr at 5:00 PM

A formula (or expression) is a piece of code that performs a calculation, manipulates data, or evaluates conditions. It allows you to make your data behave how you'd like, from calculating the sum of expenses to filtering datasets based on user selections. It's your go-to for setting up the dynamic and automated magic of a Droplet form.

Starting with Your Goal

Before you dive into writing a formula, clarify what you aim to achieve. This goal will guide your choice of tools from our extensive list below so your formula does exactly what you need!

First, A Quick Word About Component IDs

As we dive into creating formulas, you should know that we will be referencing Component IDs quite a bit.

For example, let’s say you have an input component that collects a number from your submitter. Maybe it’s for asking about the number of students going on a field trip, so let’s set the Component ID as studentCount. Another input might be looking for the number of staff members attending as well. We can set that Component’s ID to be staffCount.

Now, you can refer to those components by their ID right within a formula. Just remember to make them appear exactly as-is!

Operators

Operators are the fundamental elements you’ll use to construct your formula, covering everything from basic math to more intricate comparisons. Here’s a brief overview:

Arithmetic Operators

Use these for basic calculations:

+	Add: This sums the numerical values. Example:Add the studentCount and staffCount together. studentCount + staffCount
-	Subtract: This subtracts numerical values. Example: Subtract 1 from the number of staff members in staffCount. staffCount - 1
*	Multiply: This multiplies numerical values. Example: This would double the amount of students in studentCount studentCount * 2
/	Divide: This divides numerical values. Example: This divides the studentCount by the staffCount which could be useful for a student:staff ratio. studentCount/staffCount
%	Modulus (Remainder): This operation finds the remainder after one numerical value is divided by another. It's particularly useful in scenarios where you need to determine if a number is divisible by another. Example: Find the remainder when studentCount is divided by 3. If the result is 0, then the number of students is divisible by 3.This could be helpful in determining if a number is perfectly divisible by another number. studentCount % 3

Other Examples:

Purpose	Expression
Split the assignment between two people based on the sequenceId of the submission (Odd numbers go to person A, Even numbers go to person B)	sequenceId % 2 === 1 ? personnel.accountants.A : personnel.accounts.B
Multiply the miles entered by the reimbursement rate.	totalMiles * .065

Comparison Operators

Comparison operators are the secret sauce that lets your formulas make smart decisions! They perform checks between values, and if the condition is met, they return true; otherwise, they return false. These tools are incredibly handy in Droplet forms for ensuring your data meets certain criteria before taking the next step.

===	Equals: Checks if two values are identical. This is very common! Example: Check if the submitter entered a 5 in the staffCount component. staffCount === 5
!==	Not Equal: Checks if two values are not identical. This is also very common! Example: Check if the number of staff members is not the same as the number of students. staffCount !== studentCount
>	Greater Than: Checks if one numerical value is larger than another. Example: Check if more than 100 students will be attending the field trip. studentCount > 100
<	Less Than: Checks if one numerical value is smaller than another. Example: Check if the number of students attending is less than the number of staff attending. This could be used to ensure the submitter didn't accidentally flip the two fields around when filling out the form. studentCount < staffCount
>=	Greater Than or Equal To: Checks if one numerical value is larger than or equal to another. Example: Check if there are more than 100 students, or exactly 100 students. studentCount >= 100
<=	Less Than or Equal To: Checks if one numerical value is smaller than or equal to another. Example: Check if the number of students is less than or equal to 10. studentCount <= 10

Other Examples:

Purpose	Expression
Conditionally send a notification when the expense amount is greater than $1500.	expenseTotal > 1500
Conditionally display a group of components when the user selects Mileage as the reimbursement type.	reimbursementType === 'Mileage'

Logical Operators

Logical operators can level-up your formulas by combining or altering conditions. They are especially useful when your data needs to meet multiple criteria or when you want to reverse a condition's result.

&&	And: Combines conditions that all need to be true for the overall condition to be true. Example: Verify that there are more than 50 students and more than 5 staff members. studentCount > 50 && staffCount > 5
\|\|	Or: Checks if at least one of multiple conditions is true. Example: Verify that either the number of students is greater than 200 or the number of staff is less than 3. studentCount > 200 \|\| staffCount < 3

You can also use more than two conditions, if you'd like, or combine them for multiple criteria.

Other Examples:

Purpose	Expression
Check if the submission is in certain steps	currentStep() === 'payrollStep' \|\| currentStep() === 'completed'
Check if the location selected is the District Office and the expense amount is greater than $500	location === 'District Office' && totalExpense > 500

String Operators

Think of a string as a line of text. Just like a string of letters that forms words and sentences, in Droplet forms and other computer programs, a string is simply any text you type out, from names and addresses to whole paragraphs. Whether you're entering data or giving instructions, if it's text, it's a string!

Strings are super useful because they let you work with words and sentences in your forms, just like you would with numbers, but with more emphasis on the message you want to convey or collect. And, string operators let you handle text in clever ways.

+	Concatenate: Joins strings together. Note: If the values are numbers, this will instead add them together as we've seen above! Adding strings to the formula will turn any numbers into strings. Example: Create a sentence embedding the component values. studentCount + 'students and ' + staffCount + 'staff are attending the trip.'
``	Template Literals: Joins strings together without using a +. If you reference any components, you will put them in curly braces ({}) with a dollar sign in front (${componentId}). Example: `${studentCount} students and ${staffCount} staff are attending the trip.`

Conditional Operators

Once you've got a handle on the operators above, you're ready to step up to conditional operators, which add layers of nuanced decision-making to your formulas. These operators work by evaluating conditions and executing different actions based on whether the condition is true or false. A perfect example is the ternary operator, which acts as a more compact form of an if-else statement simplifying decision logic in your formulas.

Ternary: The ternary operator is a streamlined, three-part expression that simplifies conditional logic. First it evaluates a condition (like a comparison); next, it provides one outcome if the condition is true, and another if it is false

Example: If the studentCount is greater than 100, return "Large Group", but if it is not, return "Small Group".

studentCount > 100 ? 'Large Group' : 'Small Group'

Use the following template to help you write your conditional expressions.

condition ? expressionIfTrue : expressionIfFalse

Other Examples:

Purpose	Expression
Change the text on the submit button depending on what option is selected.	reviewedStatus === 'Needs Review' ? 'Send Back for Corrections' : 'Submit'
Set up notification recipients to be conditional based on the location selected.	location === 'Location 1' ? 'personOneEmail@email.com' : location === 'Location 2' ? 'personTwoEmail@email.com' : 'defaultPersonEmail@email.com'

Purpose

Expression

Change the text on the submit button depending on what option is selected.

reviewedStatus === 'Needs Review' ? 'Send Back for Corrections' : 'Submit'

Set up notification recipients to be conditional based on the location selected.

location === 'Location 1' ? 'personOneEmail@email.com' : location === 'Location 2' ? 'personTwoEmail@email.com' : 'defaultPersonEmail@email.com'

Helper Functions

Droplet has created many of the most common uses of more complex formulas into simple ones for you. To learn more about how helper functions work, check out this article.

sum(...args)
subtract(a, b, fallback)
multiply(a, b, fallback)
divide(a, b, fallback)
concat(a, b, separator, fallback)
padStart()
padEnd()
today()
addDays(days, fromDate)
getSubmissionId()

Table Helper Functions:

Number(sum(columnId)
getCell()

Functions to more easily deal with dollar signs:

toCurrency(number) => string formatted as Currency ($___.__)
fromCurrency(string) => number

Functions to access workflow information:

currentStep() => string of current step name   
getAssignedTo() => object of current assignee's name and email address
getSubmittedBy() => object of submission initiator's name and email address

Utilizing a Dataset

When you want to reference a dataset, use the dataset id that you defined in the Datasets tab within the builder. In the examples below, let's assume I am using a dataset set up like the Locations and Associated Workflow Assignees Dataset Template.

Examples:

Purpose	Expression
Use a dataset to get a list of locations.	Object.keys(locationAdminDataset).sort()
Use the selection from a Dropdown to determine the assignee for a workflow step	locationAdminDataset[locationsDropdown]