Calculated Fields

Updated Sep 30, 2026Article

Formulas#

For calculated rows in an export or import (be it API or flat-file), a list of formulas and basic examples can be found in the tables below.

When working with formulas, if you need to reference the quote symbol, use a double quote “” inside of the quote marks for the equation. (i.e., “”””)

Calculated Row Formulas

Function: Logic#

IF

Formula / Examples:

json

Name of the function: IF
Number of parameters: 3
Type of parameters: bool, any, any

INPUT: IF(HEIGHT, 3/HEIGHT, 3)
DESCRIPTION: Will make sure 3/HEIGHT does not cause division by zero

Notes:

The IF(b, case1, case2) function provides branching capability. If B is not 0, then it returns case1, else it returns case2. Behavior is similar to PHP's: return b ? case1 : case2;. If b==0 then case1 will not be Evaluated, and vice versa. The IF function is not restricted to only the = symbol and does supports “and”, “or”, and “not” using the symbols “&”, “|”, “!” as shown below

IF([C1] <> [C2],[C3],"Failed") → Checking to see if two cells are not equal.
IF([C1] = [C2],[C3],"Failed") → Checking to see if two cells are equal.
IF([C1] ="Y" & [C2] ="Y",[C3],"Failed") → Checking if both cells are true.
IF([C1] ="Y" I [C2] ="Y",[C3],"Failed") → Checking if either (or) cells are true.
IF(![C1]=”Y”,[C3],"Failed") → If [C1]=”Y” is true, the ! will flip true to false and vice versa.

&, |, !

Formula / Examples:

json

And, Or, Not (See IF notes above)

Function: String Operations#

STRLEN

Formula / Examples:

json

Name of the function: STRLEN
Number of parameters: 1
Type of parameters: number

INPUT: STRLEN("ABC")
OUTPUT: 3

Notes:

The string length function returns the length of the string parameter.

STR

Formula / Examples:

json

Name of the function: STR
Number of parameters: 1
Type of parameters: number

INPUT: STR(451)
OUTPUT: "451"

Notes:

The string function return the string representation of the passed value.

SUBSTR

Formula / Examples:

json

Name of the function: SUBSTR
Number of parameters: 3
Type of parameters: string, number, number

INPUT: SUBSTR("abcdef", 0, -1)
OUTPUT: "abcde"

INPUT: SUBSTR("abcdef", 2, 4)
OUTPUT: "cde"

INPUT: SUBSTR("abcdef", 2, -1)
OUTPUT: "cde"

INPUT: SUBSTR("abcdef", 4, -4)
OUTPUT: FALSE

INPUT: SUBSTR("abcdef", -3, -1)
OUTPUT: "de"

Notes:

The string function return the string representation of the passed value.

The substring function return part of a string. The first parameter is the input string. Must be one character or longer. The second parameter is the start point. And the third parameter represent the length.

2nd param - start

If start is non-negative, the returned string will start at the n'th position in string, counting from zero. For instance, in the string 'abcdef', the character at position 0 is 'a', the character at position 2 is 'c', and so forth.
If start is negative, the returned string will start at the n'th character from the end of string.
If string is less than start characters long, FALSE will be returned.

3rd param - length

If length is given and is positive, the string returned will contain at most length characters beginning from start (depending on the length of string).
If length is given and is negative, then that many characters will be omitted from the end of string (after the start position has been calculated when a start is negative). If start denotes the position of this truncation or beyond, FALSE will be returned.
If length is given and is 0, FALSE or NULL, an empty string will be returned.
If length is omitted, the substring starting from start until the end of the string will be returned.

CONCAT

Formula / Examples:

json

Name of the function: CONCAT
Number of parameters: unlimited
Type of parameters: number

INPUT: CONCAT("ABC", "DEF")
OUTPUT: "ABCDEF"

Notes:

The concatenation function CONCAT returns the concatanated strings. There is no preset limit on the number of parameters

TRIM

Formula / Examples:

json

Name of the function: TRIM
Number of parameters: 1
Type of parameters: number

