Multi-Function Equation Examples

Listed below are several examples of equations demonstrating various combined functions in action.

Please feel free to post in our Community if you're looking for something specific that is not listed below.

Combine Two Date Range Fields 

We use the CONCAT and DATE_FORMAT functions to combine two Date Range fields together into one field. 

CONCAT(DATE_FORMAT({Date Range - Start Date},'%M'),' ',DATE_FORMAT({Date Range - Start Date}, '%d'),',',' ',DATE_FORMAT({Date Range - Start Date}, '%Y'),' ',' -',' ',DATE_FORMAT({Date Range - End Date},'%M'),' ',DATE_FORMAT({Date Range - End Date}, '%d'),',',' ',DATE_FORMAT({Date Range - End Date}, '%Y'),'<br>',DATE_FORMAT({2nd Date Range - Start Date},'%M'),' ',DATE_FORMAT({2nd Date Range - Start Date}, '%d'),',',' ',DATE_FORMAT({2nd Date Range - Start Date}, '%Y'),' ',' -',' ',DATE_FORMAT({2nd Date Range - End Date},'%M'),' ',DATE_FORMAT({2nd Date Range - End Date}, '%d'),',',' ',DATE_FORMAT({2nd Date Range - End Date}, '%Y'))

Create a random code

We use the CONCAT, CHAR, FLOOR, and RAND functions to generate a seven-digit string of the following.

1. Random Number (ASCII 48-57)
2. Random Number (ASCII 48-57)
3. Random UPPERCASE letter (ASCII 65-90)
4. Random lowercase letter (ASCII 97-122)
5. Random lowercase letter (ASCII 97-122)
6. Random Number (ASCII 48-57)

CONCAT(CHAR(FLOOR(48+RAND()*9)),CHAR(FLOOR(48+RAND()*9)),CHAR(FLOOR(65+RAND()*25)),CHAR(FLOOR(97+RAND()*25)),CHAR(FLOOR(97+RAND()*25)),CHAR(FLOOR(65+RAND()*25)),CHAR(FLOOR(48+RAND()*9)))

Detect if a field is blank or not

We use IF and LENGTH to check whether a field is blank and output text.

IF(LENGTH({Text})>0,CONCAT('text is not blank'),CONCAT('text is blank'))

Show a formatted price based on what option is selected.

We use IF, CONCAT, and FORMAT to show a Rent or Sales price formatted with two decimal places and a dollar symbol in front.

IF({Sale/Rent}='For Sale', CONCAT('$',FORMAT({Sales Price},2)), CONCAT('$',FORMAT({Rent Per Month},2)))

Detect if a Number is zero or blank

We use IF and LENGTH to detect if a number field has a blank or zero value.

IF({Number}!=0 && LENGTH({Number})>0,'FALSE','TRUE')

Output unique values from five different fields

We use CONCAT and multiple IFs to output an array of unique values based on five fields.

CONCAT({Text 1}, IF(!({Text 1}={Text 2}), CONCAT(', ',{Text 2}), ''), IF(!({Text 1}={Text 3} || {Text 2}={Text 3}), CONCAT(', ',{Text 3}),''), IF(!({Text 1}={Text 4} || {Text 2}={Text 4} || {Text 3}={Text 4}), CONCAT(', ',{Text 4}),''), IF(!({Text 1}={Text 5} || {Text 2}={Text 5} || {Text 3}={Text 5} || {Text 4} = {Text 5}), CONCAT(', ',{Text 5}),''))

Output the number of times a phrase or letter appears in a Rollup

We use FORMAT, LENGTH and some math to output the amount of times a letter appears in a Rollup Field

FORMAT((LENGTH({Rollup}) - LENGTH(REPLACE({Rollup},'A','')))/ LENGTH('A'),0)