This article will walk you through some of the most common formula use cases and how to solve for them.
Workflow Designer formulas enable you to build complex logic calculations directly into your workflow configurations. This reduces manual data entry in launch forms and gives legal teams confidence that the data in their contracts is calculated correctly.
Formulas are a powerful way to automate complex calculations that live within your templates, however, they can increase the complexity of your workflow building process. Formulas are built with a few guidelines to ensure you do not have any unexpected outcomes in your workflows.
To learn more about how to use formulas, refer to Manage Formulas.
For a walkthrough of example formulas and more information on supported Formula types, refer to the Formulas Help Center.
Use a Value if a Particular Property Exists
This formula returns [whatever should return if My Property exists] if [My Property] exists and Empty otherwise:
IfExists([My Property], [whatever should return if My Property exists])
The formula below is the most common use case of IfExists. It returns the value of [My Property] if it exists and Empty if it does not:
IfExists([My Property], [My Property])
The formula below returns [Property A] + [Property B] if both [Property A] and [Property B] exist and Empty otherwise:
IfExists([Property A], [Property B], Add([Property A], [Property B]))
NOTE
If you want to use the value you get from IfExists in another formula, you must handle the scenario in which IfExists returns Empty using IfEmpty. This is the most common reason workflows break after launch and return user-facing errors.
If you need IfExists, you almost always need IfEmpty, too.
Example:
- This is an unsafe formula! It will throw an error and break the workflow if Value 2 does not exist:
Add([Value 1], IfExists([Value 2], [Value 2]))
- This is the safe version, which handles the scenario in which Value 2 does not exist:
Add([Value 1], IfEmpty(IfExists([Value 2], [Value 2]), 0)
The appropriate fallback value may not always be 0; tailor this to your own needs based on what should happen if the property in question does not exist.
Display a Checkbox
This formula returns a checked checkbox if [Testing Checkbox] is True and an empty checkbox otherwise.
If([Testing Checkbox], "☒", "☐")
Extract the Year from a Date
This formula extracts the year from the date as a string. This is the most simple approach:
FormatDate([Your Date Property], "yyyy") // returns "2023"
FormatDate([Your Date Property], "yy") // returns "23"
Extract the Month from a Date
The FormatDate formula can be used to extract the month from a Date property as a Text value: :
FormatDate([Your Date Property], "M") // returns a Text value like "2"
FormatDate([Your Date Property], "MM") // returns a zero-padded Text value like "02"
FormatDate([Your Date Property], "MMM") // returns a Text value like "Feb"
FormatDate([Your Date Property], "MMMM") // returns a Text value like "February"
If you’d like the month as a Number value, use TextToNumber:
TextToNumber(FormatDate([Your Date Property], "M"))
Extract the Day of the Month from a Date
The formula below extracts and returns the month from [Your Date Property] as a Text value:
FormatDate([Your Date Property], "d") // returns a Text value like "2"
FormatDate([Your Date Property], "dd") // returns a zero-padded Text value like "02"
If you’d like the month as a Number value instead of a Text value, for some reason, you need the month as a number, use TextToNumber:
TextToNumber(FormatDate([Your Date Property], "d"))
Extract the Day of the Week from a Date
The formula below extracts and returns the day of the week from [Your Date Property] as a Text value:
FormatDate([Your Date Property], "eee") // returns an abbreviated Text value like "Tue"
FormatDate([Your Date Property], "eeee") // returns a Text value like "Tuesday"
Populate the Last Day of the Month
The formula below populates the last day of the month:
RelativeDate(RelativeDate(StartOf([My Date], "month"), 1, "month"), -1, "day")
Format a Date as YYYY-MM (e.g., “2023-04”)
This formula returns [Your Date Property] in “YYYY-MM” format:
FormatDate([Your Date Property], "yyyy-MM")
Format a Date as YYYY-MM-DD (a/k/a “ISO Format,” e.g., “2023-04-15”)
This formula returns [Your Date Property] in ISO format, e.g. “YYYY-MM-DD”:
FormatDate([Your Date Property], "yyyy-MM-dd")
State Abbreviation Based on State Name (e.g., “CA” from “California”)
The formula below returns the state abbreviation for the state portion of [Your Address Property]:
Switch(State([Your Address Property]),
"Alabama", "AL",
"Alaska", "AK",
"Arizona", "AZ",
"Arkansas", "AR",
"American Samoa", "AS",
"California", "CA",
"Colorado", "CO",
"Connecticut", "CT",
"D.C.", "DC",
"Delaware", "DE",
"District of Columbia", "DC",
"Florida", "FL",
"Georgia", "GA",
"Guam", "GU",
"Hawaii", "HI",
"Idaho", "ID",
"Illinois", "IL",
"Indiana", "IN",
"Iowa", "IA",
"Kansas", "KS",
"Kentucky", "KY",
"Louisiana", "LA",
"Maine", "ME",
"Maryland", "MD",
"Massachusetts", "MA",
"Michigan", "MI",
"Minnesota", "MN",
"Mississippi", "MS",
"Missouri", "MO",
"Montana", "MT",
"Nebraska", "NE",
"Nevada", "NV",
"New Hampshire", "NH",
"New Jersey", "NJ",
"New Mexico", "NM",
"New York", "NY",
"North Carolina", "NC",
"North Dakota", "ND",
"Northern Mariana Islands", "MP",
"Ohio", "OH",
"Oklahoma", "OK",
"Oregon", "OR",
"Pennsylvania", "PA",
"Puerto Rico", "PR",
"Rhode Island", "RI",
"South Carolina", "SC",
"South Dakota", "SD",
"Tennessee", "TN",
"Texas", "TX",
"Trust Territories", "TT",
"Utah", "UT",
"Vermont", "VT",
"Virginia", "VA",
"Virgin Islands", "VI",
"Washington", "WA",
"West Virginia", "WV",
"Wisconsin", "WI",
"Wyoming", "WY",
"ERROR"
)
Get the Correct Indefinite Article Based on State Name
The formula below can be used to get the indefinite article from [Your State Property]. For example, if you’d like to display a sentence like “XYZ is a/an [Your State Property] corporation,” you could use this formula to extract the correct article before [Your State Property].
Switch([Your State Property],
"Alaska", "an",
"Alabama", "an",
"Arkansas", "an",
"American Samoa", "an",
"Arizona", "an",
"Iowa", "an",
"Idaho", "an",
"Illinois", "an",
"Indiana", "an",
"Ohio", "an",
"Oklahoma", "an",
"Oregon", "an",
"Utah", "an",
"a"
)
Displaying Cents from a Monetary Amount
The formula below can be used to retrieve the cents portion (as a whole number) of [Your Monetary Amount Property]:
(
MonetaryValue([Your Monetary Amount Property]) -
Truncate(MonetaryValue([Your Monetary Amount Property]))
) * 100
Date Examples for Termination Dates
Termination Date (365 Days): RelativeDate([Agreement Date], 365, "days")
Termination Date (12 Months): RelativeDate([Agreement Date], 12, "months")
Termination Date (1 Year): RelativeDate([Agreement Date], 1, "years")
Termination Date (1 Year minus 1 Day): RelativeDate(RelativeDate([Agreement Date], 1, "years"), -1, "days")
Termination Date (1.5 Years): RelativeDate([Agreement Date], 18, "months")
Unique Values per Dropdown Item
If you have a dropdown of Legal Entities for example, the formula below will return the correct address per entity.
Switch([Legal Entity],
'Ironclad, Inc.','US Address Here',
'Ironclad, Ltd.','UK Address Here',
'Fall Back Address Here'
)
If A then... Else If B then... Else...
You may need a formula that gives one value if A is true, another if A is false but B is true, and another if neither is true (or some more complex chain with additional conditions). To avoid errors, you again need to use IfEmpty and IfExists.
If([BooleanValueA],
"Value if A is true",
If(
IfEmpty(IfExists(TheBooleanProperty, TheBooleanProperty), false),
"Value if B exists and is true",
"Fallback value"
)
)
Table-Like Lookups
You may have a complicated set of logic depending on multiple variables that can be represented in a table such as this:
Use IfEmpty to confirm the propertys exist, Concat to build a single Text value to do a lookup (formatted in some unique way, like "Value~Next Value~Third Value"), and Switch to handle the decision/lookup process:
Switch(
Concat(
IfEmpty(IfExists([Property 1], [Property 1]), "Empty"),
"~",
IfEmpty(IfExists([Property 2], [Property 2]), "Empty"),
...
),
"Value 1 for Property 1~Value 1 for Property 2", "Result for this combo",
"Value 1 for Property 1~Empty", "Result for this other combo",
...,
"Some fallback value here if nothing matched"
)
For example, if you were using the data from the table above:
Switch(
Concat(
IfEmpty(IfExists([Primary], [Primary]), "Empty"),
"~",
IfEmpty(IfExists([Secondary], [Secondary]), "Empty")
),
"Apple~Empty", 1,
"Apple~Banana", 2,
"Apple~Pear", 3,
"Banana~Pear", 4,
"Banana~Peach", 5,
"Pear~Grape", 6,
"Pear~Empty", 7,
"Pear~Peach", 8,
99
)
NOTE
If you can export a table like the above in Excel format, you can use Excel to build the Ironclad formula for you. For example, for the image above, you might enter this formula in cell D2 of the Excel sheet:
="""" & IF(A2 = "", "Empty", A2) & "~" & IF(B2 = "", "Empty", B2) & """, " & C2 & ","
Copy that formula down for as many rows as you need, and Excel will build all of your scenarios for your Switch for you, except for the fallback.
Dates in French Text
This formula changes “[Your Date Property]” to the property containing the date in question:
Concat(
FormatDate([Your Date Property], "d"),
If(Equals(FormatDate([Your Date Property], "d"), "1"), "er", ""),
" ",
Switch(FormatDate([Your Date Property], "M"),
"1", "janvier",
"2", "février",
"3", "mars",
"4", "avril",
"5", "mai",
"6", "juin",
"7", "juillet",
"8", "août",
"9", "septembre",
"10", "octobre",
"11", "novembre",
"12", "décembre",
"ERREUR"
),
" ",
FormatDate([Your Date Property], "yyyy")
)
Dates in Spanish Text
This formula changes “[Your Date Property]” to the property containing the date in question:
Concat(
FormatDate([Your Date Property], "d"),
If(FormatDate([Your Date Property], "d"),
" de ",
Switch(FormatDate([Your Date Property], "M"),
"1", "enero",
"2", "febrero",
"3", "marzo",
"4", "abril",
"5", "mayo",
"6", "junio",
"7", "julio",
"8", "agosto",
"9", "septiembre",
"10", "octubre",
"11", "noviembre",
"12", "diciembre",
"ERROR"
),
" de ",
Switch(FormatDate([Your Date Property], "yyyy")
)
French Numbers in Text Form
The formula below returns [Your Number Property] formatted as French Text.
NOTE
The translation “un” for 1 may be incorrect because of the gender of the following noun; you may need to adjust accordingly.
IfExists([Your Number Property],
Switch([Your Number Property],
1, "un",
2, "deux",
3, "trois",
4, "quatre",
5, "cinq",
6, "six",
7, "sept",
8, "huit",
9, "neuf",
10, "dix",
11, "onze",
12, "douze",
15, "quinze",
18, "dix-huit",
20, "vingt",
24, "vingt-quatre",
25, "vingt-cinq",
30, "trente",
36, "trente-six",
40, "quarante",
45, "quarante-cinq",
48, "quarante-huit",
50, "cinquante",
60, "soixante",
70, "soixante-dix",
75, "soixante-quinze",
80, "quatre-vingts",
90, "quatre-vingt-dix",
100, "cent",
120, "cent vingt",
"VEUILLEZ MODIFIER CE TEXTE"
)
)
Spanish Numbers in Text Form
The formula below returns [Your Number Property] formatted as Spanish Text.
NOTE
The translation “uno” for 1 may be incorrect because of the gender of the following noun; you may need to adjust accordingly.
IfExists([Your Number Property],
Switch([Your Number Property],
1, "uno",
2, "dos",
3, "tres",
4, "cuatro",
5, "cinco",
6, "seis",
7, "siete",
8, "ocho",
9, "nueve",
10, "diez",
11, "once",
12, "doce",
15, "quince",
18, "dieciocho",
20, "veinte",
24, "veinticuatro",
25, "veinticinco",
30, "treinta",
36, "treinta y seis",
40, "cuarenta",
45, "cuarenta y cinco",
48, "cuarenta y ocho",
50, "cincuenta",
60, "sesenta",
70, "setenta",
80, "ochenta",
90, "noventa",
100, "cien",
120, "ciento veinte",
"POR FAVOR EDITE ESTE TEXTO"
)
)