INPUT: TRIM(" abc  ")
OUTPUT: "abc"

Notes:

The trim function TRIM returns the trimmed version of the given string.

RTRIM

Formula / Examples:

json

Name of the function: RTRIM
Number of parameters: 1
Type of parameters: number

INPUT: RTRIM("abc  ")
OUTPUT: "abc"

INPUT: RTRIM("  abc ")
OUTPUT: "  abc"

Notes:

The RTRIM remove whitespaces from the ends of the string.

LTRIM

Formula / Examples:

json

Name of the function: LTRIM
Number of parameters: 1
Type of parameters: number

INPUT: LTRIM("  abc")
OUTPUT: "abc"

INPUT: LTRIM(" abc  ")
OUTPUT: "abc  "

Notes:

The LTRIM remove whitespaces from the start of the string.

CHR

Formula / Examples:

json

Name of the function: CHR
Number of parameters: 1
Type of parameters: number

INPUT: CHR(65)
OUTPUT: "A

Notes:

The CHR functions gets a character with the given ASCII code.

UPPER

Formula / Examples:

json

Name of the function: UPPER
Number of parameters: 1
Type of parameters: string

INPUT: UPPER("hello world")
OUTPUT: "HELLO WORLD"

Notes:

The uppercase function UPPER will capitalize all letters of string.

LOWER

Formula / Examples:

json

Name of the function: LOWER
Number of parameters: 1
Type of parameters: string

INPUT: LOWER("HELLO WORLD")
OUTPUT: "hello world"

Notes:

The lowercase function LOWER will lowercase all letters of string.

TITLECASE

Formula / Examples:

json

Name of the function: TITLECASE
Number of parameters: 1
Type of parameters: string

INPUT: TITLECASE("heLlO woRLd")
OUTPUT: "Hello World"

Notes:

The titlecase function TITLECASE will capitalize first letter of each word.

MATCH

Formula / Examples:

json

Name of the function: MATCH
Number of parameters: 2
Type of parameters: p1, p2, caseSensitve = 1

INPUT: MATCH("Hello", "hello", FALSE)
OUTPUT: TRUE

INPUT: MATCH("Hello", "hello", TRUE)
OUTPUT: FALSE

INPUT: MATCH ("Hello", "Hello", TRUE)
OUTPUT: TRUE

Notes:

The match function MATCH check if strings are equal. If caseSensitive is TRUE, then do not ignore case.

PAD

Formula / Examples:

json

Name of the function: PAD
Number of parameters: 3
Type of parameters: string, char, length

INPUT: PAD("137", '0', 5)
OUTPUT: "00137"

INPUT: PAD("137", '2', -6)
OUTPUT: "137222"

INPUT: PAD("137", '0', 2)
OUTPUT: "137"

Notes:

The pad function PAD will pads a given string to given length by given char.

If length is < 0, pad the string from the right instead.
If length is < length of string, return string.

CONTAINS

Formula / Examples:

json

Name of the function: CONTAINS
Number of parameters: 2
Type of parameters: string, substr

INPUT: CONTAINS("Hello World", "Hello")
OUTPUT: TRUE

INPUT: CONTAINS("Hello World", "random string")
OUTPUT: FALSE

Notes:

Check if string contains substring.

HAS_ONE_TRUE

Formula / Examples:

json

Name of the function: HAS_ONE_TRUE
Number of parameters: 2
Type of parameters: json_input, attribute

INPUT: HAS_ONE_TRUE("[{'associated_degree':false},{'associated_degree':true,'text':'European'}]", "associated_degree")
OUTPUT: TRUE

Notes:

Returns TRUE if an option in options has attribute = TRUE. Otherwise, returns FALSE.

STARTS_WITH

Formula / Examples:

json

Name of the function: STARTS_WITH
Number of parameters: 2
Type of parameters: string, substr

INPUT: STARTS_WITH("Hello World", "Hello")
OUTPUT: TRUE

Notes:

Check if string starts with substr.

ENDS_WITH

Formula / Examples:

json

