Formula Reference

Concatenate text values into a single text value.

To concatenate static text, surround it with double quotation marks. To concatenate double quotation marks, you need to use a backslash (\) as an escape character.

Equivalent to the concat(text) function.

Name + " - " + Age

=> Jane - 28

"\""+ProductName+"\""

=> "GreatProduct"

Extracts a single character from a text value.

Equivalent to the charAt(number) function.

"John Smith"[0]

=> J

"yes" == "no"

=> false

"yes"[0] == "y"

=> true

"yes" != "no"

=> true

"yes"[0] != "y"

=> false

Determines whether one text value precedes another in alphabetical order.

Roughly corresponds to the localeCompare(text) function returning -1.

"ant" < "boy"

=> true

"boy" < "ant"

=> false

Determines whether one text value precedes or matches another in alphabetical order.

Roughly corresponds to the localeCompare(text) function returning either 0 or -1.

"ant" <= "ant"

=> true

"ant" <= "boy"

=> true

Determines whether one text value follows another in alphabetical order.

Roughly corresponds to the localeCompare(text) function returning a positive 1.

"ant" > "boy"

=> false

"boy" > "ant"

=> true

Determines whether one text value follows or matches another in alphabetical order.

Roughly corresponds to the localeCompare(text) function returning either 0 or 1.

"ant" >= "ant"

=> true

"ant" >= "boy"

=> false

Makes a text value uppercase.

Equivalent to the toUpperCase() function.

"Hello!"|upper

=> HELLO!

Makes a text value lowercase.

Equivalent to the toLowerCase() function.

"Hello!"|lower

=> hello!

"macDonald"|initcap

=> MacDonald

"macDonald"|initcap:true

=> Macdonald

"marty mcFly"|titlecaps

=> Marty McFly

"marty mcFly"|titlecaps:true

=> Marty Mcfly

Determines if a text value contains another text value. The search is case sensitive.

Equivalent to the includes(text) function.

"MacDonald"|contains:"Don"

=> true

"MacDonald"|contains:"old"

=> false

ClientName|else:"client"

=> John

""|else:"client"

=> client

Extracts a single character from a text value at the position indicated by number.

Equivalent to use of the [ ] operator.

"John Smith".charAt(5)

=> S

"ABC".charCodeAt(0)

=> 65

"☃★♲".codePointAt(1)

=> 9733

Concatenates the text arguments onto the "calling" text value.

To concatenate static text, surround it with double quotation marks. To concatenate double quotation marks, you need to use a backslash (\) as an escape character.

Roughly equivalent to use of the + operator.

Name.concat(" – ", Age)

=> Bob - 43

"".concat("\"", ProductName, "\"")

=> "GreatProduct"

"brown fox".endsWith("fox")

=> true

"quick brown fox".first(5)

=> quick

Determines whether textValue contains a specific substring (text) or not. The search is case sensitive.

If startAt is specified, it searches textValue only from that position onwards.

Similar to the contains filter.

"quick fox".includes("ick")

=> true

"quick ox".includes("ick",3)

=> false

"brown fox".indexOf("fox")

=> 6

"brown fox".indexOf("o", 3)

=> 7

"quick brown fox".last(3)

=> fox

"brown fox".lastIndexOf("o")

=> 7

"brown fox".lastIndexOf("o", 6)

=> 2

"ant".localeCompare("boy")

=> -1 => ant precedes boy

"boy".localeCompare("boy")

=> 0 => no difference in sort order

"boy".localeCompare("ant")

=> 1 => boy follows ant

"quick brown fox".length

=> 15

Pads textValue by appending a given padding string (repeated, if needed) so the resulting value reaches the given length.

If padding is omitted, spaces are used by default.

"boy".padEnd(5, "y")

=> boyyy

Pads textValue by prepending a given padding string (repeated, if needed) so the resulting value reaches the given length.

If padding is omitted, spaces are used by default.

"hat?".padStart(7, "wh")

=> whwhat?

"beep ".repeat(3)

=> beep beep beep

"how now".replace("ow", "at")

=> hat now

"big dig".replace("i", "o")

=> bog dog

Searches within textValue for a substring that matches pattern, and returns the position in the string (if found) or -1 (if not found).

pattern is a text value containing a JavaScript regular expression, although RegExp flags (g, i, m, etc.) are not allowed.

"356".search("^[0-9]+$")

=> 0

"35a6".search("^[0-9]+$")

=> -1

Extracts the part of textValue that begins at start and ends before end.

If start or end is negative, it counts backwards from the end of textValue rather than from the beginning.

If end is omitted, the extracted value will go from start to the end of textValue.

"abcde".slice(1)

=> bcde

"abcde".slice(1, 4)

=> bcd

"abcde".slice(0, -2)

=> abc