Name of the function: ENDS_WITH
Number of parameters: 2
Type of parameters: string, substr

INPUT: ENDS_WITH("Hello World", "World")
OUTPUT: TRUE

Notes:

Check if string ends with substring.

LEFT

Formula / Examples:

json

Name of the function: LEFT
Number of parameters: 2
Type of parameters: string, numChars

INPUT: LEFT("Hello World", 5)
OUTPUT: "Hello"

Notes:

Get the first numChars characters of string.

RIGHT

Formula / Examples:

json

Name of the function: RIGHT
Number of parameters: 2
Type of parameters: string, numChars

INPUT: RIGHT("Hello World", 5)
OUTPUT: "World"

Notes:

Get the last numChars characters of string.

LEN

Formula / Examples:

json

Name of the function: LEN
Number of parameters: 1
Type of parameters: string

INPUT: LEN("Hello World")
OUTPUT: 11

Notes:

Get the number of characters in string.

SPLIT_INDEX

Formula / Examples:

json

Name of the function: SPLIT_INDEX
Number of parameters: 3
Type of parameters: string, delim, index

INPUT: SPLIT_INDEX("Hello World", " ", 2)
OUTPUT: "World"

Notes:

Split string by delimiter and get the index substring.

Function: Regex Operations#

REGEXP_MATCH

Formula / Examples:

json

Name of the function: REGEXP_MATCH
Number of parameters: 2
Type of parameters: string, pattern

INPUT: REGEXP_MATCH("Hello World", "H[a-z]{4}\s.*")
OUTPUT: TRUE

Notes:

Match string with pattern. Uses PHP RegExp.

REGEXP_REPLACE

Formula / Examples:

json

Name of the function: REGEXP_REPLACE
Number of parameters: 3
Type of parameters: string, pattern, replaceWith

INPUT: REGEXP_REPLACE("Hello World", "[a-z]", "x")
OUTPUT: "Hxxxx Wxxxx"

Notes:

Replace pattern in string with replaceWith. Uses PHP RegExp.

REGEXP_EXTRACT

Formula / Examples:

json

Name of the function: REGEXP_EXTRACT
Number of parameters: 2
Type of parameters: string, pattern

INPUT: REGEXP_EXTRACT("Hello World", "Hello\s(.*)")
OUTPUT: "World"

Notes:

Get substring within string that matches the first parenthetical expression of pattern. Uses PHP RegExp.

REGEXP_EXTRACT_NTH

Formula / Examples:

json

Name of the function: REGEXP_EXTRACT_NTH
Number of parameters: 3
Type of parameters: string, pattern, index

INPUT: REGEXP_EXTRACT_NTH("Hello World", "H([a-z]+)\s(.*)", 1)
OUTPUT: "ello"

Notes:

Get substring within string that matches the index parenthetical expression of pattern. Uses PHP RegExp.

Function: Maps and Lists#

DS_MAP

Formula / Examples:

json

Name of the function: DS_MAP
Number of parameters: 5
Type of parameters: input, dataSource, match, output, default

INPUT: DS_MAP("cnr.terms.1", "data_source.terms", "guid", "name", "TermNotFound")
OUTPUT: "Fall 2016"

INPUT: DS_MAP("cnr.terms.1350", "data_source.terms", "guid", "name", "TermNotFound")
OUTPUT: "TermNotFound"

INPUT: DS_MAP("USA", "data_source.country", "alpha3", "name", "CountryNotFound")
OUTPUT: "United States"

Notes:

Luminous Data Source mapping. The output value is the value of the output key for the setting matched by match in the dataSource. If no settings are matched with match, default is returned. Checks the luminous_data_sources collection for item dataSource, check its options, finds an item which match field is input, and returns it's name field.

CEEB_MAP

Formula / Examples:

json

Name of the function: CEEB_MAP
Number of parameters: 4
Type of parameters: input, match, output, default

INPUT: CEEB_MAP(181615,"ceeb","name", "CeebNotFound")
OUTPUT: "Academy At Shawnee"

Notes:

Same as DS_MAP, but the Data Source is the luminous_ceebs collection.

CHECKBOX_FIRST

Formula / Examples:

json

Name of the function: CHECKBOX_FIRST
Number of parameters: 1
Type of parameters: json_input

INPUT: CHECKBOX_FIRST("[{'checked':true,'text':'Asian'},{'checked':false,'text':'European'},{'checked':true,'text':'American'}]")
OUTPUT: "Asian"

INPUT: CHECKBOX_FIRST("[{'checked':false,'text':'Asian'},{'checked':false,'text':'European'},{'checked':false,'text':'American'}]")
OUTPUT: "

Notes:

Returns the "text" field of the first checkbox that has "checked"=TRUE.

CHECKBOX_CHECKED

Formula / Examples:

json

Name of the function: CHECKBOX_CHECKED
Number of parameters: 2
Type of parameters: json_input, delimiter

INPUT: CHECKBOX_CHECKED("[{'checked':true,'text':'Asian'},{'checked':false,'text':'European'},{'checked':true,'text':'American'}]", ",")
OUTPUT: "Asian,American"

INPUT: CHECKBOX_CHECKED("[{'checked':false,'text':'Asian'},{'checked':false,'text':'European'},{'checked':false,'text':'American'}]", ",")
OUTPUT: ""

Notes:

Used mostly internally, for exporting checked values of a checkbox field. checkboxes will most likely be a slug. The internal object is expected to have fields "checked" and "text". If "checked" is TRUE, then "text" will be output.

TAXONOMY_MAP

Formula / Examples:

json

INPUT: TAXONOMY_MAP("cnr.taxonomy.18", "guid", "value", "MISSING")
OUTPUT: "grad-admit"

INPUT: TAXONOMY_MAP("cnr.taxonomy.18", "guid", "name", "MISSING")
OUTPUT: "Grad Admits"

Notes:

Map function to fetch taxonomy.

Usage Example:

json

Name of the function: TAXONOMY_MAP
Number of parameters: 4
Type of parameters: input, match, output, default

INPUT: TAXONOMY_MAP("cnr.taxonomy.18", "guid", "value", "MISSING")
OUTPUT: "grad-admit"

INPUT: TAXONOMY_MAP("cnr.taxonomy.18", "guid", "name", "MISSING")
OUTPUT: "Grad Admits" 

INPUT: TAXONOMY_MAP("", "guid", "name", "MISSING")
OUTPUT: "MISSING" 

INPUT: TAXONOMY_MAP("grad-admit", "value", "name", "MISSING")
OUTPUT: "Grad Admits"

INPUT: TAXONOMY_MAP("grad-admit", "value", "guid", "MISSING")
OUTPUT: "cnr.taxonomy.6"

DB_MAP

Formula / Examples:

json

Name of the function: DB_MAP
Number of parameters: 5
Type of parameters: type, input, match, output, default
  type: term, major, or taxonomy. (may be expanded later)

INPUT: DB_MAP("taxonomy","grad-admit", "value", "guid", "MISSING")
OUTPUT: "cnr.taxonomy.6"

Notes:

An universal db mapper method, supposed to be for mainly internal usage. Internally TAXONOMY_MAP, SC_MAP_IMPORT, SC_MAP_EXPORT are using this too. Note: The value is being searched as it was given; with trim (to remove leading and trailing whitespaces); and with all non-alphanumeric character replaced by _.

SC_MAP_IMPORT

Formula / Examples:

json

Name of the function: SC_MAP_IMPORT
Number of parameters: 2
Type of parameters: input, default

INPUT: SC_MAP_IMPORT([c42], "MISSING") - where c42 is the 42th imported column
ALIAS FOR: TAXONOMY_MAP([c42], "value", "guid", "MISSING")
OUTPUT: "tu.taxonomy.1"

Notes:

Map function used to import taxonomies to user. Use with [user-sources-source-code] and [user-sources-source-code-segment] user fields. This function is an alias for TAXONOMY_MAP default of match and output as value/guid. Note: You can use the missing value to figure out what can not be found, eg. SC_MAP_IMPORT([C1], CONCAT("Cant find mapping for: ",[C1]) )