Turns a text value into a list of text values by splitting it at each instance of separator.

If separator contains multiple characters, that entire character sequence must be found in order to split.

If separator is omitted or does not occur in textValue, the returned list will contain only one item (textValue).

If separator appears at the beginning or end of textValue, or both, the array begins, ends, or both begins and ends, respectively, with an empty text value.

If separator is an empty text value (""), the result is a list of text values, each a single character of textValue.

"a/b/c".split("/")

=> [ "a", "b", "c" ]

"Hello there world".split(" ", 3)

=> [ "Hello", "there", "" ]

(Two spaces after "there" causes an empty text value in the list, and the limit of 3 caps the number of items returned.)

"brown fox".startsWith("fox")

=> false

Strips any occurrences of the given characters from the start, the end, or both the start and end of textValue.

If fromEnd is omitted, it defaults to true. If both fromStart and fromEnd are omitted, they both default to true.

"aaabccc".strip("ac")

=> b

"aaabccc".strip("ac", false)

=> aaab

"aaabccc".strip("ac", true, false)

=> bccc

"an old fox".substr(3,3)

=> old

"an old fox".substr(3)

=> old fox

"an old fox".substring(3,6)

=> old

"an old fox".substring(3)

=> old fox

2 * ("32".toInt())

=> 64

Makes textValue lowercase.

Equivalent to lower filter.

"Hello!".toLowerCase()

=> hello!

Makes textValue uppercase.

Equivalent to upper filter.

"Hello!".toUpperCase()

=> HELLO!

Removes whitespace from both the beginning and end of textValue.

Whitespace includes spaces, non-breaking spaces, tabs, and line termination characters.

" Hello world! ".trim()

=> Hello world!

text.fromCharCode(189, 43, 190, 61)

=> ½+¾=

text.fromCodePoint(9731, 9733, 9842, 0x2F804)

=> ☃★♲你

Determines whether any value is equal to another value.

Special note: you must use two equals signs. In JavaScript, a single equals (=) is used to assign a value to a variable. Knackly does not support this syntax.

2 == 2

=> true

3 != 2

=> true

3 < 5

=> true

date.today < date.new(2020,1,1)

5 <= 5

=> true

Logical AND: the && operator will return a "truthy" value only if both its operands are "truthy".

If the left-hand operand is falsy, the result will be whatever the value of the left-hand operand is.

If the left-hand operand is truthy, the result will be whatever the value of the right-hand operand is.

true && true

=> true

Client && Client.Name

=> John Smith (if Client is non-null)

=> null (if Client is null/not yet answered)

"" && 5

=> ""

Logical OR: the || operator will return a "truthy" value if either of its operands are "truthy".

If the left-hand operand is truthy, the result will be whatever the value of the left-hand operand is.

If the left-hand operand is falsy, the result will be whatever the value of the right-hand operand is.

false || true

=> true

false || false

=> false

!true

=> false

!false

=> true

!Client

=> false (if Client is answered)

=> true (if Client is null / not answered)

Simple example:

Sales > 50 ? "Win" : "Lose"

Nested and cleanly formatted:

WaterTemp > 100

? WaterTemp < 212

? "just right"

: "too hot"

: "too cold"

Testing for "answeredness":

BirthDate

? "Birth date entered"

: "Please enter a birth date"

10 % 3

=> 1

NumPeople % NumTables

10000|format:"0,0.00"

=> 10,000.00

-5.5 | format: "0 %"

=> -550 %

25 | format: "0000"

=> 0025

28 | format: "A"

=> AB

123|cardinal

=> one hundred twenty-three

123|ordinal

=> one hundred twenty-third

the {[Day]}{[Day|ordsuffix]} of the month

=> the 22nd of the month

math.abs(-5)

=> 5

math.ceil(1.01)

=> 2

math.ceil(-1.01)

=> -1

math.exp(1)

=> 2.71828

math.exp(3)

=> 20.08554

math.floor(1.99)

=> 1

math.log10(1000)

=> 3

math.log2(1024)

=> 10

math.pow(3, 3)

=> 27

math.pow(7, 0)

=> 1

The value of the given number (x) rounded to the nearest integer.

If the fractional portion of the argument is greater than 0.5, the argument is rounded to the integer with the next higher absolute value. If it is less than 0.5, the argument is rounded to the integer with the lower absolute value. If the fractional portion is exactly 0.5, the argument is rounded to the next integer in the direction of +∞.

math.round(3.5, 0)

=> 4

math.round(3.4, 0)

=> 3

math.sqrt(100)

=> 10

math.E

=> 2.71828

2-agrument arctangent (2-argument inverse tangent)

the angle in the plane (in radians) between the positive x-axis and the ray from (0,0) to the point (x,y)

Future value of an investment with periodic constant payments and a constant interest rate.