SC_MAP_EXPORT

Formula / Examples:

json

Name of the function: SC_MAP_EXPORT
Number of parameters: 2
Type of parameters: input, default

INPUT: SC_MAP_EXPORT([user-custom-source], "MISSING")
ALIAS FOR: TAXONOMY_MAP([user-custom-source], "guid", "value", "MISSING")
OUTPUT: "grad-admit"

Notes:

Map function used to export taxonomies from user. Use with [user-sources-source-code] and [user-sources-source-code-segment] user fields. This function is an alias for TAXONOMY_MAP default of match and output as guid/value.

Function: Date Functions#

DATE_ADD

Formula / Examples:

json

Name of the function: DATE_ADD
Number of parameters: 3
Type of parameters: datePart, interval, date

INPUT: DATE_ADD("month", 5, "2016-10-04 12:45:23")
OUTPUT: "2017-03-04 12:45:23"

Notes:

Add interval datePart to date. date should be in Y-m-d H:i:s format, interval is an integer, datePart is second/minute/hour/day/month/year. Keeps output as Y-m-d H:i:s.

DATE_DIFF

Formula / Examples:

json

Name of the function: DATE_DIFF
Number of parameters: 3
Type of parameters: datePart, startDate, endDate

INPUT: DATE_DIFF("month", "2016-10-04 12:45:23", "2017-03-04 12:45:23")
OUTPUT: 5

Notes:

Get the difference between endDate and startDate in datePart. Using datePart as unit (which is one of second/minute/hour/day/month/year).

DATE_COMPARE

Formula / Examples:

json

Name of the function: DATE_COMPARE
Number of parameters: 3
Type of parameters: p1, operator, p2

INPUT: DATE_COMPARE("2016-10-04 12:45:23", "=", "2016-10-04 12:45:23")
OUTPUT: TRUE

Notes:

Check if p1 operator p2 is TRUE. p1, p2 is Y-m-d H:i:s date, operator is <;>;<=;>=;!=;=.

DATE_FORMAT

Formula / Examples:

json

Name of the function: DATE_FORMAT
Number of parameters: 2
Type of parameters: input, format

INPUT: DATE_FORMAT("2016-10-04 12:45:23", "D M d H:i:s O Y")
OUTPUT: Tue Oct 04 12:45:23 +0000 2016

Notes:

Reformat input date for format, which should be a valid PHP format string.

DATE_DEFAULT

Formula / Examples:

json

Name of the function: DATE_DEFAULT
Number of parameters: 1
Type of parameters: format

INPUT: DATE_DEFAULT("D M d H:i:s O Y")
OUTPUT: Fri Nov 17 09:29:26 -0500 2017

Notes:

Reformat current date for format to the default format.

DATE_READ

Formula / Examples:

json

Name of the function: DATE_READ
Number of parameters: 2
Type of parameters: datestring, format

INPUT: DATE_READ("01/02/03","d/m/y")
OUTPUT: 2003-02-01 00:00:00

Notes:

Reads an arbitrary formatted date, and converts it to the default format

Function: Math Functions#

SQR

Formula / Examples:

json

Name of the function: SQR
Number of parameters: 1
Type of parameters: number

INPUT: SQR(451)
OUTPUT: 203401

Notes:

A square of X is a number R such that R=X^2.

SIN

Formula / Examples:

json

Name of the function: SIN
Number of parameters: 1
Type of parameters: number

INPUT: SIN(1)
OUTPUT: 0.84{...}

Notes:

The sine function SIN(X). X is a real-type expression. SIN returns the sine of the angle X in radians.

COS

Formula / Examples:

json

Name of the function: COS
Number of parameters: 1
Type of parameters: number

INPUT: COS(1)
OUTPUT: 0.54{...}

Notes:

The cosine function COS(X). X is a real-type expression. COS returns the cosine of the angle X in radians.

ATAN

Formula / Examples:

json

Name of the function: ATAN
Number of parameters: 1
Type of parameters: number

INPUT: ATAN(1)
OUTPUT: 0.79{...}

Notes:

The inverse tangent function ATAN(X).

SINH

Formula / Examples:

json

Name of the function: SINH
Number of parameters: 1
Type of parameters: number

INPUT: SINH(1)
OUTPUT: 1.18{...}

Notes:

The hyperbolic sine function SINH(X).

COSH

Formula / Examples:

json

Name of the function: COSH
Number of parameters: 1
Type of parameters: number

INPUT: COSH(1)
OUTPUT: 1.54{...}

Notes:

The hyperbolic cosine function COSH(X).

COTAN

Formula / Examples:

json

Name of the function: COTAN
Number of parameters: 1
Type of parameters: number

INPUT: COTAN(1)
OUTPUT: 0.64{...}

Notes:

The cotangent function COTAN(X)

TAN

Formula / Examples:

json

Name of the function: TAN
Number of parameters: 1
Type of parameters: number

INPUT: TAN(1)
OUTPUT: 1.56{...}

Notes:

The tangent function TAN(X).

EXP

Formula / Examples:

json

Name of the function: EXP
Number of parameters: 1
Type of parameters: number

INPUT: EXP(1)
OUTPUT: 2.72{...}

Notes:

The exponential function EXP(X).

LN

Formula / Examples:

json

Name of the function: LN
Number of parameters: 1
Type of parameters: number

INPUT: LN(451)
OUTPUT: 6.11{...}

Notes:

The natural logarithm LN(X) is the logarithm having base e, where e=2.718281828.…

LOG

Formula / Examples:

json

Name of the function: LOG
Number of parameters: 1
Type of parameters: number

INPUT: LOG(451)
OUTPUT: 2.65{...}

Notes:

Gives the natural logarithm of X (logarithm to base 10).

SQRT

Formula / Examples:

json

Name of the function: SQRT
Number of parameters: 1
Type of parameters: number

INPUT: SQRT(203401)
OUTPUT: 451

Notes:

A square root of X is a number R such that R^2=X.

ABS

Formula / Examples:

json

Name of the function: ABS
Number of parameters: 1
Type of parameters: number

INPUT: ABS(-451)
OUTPUT: 451

Notes:

The absolute value of a real number X is denoted ABS(X) and defined as the "unsigned" portion of X.

SIGN

Formula / Examples:

json

Name of the function: SINH
Number of parameters: 1
Type of parameters: number

INPUT: SIGN(451)
OUTPUT: 1

INPUT: SIGN(-451)
OUTPUT: -1

Notes:

The sign of a real number, also called sgn or signum, is -1 for a negative number (i.e., one with a minus sign "-"), 0 for the number zero, or +1 for a positive number (i.e., one with a plus sign "+").

TRUNC

Formula / Examples:

json

Name of the function: TRUNC
Number of parameters: 1
Type of parameters: number

INPUT: TRUNC(-3.2)
OUTPUT: -3

INPUT: TRUNC(3.2)
OUTPUT: 3

Notes:

To truncate a real number is to discard its noninteger part. Truncation of a (positive) number X therefore corresponds to taking the floor function FLOOR.

CEIL

Formula / Examples:

json

Name of the function: CEIL
Number of parameters: 1
Type of parameters: number

INPUT: CEIL(-3.2)
OUTPUT: -3

INPUT: CEIL(3.2)
OUTPUT: 4

Notes:

The ceiling function CEIL gives the smallest integer >=X.

FLOOR

Formula / Examples:

json

Name of the function: FLOOR
Number of parameters: 1
Type of parameters: number

INPUT: FLOOR(-3.2)
OUTPUT: -4

INPUT: FLOOR(3.2)
OUTPUT: 3

Notes:

The floor function FLOOR, also called the greatest integer function or integer value, gives the largest integer less than or equal to X.

RND

Formula / Examples:

json

Name of the function: RND
Number of parameters: 0
Type of parameters: number