rate: interest rate per period

nper: number of periods for the lifetime of the annuity

pmt: payment amount per period

pv: present value of the annuity (if omitted, defaults to 0)

type: whether payments made at start or end of each period

0 = payments at end of period (default)

1 = payments at start of period

Future value of an investment of $1,000 per month for a period of 5 years. The present value is 0, interest rate is 5% per year and payments are made at the end of each month:

finance.FV(0.05/12, 5*12, -1000)

=> 68,006.08

Future value of an investment of $2,000 per quarter for a period of 4 years. The interest is 10% annually and payment are made at the start of the quarter:

finance.FV(0.1/4, 4*4, -2000, 0, 1)

=> 39,729.46

Present value of a loan or an investment, based on a constant interest rate.

rate: interest rate per period

nper: number of periods for the lifetime of the annuity

pmt: payment amount per period

fv: future value of the annuity (if omitted, defaults to 0)

type: whether payments made at start or end of each period

0 = payments at end of period (default)

1 = payments at start of period

Present value of an annuity that pays $1,000 per month for a period of 5 years. The interest is 5% annually and payments are made at the end of the month.

finance.PV(0.05/12, 5*12, 1000)

=> -52,990.71

Present value of an annuity of $2,000 per quarter for a period of 4 years. The interest is 10% per year and payments are made at the start of the quarter.

finance.PV(0.1/4, 4*4, 2000, 0, 1)

=> -26,762.76

Extracts only the day (of the month) from dateValue.

Equivalent to date.dayOf().

NewYearsEve.getDate()

=> 31

Returns the day of the week for dateValue, where 0 represents Sunday and 6 represents Saturday.

Equivalent to date.dayOfWeek().

MemorialDay.getDay()

=> 1

Extracts only the year from dateValue.

Equivalent to date.yearOf().

KnacklyOrigin.getFullYear()

=> 2019

Extracts only the month from dateValue.

NOTE: This returns a zero-based month, where 0 is January, 1 is February, … and 11 is December.

For 1-based months, either add one to the result, or see the date.monthOf() helper function.

ValentinesDay.getMonth()

=> 1

Tests whether date1 is after date2 chronologically.

Equivalent to date1 > date2 (?)

Tests whether date1 is before date2 chronologically.

Equivalent to date1 < date2 (?)

Tests whether date1 and date2 represent the same day.

Equivalent to date1 == date2 (?)

Tests whether date1 is NOT after date2 chronologically. In other words, date1 is no later than date2, or, date1 is before or on date2.

Equivalent to date1 <= date2 (?)

Tests whether date1 is NOT before date2 chronologically. In other words, date1 is no earlier than date2, or, date1 is on or after date2.

Equivalent to date1 >= date2 (?)

Tests whether date1 and date2 represent different days.

Equivalent to date1 != date2 (?)

date 10 days after openingDate:

date.addDays(openingDate, 10)

date 3 months after startDate:

date.addMonths(startDate, 3)

date 8 weeks after startDate:

date.addWeeks(startDate, 8)

date 2 years after startDate:

date.addYears(startDate, 2)

date.age(BirthDate)

=> 27

Returns the day portion (day of the month) of dateValue in the form of a number between 1 and 31.

Equivalent to dateValue.getDate()

date.dayOf(Christmas)

=> 25

Returns the day of the week for dateValue, where 0 represents Sunday and 6 represents Saturday.

Equivalent to dateValue.getDay()

Returns the month portion of dateValue as a number between 1 (January) and 12 (December).

Note the difference in numbering with dateValue.getMonth()

date 10 days before openingDate:

date.subDays(openingDate, 10)

date 3 months before startDate:

date.subMonths(startDate, 3)

date 8 weeks before startDate:

date.subWeeks(startDate, 8)

date 2 years before startDate:

date.subYears(startDate, 2)

Returns the current date in the local time zone.

(Note that the results of this function changes only when the formula is recalculated. It does not update continuously.)

"Format" a true/false value as one of the provided text arguments.

Note: the conditional operator can do the same thing more concisely (coin ? "heads" : "tails"), so this is not very useful.

coin|format:"heads":"tails"

=> "heads"

Children.length

=> 4

Children[0]

=> the first child in the Children list

Produces a true/false value indicating whether the given expression (predicate) is true when evaluated against any item in the input list.

The filter stops evaluating as soon as it finds one item in the list where the expression evaluates to a truthy value.

This filter can also be used under the alias of some.

Do any children have a name that starts with C?

Children|any:Name.startsWith("C")

=> true

Produces a true/false value indicating whether the given value exists in the input list.

Equivalent to the includes function.

Does a list of text values contain a specific text value?

TextList|contains:"this value"

=> false

Produces a true/false value indicating whether the given expression (predicate) is true when evaluated against EVERY item in the input list.