INPUT: RND()
OUTPUT: 0.3{...}

Notes:

The random function RND generates a random number (double value) between 0 and 1.

VAL

Formula / Examples:

json

Name of the function: VAL
Number of parameters: 1
Type of parameters: number

INPUT: VAL("3.1")
OUTPUT: 3.1

Notes:

The value function VAL returns the floating point numeric value of the string argument.

POW

Formula / Examples:

json

Name of the function: POW
Number of parameters: 2
Type of parameters: number, number

INPUT: POW(451, 2)
OUTPUT: 203401

Notes:

The power function POW raises Base to any power. For fractional exponents or exponents greater than MaxInt, Base must be greater than 0.

LOGN

Formula / Examples:

json

Name of the function: LOGN
Number of parameters: 2
Type of parameters: number, number

INPUT: LOGN(10, 100)
OUTPUT: 2

Notes:

The LogN function LOGN returns the log base N of X.

MIN

Formula / Examples:

json

Name of the function: MIN
Number of parameters: 2
Type of parameters: number

INPUT: MIN(2, 3)
OUTPUT: 2

Notes:

The minimum function MIN gives the smallest value of a set.

MAX

Formula / Examples:

json

Name of the function: MAX
Number of parameters: 2
Type of parameters: number

INPUT: MAX(2, 3)
OUTPUT: 3

Notes:

The maximum function MAX gives the largest value of a set.

MOD

Formula / Examples:

json

Name of the function: MOD
Number of parameters: 2
Type of parameters: number, number

INPUT: MOD(451, 2)
OUTPUT: 1

Notes:

The mod function MOD gives the remainder on division of M by N.

NUM

Formula / Examples:

json

Name of the function: NUM
Number of parameters: 1
Type of parameters: number

INPUT: NUM("$100.00")
OUTPUT: 100.00

Notes:

To number function tries to convert an expression to a number.

SUM

Formula / Examples:

json

Name of the function: SUM
Number of parameters: 1
Type of parameters: number

INPUT: SUM(1, 2, 3, 4)
OUTPUT: 10

Notes:

A sum is the result of an addition. There is no preset limit on the number of parameters.

Basic Examples

Below are some examples for formulas that could be used when importing or exporting data with Element451.

Function: Defaults#

(Setting a field to a specific text value)

Adding a label to a record

Element Slug Examples:

user-labels-import

Formula:

(will add the “Alumni” label to a record in the “training” Element instance)

json

training.taxonomy.2897204

Element451 Labels list showing the Alumni label with its taxonomy GUID training.taxonomy.2897204 — Element451 Labels page, 214 Labels, with columns Name, Description, Color, Slug and Taxonomy. The single row shown is Alumni, no description, a teal color swatch, slug alumni and taxonomy training.taxonomy.2897204, which is the value used in the formula.

Setting an application status to “Submitted”

Element Slug Examples:

user-applications-status

Formula:

(will set the status to Submitted by passing the text string “Submitted” to this slug)

json

"Submitted"

Function: IF#

A Single IF Statement

Formula:

json

IF(condition, do this, else do this)

A Nested IF Statement

Formula:

json

IF(condition, do this, 

	IF(condition, do this,

		IF(condition, do this, else do this)
	)
)

If Statement for Hispanic/Latino

Formula (option 1):

json

IF(column ="Hispanic/Latino","TRUE",

	IF(column ="Non-Hispanic/Latino","FALSE", "" )
)

Formula (option 2):

json

IF (column = "Y", "TRUE",  " ")

Map Race when split OR different options in same column

Formula

json

IF([C#]="1",3,IF([C#]="2",1,IF([C#]="3",5,IF([C#]="5",2,IF([C#]="6",4,"")))))

Function: Dates#

Insert today’s date (typically used for date of inquiry)

Formula

json

DATE_DEFAULT("m/d/Y")

Add a value based on the Date of Birth (year) range

Formula

json

IF(DATE_DIFF("year",DATE_DEFAULT("Y"),DATE_FORMAT(RIGHT([C12],4),"Y"))>25,"value 1","value 2")

Add Graduation Date when only given a year

Formula

json

DATE_READ(CONCAT("01/05/",[C#]),"d/m/Y")

Dynamically add to today’s date and adjust to timezone

Element Slug Examples:

user-milestones….(any date milestone)
user-sources-source-code-date
….(any date field)

Formula:

(adds 4 hours to UTC time, adjusting to Eastern Timezone)

json

DATE_ADD("hour", 4, DATE_READ(DATE_DEFAULT("m/d/Y"), "m/d/Y"))

Pad Date of Birth when given as one string

Formula

json

DATE_READ(PAD([C29],"0",8),"mdY")

Conditional date formatting

Formula

json

IF([C25]=”Suspect”,””,DATE_FORMAT([C110],”m/d/y”))

Function: Strings#

Only import first 5 digits of Zipcode and pad if needed with leading 0

Formula

json

SUBSTR(IF(STRLEN([C13])=4,PAD([C13],0,5),IF(STRLEN([C13])=8,PAD([C13],0,9),[C13])),0,5)

Grab Last 4 digits for +4 Zipcode

Formula

json

IF(STRLEN([C28])>5,SUBSTR([C28],-4,4),"")

Concatenate two values into one with a spacer

Formula

json

IF([C36],CONCAT([C35],”-”,[C36]),””)

Function: DB_MAP#

Add Intended Term when only given a year (assuming all are Fall)

Formula

json

DB_MAP("term",CONCAT("Fall ",[C#]), "name", "guid", "")

Mapping an external term to an Element term.

Element Slug Examples:

user-applications-term
user-education-term

Formula:

json

DB_MAP(”term”, “column”, “name”, “guid”, “if untranslatable”)

Mapping an external major to an Element major.

Element Slug Examples:

user-applications-major
user-education-prefered-major

Formula:

json

DB_MAP(”major”, “column”, “name”, “guid”, “if untranslatable”)

Conditional DB_MAP

Formula

json

DB_MAP(“major”,IF([C8],[C8],[C6]),”code”,”guid”,””)

Function: SPLIT_INDEX#

Splitting a Parent Full Name into First Name

Element Slug Examples:

user-family-guardian-1-first-name
user-family-guardian-2-first-name

Formula:

json

SPLIT_INDEX(column, " ", 1)

Splitting a Parent Full Name into Last Name

Element Slug Examples:

user-family-guardian-1-last-name
user-family-guardian-2-last-name

Formula:

json

SPLIT_INDEX(column, " ", 2)

Function: DS_MAP#

Match an External Value to an Internal Data Source Value

Element Slug Examples:

user-applications-major
user-educationa-prefered-major
…(any data source field)

Formula:

json

DS_MAP(”column”, “client.data_source”, “name”, “guid”, “if untranslatable”)

Match an External Value to an Internal Data Source Value if Nested

(if the data source has nested values, here is how to map correctly)

Element Slug Examples:

user-applications-major
user-educationa-prefered-major
…(any data source field)

Formula:

json

DS_MAP(
    (DS_MAP([C28],"client.data_source.1599","column_2","column_1", 
        DS_MAP([C28],"client.data_source.1611","column_1","column_2", 
            DS_MAP([C28],"client.data_source.1636","column_2","column_1", 
                DS_MAP([C28],"client.data_source.1635","column_2","column_1", 
                    DB_MAP("major",[C28],"cip","code","mnm")
                )
            )
        )
    )
), "data_source.majors", "code", "guid", "client.major.2996")

Function: TAXONOMY_MAP#

Import Taxonomies

This can be used to correctly map to sources, source segments, statuses, or anything that uses taxonomies inside Element451.

Formula:

json

TAXONOMY_MAP(CODE,"value","guid","")

Export Decision Board Status Name

This can be used to export the name of the decision board status rather than the taxonomy code.

Element Slug Examples:

user-applications-board-status

Formula:

json

TAXONOMY_MAP([user-applications-board-status],"guid","name","value if not found")