The filter stops evaluating as soon as it finds one item in the list where the expression evaluates to a falsy value.

This filter can also be used under the alias of all.

Are all the children married?

Children|every:IsMarried

=> false

List all of the minor children:

Children|filter:

date.age(BirthDate)<18

Get the party designated as executor:

Parties|find:Role=="Executor"

Produces a list of groups or categories from a flat input list.

The given expression (expr) is evaluated against each item in the input list. An output group is created for each unique result of that evaluation.

Each item in the output list (that is, each output group) is an object with two properties:

_key: the result of evaluating expr

_values: a list of the same type as the input list

List parties by relationship to the client:

{[list AllParties|group:

RelationshipToClient]}

Client's {[_key]}s:

{[list _values]}

{[Name]}

{[endlist]}

{[endlist]}

Produces a list of items where each item in the input list "maps" to exactly one item in the output list.

The given expression (expr) is evaluated against each item in the input list, and the result of that evaluation is placed in the output list.

Produce a list of names (simple text values) from a list of children:

Children|map:FullName

Specifies a punctuation style for the list to which it is applied. Only has an effect when used in a {[list]} field in a template.

Punctuation example strings consist of the digits (tokens) 1, 2, and optionally 3, separated by other characters as a way of indicating how items in this list should be joined together in text.

If no example is provided, "1, 2, and 3" is assumed.

Note: Unlike the rest of the list filters, this one outputs the same list that was passed into it.

{[list Children|punc]}‌{[Name]} ‌{[endlist]}

=> John, Mary, Susan, and Edward

{[list Boys|punc]}‌{[Name]} ‌{[endlist]}

=> John and Edward

{[list Children|punc:"1, 2."]}‌{[Name]} ‌{[endlist]}

=> John, Mary, Susan, Edward.

{[list Children|punc:"1, 2 and 3"]}‌{[Name]} ‌{[endlist]}

=> John, Mary, Susan and Edward

{[list Girls|punc:"1, 2, or 3?"]}‌{[Name]} ‌{[endlist]}

=> Mary or Susan?

Produces a new value by evaluating the given reducer expression against each item in the input list. Typically follows one of two models:

It can "reduce" the input list to a single value, such as when you want to calculate a value that requires some input from each item in the input list.

Or it can "accumulate" a new list as it goes through each item in the input list. Items in this new list need not have a 1:1 correspondence with the input items.

You can optionally specify an initial value for the output. For example, if the input list were empty, the output value would be whatever was specified as initial. If you do not specify an initial value, the first item in the input list becomes the initial value.

Reducer expressions have access to a special local variable called _result., which is of the same type as either the initial value or the first item in the input list. _result is always set to the result of the previous item's evaluation. Think of _result as a kind of bridge between the evaluation of each item in the input.

Sum a list of numbers:

NumberList|reduce:_result + this

Do the same thing as the map filter, mapping a list of Children to a list of their names:

Children|reduce:_result.concat(

FullName):[]

Do the same thing as the filter filter, conditionally adding items to the result only if the child's age is <18:

Children|reduce:

date.age(BirthDate)<18

? _result.concat(this)

: _result

:[]

Flatten a nested list. Each child has a list of toys, and each toy has a ToyName, but we need a flat list of ALL the toy names:

Children|reduce:

_result.concat(Toys|map:ToyName)

:[]

Changes the sort order of the list to which it is applied.

Sort keys are expressions relative to each item in the list. Expressions will be evaluated for each item in the list, and the result is used to determine the order of the resulting list.

Specifying a sort with multiple keys:

Parties|sort:LastName:FirstName

Descending (reversed) sort order uses a minus sign:

Matters|sort: -StartDate

Sorting a list of text values (instead of objects):

TextList|sort: this

[1, 2, 3].concat(4, 5, [6, 7, 8])

=> [1, 2, 3, 4, 5, 6, 7, 8]

Determines whether a list includes a certain item among its entries, returning true or false as appropriate.

If start is specified, it ignores any items in the list prior to that index.

Equivalent to the contains filter.

Creates and returns a text value by concatenating all the items in a list of text values, separated by a specified separator value.

If no separator is provided, an unspaced comma (",") will be used.

If the list has only one text value, that value will be returned without using the separator.

If the list contains items other than text values, they will be coerced to text and you'll probably get something ugly.

Extracts a portion of a list from begin (inclusive) to end (exclusive) where begin and end represent zero-based indices of items in the list.

begin: Index at which to begin extraction. A negative index can be used, indicating an offset from the end of the list.  If begin is omitted, slice begins from index 0. If begin is greater than the length of the list, an empty list is returned.

end: Index before which to end extraction. A negative index can be used, indicating an offset from the end of the list. If end is omitted or is greater than the length of the list, slice extracts through the end of the list.