Field Formulas

Field formulas allow you to perform logic, date, and math formulas on the contents of a field. These formulas reference values in other fields, allowing you to perform operations like concatenating two fields, adding or averaging the values of several number fields, and date calculations such as number of years between two dates. The formula is dependent on values of other fields; it will appear blank until those fields are filled. Once the field has been populated and the user has clicked out of the field, the formula will be applied to that value. Additional fields populated will cause the formula to update the value.

The formula is dependent on values of other fields; it will appear blank until those fields are filled. Once the field has been populated and the user has clicked out of the field, the formula will be applied to that value. Additional fields populated will cause the formula to update the value.

You can also specify that the field should be read-only, so that users can't modify the calculated value.

Field formulas can only be applied and used in the Laserfiche Repository Web Client. The formulas you create will not be applied to documents through other applications such as Workflow, Import Agent, or the Repository Desktop Client.

Adding a Formula to a Field

To add a field formula to a field, perform the following steps:

  1. Open the Laserfiche Repository Web Client and select your user name in the upper right corner, then select Administration.
  2. Select the Metadata section in the left-hand menu.
  3. Select the Templates tab.
  4. Open the template to which you want to add a formula. See Creating and Modifying Fields and Templates if you need to create a new field.
  5. Determine the fields that will contribute to the template, and the field that will contain the final value. For example, if you want to average several fields, you would first identify or create the fields containing the numbers to be averaged. and then identify or create a field to contain the final average.
  6. Select the field that will contain the final value. In the panel to the right, select the Advanced tab.
  7. In the Formula option, type your formula. See Formula Reference, below, for more information. You can also select the Insert function and Insert field buttons to display and select from a list of supported formulas and a list of field in the template.
  8. Optional: To ensure that users don't change the final calculation, you can select the Display field as read-only option. Note that this is not a security feature; if the field exists in another template, it will not be read-only there.
  9. Select Save to save your formula.

Examples

Adding Field Values with =SUM()

An administrator wants to create a formula to add the values of three numbers (fields "One," "Two," and "Three,") and store it in a fourth "Total" field. The administrator could create a formula in the "Total" field and use the =SUM function to add the three other fields, with a syntax of =SUM(FIELD("One", "Two","Three")). When a user populates a any of the three fields that are being added together, and then clicks out of that field, the formula in the field "Total" will be updated. When all three of the fields are filled, the "Total" field will show the final value.

Concatenating Field Values with =CONCATENATE()

The template you are updating contains a "First Name" field, a "Last Name" field, and a "Full Name" field. An administrator has configured the "Full Name" field to concatenate the other two fields, last name first, using the formula =CONCATENATE(FIELD("Last Name"),", ",FIELD("First Name")). When a user populates a value in the "Last Name" field and clicks out of that field, the last name will be inserted into the "Full Name" field; when the "First Name" field is populated, its value will also be inserted in the "Full Name" field. This also works if the "First Name" field is populated before the "Last Name" field.

Formula Reference

The supported formulas for a field are in the OpenFormula standard. The OpenFormula standard is the same standard used by Microsoft Excel and Google Sheets. Note that the formula names are case sensitive and should be written in all caps.

Formulas
Name Format Description
ABS ABS(value)

The ABS function returns the absolute value of the specified value.

"=ABS(3)" returns "3"

"=ABS(-3)" returns "3"

"=ABS(a)" returns nothing
ADD ADD(value1, value2)

The ADD function returns the sum of two values.

"=ADD(3, 3)" returns "6"
ADD_MONTHS ADD_MONTHS(date, months)

The ADD_MONTHS function adds months to a date object and returns a date object.

"=ADD_MONTHS(DATE(2015, 8, 11), 4)" returns "42349"
AND AND(value1, value2, ...)

The AND function returns "true" if all specified values are true; otherwise, it returns false.

"=AND(true, false)" returns "false"
AVERAGE AVERAGE(value1, value2, ...)

The AVERAGE function returns the average of a list of values.

"=AVERAGE(3, 4, 5, 6)" returns "4.5"
AVERAGEIF AVERAGEIF([value1, value2, ...], criterion)

The AVERAGEIF function returns the average of a list of number values that satisfy the criterion.

"=AVERAGEIF([1, 2, 3], ">2")" returns "3"
AVG AVG(value1, value2, ...)

The AVG function returns the average of a list of values.

"=AVG(3, 4, 5, 6)" returns "4.5"
CEILING CEILING(value, factor)

The CEILING function rounds the specified value up to the nearest multiple of the factor.

"=CEILING(10.1, 2)" returns "12"
COLUMN COLUMN()

The COLUMN function returns the column number of a cell in a table. The first cell has a value of 1.

"=COLUMN()" returns "1" for a cell in column 1

"=COLUMN()=0" returns "TRUE" if the field is outside the table/collection
CONCATENATE CONCATENATE(value1, value2, ...)

The CONCATENATE function joins multiple strings together into one string. Also see JOIN.

"=CONCATENATE("a", "b", "c")" returns "abc"

"=CONCATENATE(CHAR(76), "aserfiche")" returns "Laserfiche"
COUNT COUNT(value1, value2, ...)

The COUNT function tallies the number of non-numeric, listed values.

"=COUNT(3, 2, 7, 4, 6)" returns "5"
COUNTIF COUNTIF([value1, value2, ...], "condition")

The COUNTIF function tallies the number of listed values that satisfy the condition.

"=COUNTIF([1, 4, 6], ">1")" returns "2"
DATE DATE(year, month, day)

The DATE function returns a date object representation of a date. You can use this date object in various other functions, including DAY, MONTH, and YEAR.

"=DATE(1985, 12, 2)" returns "31383"

Note: When used with a date-based variable or field, the date will be formatted as defined by the field or variable.
DATEDIF DATEDIF(start_date, end_date, time_unit)

The DATEDIF function calculates the difference between two date objects based on a specific unit.

"=DATEDIF(DATE(2017, 1, 2), DATE(2017, 1, 14), "M")" returns "0"

"=DATEDIF(DATE(2017, 1, 2), DATE(2017, 1, 14), "D")" returns "12"
DATEVALUE DATEVALUE(dateString)

The DATEVALUE function converts a date (stored as text) into a date object.

"=DATEVALUE("2017-1-14")" returns "42749"
DAY DAY(dateValue)

The DAY function returns the day of a given date.

"=DAY(TODAY())" returns "2" on January 2nd

"=DAY(DATE(1985, 12, 2))" returns "2"
DAYS360 DAYS360(start_date, end_date)

The DAYS360 function returns the number of days between two given dates. This is sometimes used in financial interest calculations.

"=DAYS360(DATE(2017, 1, 2), DATE(2018, 1, 3))" returns "361" because this is one day after a full year
DIV DIV(value1, value2)

The DIV function divides the first value by the second value.

"=DIV(2,4)" returns "0.5"

"=DIV(SUB(4,2), 2)" returns "1"

"=DIV(SUM(2, 4), COUNT(2, 4))" returns 3 (the average of "2" and "4').
EDATE EDATE(DateObject, months)

The EDATE function adds an integer number of months to a date object to return another date object. If the months are non-integer values, then the function is rounded and then added.

"=EDATE(DATE(2017,1,2), 1)" returns "42768"

Note: When used with a date-based variable or field, the date will be formatted as defined by the field or variable.
EOMONTH EOMONTH(DateObject, months)

The EOMONTH function adds an integer number of months to a date object then returns a date object with the day as the end of that month.

"=EOMONTH(DATE(2017,1,2), 1)" returns "42794"

"=EOMONTH(DATE(2017,1,2), -1)" returns "42735"

Note: When used with a date-based variable or field, the date will be formatted as defined by the field or variable.
EQ EQ(value1, value2)

The EQ function returns "true" if the two values are equal, "false" otherwise.

"=EQ(2,3)" returns "FALSE"
FIND FIND(targetText, textToSearch, startingCharacter)

The FIND function returns the number of digits before finding the first value in the second value. The function performs the search starting with the starting character.

"=FIND("type", "prototype", 1)" returns "6"
FIXED FIXED(value, significance)

The FIXED function returns a number based on a specific number of significant digits.

"=FIXED(3.141592, 3)" returns "3.142"
FLOOR FLOOR(value, factor)

The FLOOR function rounds the specified value down to the nearest multiple of the factor.

"=FLOOR(11.9, 3)" returns "9"
GT GT(value1, value2)

The GT function returns "true" if the first value is greater than the second, "false" otherwise.

"=GT(2, 2)" returns "FALSE"
GTE GTE(value1, value2)

The GTE function returns "true" if the first value is greater than or equal to the second, "false" otherwise.

"=GTE(2, 2)" returns "TRUE"
IF IF(booleanValue, value1, value2)

The IF function returns the second value when the first value is true, otherwise returns the third value.

"=IF(11>10,"Accepted","Denied")" returns "Accepted"

"=IF(11<10,"Accepted","Denied")" returns "Denied"
INDEX INDEX(column, row)

The INDEX function returns a specific item in an array.

"=INDEX(Table.Column_1, 3)" returns the third item in column 1.
JOIN JOIN(delimiter, array)

The JOIN function concatenates the values in an array into one string. Each value is separate by the delimiter. Also see CONCATENATE.

"=JOIN("", ["L", "a", "s", "e", "r"])" returns "Laser"
LEFT LEFT(string, [characters])

The LEFT function returns all the characters from the left up to a specific number of characters.

"=LEFT("Laserfiche", 2)" returns "La"
LOWER LOWER(text)  

The LOWER function converts a text value into lowercase.

"=LOWER("POLICY")" returns "policy"
LT LT(value1, value2)

The LT function returns "true" if the first value is less than the second, "false" otherwise.

"=LT(2, 2)" returns "FALSE"
LTE LTE(value1, value2)

The LTE function returns "true" if the first value is less than or equal to the second, "false" otherwise.

"=LTE(2, 2)" returns "TRUE"
MAX MAX(value1, value2, ...)

The MAX function returns the maximum numerical value in a list of values. The function ignores non-numeric values.

"=MAX(1, 2, 4, 1, 2)" returns "4"
MIN MIN(value1, value2 ,...)

The MIN function returns the minimum numerical value in a list of values. The function ignores non-numeric values.

"=MIN(10, 3, -5)" returns "-5"
MINUS MINUS(value)

The MINUS function returns the opposite of the current number sign.

"=MINUS(1)" returns "-1"

" =MINUS(-1)" returns "1"
MOD MOD(value1, value2)

The MOD function returns the remainder from dividing the first number by the second. Learn more

"=MOD(33, 24)" returns "9"

MONTH MONTH(dateValue)

The MONTH function returns the month (from 1 to 12) in a date object.

"=MONTH(DATE(2017, 1, 20))" returns "1"
MULT MULT(value1, value2)

The MULT function returns the first value multiplied by the second.

"=MULT(3, 4)" returns "12"
NEQ NEQ(value1, value2)

The NEQ function returns "true" if the two values are not equal, "false" otherwise.

"=NEQ(2, 2)" returns "false"
NETWORKDAYS NETWORKDAYS(startDate, endDate, [holidays], [weekDays])

The NETWORKDAYS function returns the whole number of work days between two date objects.

"=NETWORKDAYS(DATE(2017, 1, 6), DATE(2017, 1, 14))" returns "6"

" =NETWORKDAYS(DATE(2022,10,17),DATE(2022,10,23),[DATE(2022,10,19),DATE(2022,10,20)])" returns "3"

"=NETWORKDAYS(DATE(2022,10,17),DATE(2022,10,23),[DATE(2022,10,19),DATE(2022,10,20)],[0,0,0,0,0,0,0])" returns "5"
NOT NOT(value)

The NOT function returns "true" if the value is false, or "false" if the value is true.

"=NOT(false)" returns "true"
NOW NOW()

The NOW function returns the current date and time.

"=SECOND(NOW())" returns "42"
OR OR(value1, value2, ...)

The OR function returns "true" if any of the specified values are true, "false" otherwise.

"=OR(TRUE, FALSE, TRUE)" returns "TRUE"
PROPER PROPER(text)

The PROPER function returns the specified text value with the first letter in a text string and any other letters in the string that follow any character other than a letter converted to uppercase.

"=PROPER("laserfiche")" returns "Laserfiche"

"=PROPER("laserfiche forms")" returns "Laserfiche Forms"
REPLACE REPLACE(old_string, start_position, count, new_string)

The REPLACE function returns a text value from modifying the first value at the location from the second value to the third value based on the fourth value.

"=REPLACE("Laserfiche", 6, 5, "fish")" returns "Laserfish"
RIGHT RIGHT(value, n)

The RIGHT function returns all the characters from the right up to a specific number of characters.

"=RIGHT("Laserfiche", 5)" returns "fiche"
ROUND ROUND(value, places)

The ROUND function returns the value rounded to the specified number of decimal places.

"=ROUND(543.21, 0)" returns "543"
ROUNDDOWN ROUNDDOWN(value, places)

The ROUNDDOWN function returns the value rounded down to the specified number of decimal places.

"=ROUNDDOWN(567.79, 0)" returns "567"
ROUNDUP ROUNDUP(value, places)

The ROUNDUP function returns the value rounded up to the specified number of decimal places.

"=ROUNDUP(543.21, 0)" returns "544"
ROW ROW()

The ROW function returns the row number of a cell in a table. The first cell has a value of 1.

"=ROW()" returns "1" for a cell in row 1

" =ROW()=0" returns "true" if the field is outside of the table/collection
SPLIT SPLIT(textValue, textSplit)

The SPLIT function returns an array of values, where the first value is split up based on the method for splitting.

"=SPLIT("Text.Split.Up", ".")" returns "Text,Split,Up"
SUB SUB(value1, value2)

The SUB function returns the difference between the first and second values.

"=SUB(3, 2)" returns "1"
SUBSTITUTE SUBSTITUTE(text, target text, replacement text, number)  

The SUBSTITUTE function replaces existing text values with updated text values. The "number" parameter is optional, and specifies the position of found instances to replace if there is more than one.

"=SUBSTITUTE("Employees: Susan Marks, Paul Smith, Eric Marks", "Marks", "Johnson", 1)" returns "Employees: Susan Johnson, Paul Smith, Eric Marks"

"=SUBSTITUTE("Employees: Susan Marks, Paul Smith, Eric Marks", "Marks", "Johnson", 2)" returns "Employees: Susan Marks, Paul Smith, Eric Johnson"
SUM SUM(value1, value2, ...)

The SUM function calculates the sum of all the number values in a list.

=SUM(1, 2, 3) returns 6

=SUM(VALUE(Single_Line_1), VALUE(Single_Line_2))

returns the sum of two single line fields.

=SUM(INDEX(Table_VariableName.Column_VarName1,ROW()),INDEX(Table_VariableName.Column_VarName2,ROW()))

returns the sum of the contents of the first two columns in three columns, when this formula is placed in the third column
SUMIFS SUMIFS(reference, range1, criterion1, range2, criterion2, ...)

=SUMIFS([1,1,3], [3,2,3], ">2", [4,5,6], "<6")

returns "1", because the first value in range 1 is "3" and the first value in range 2 is "4". "3" fits criterion 1 because 3 is greater than 2 and "4" fits criterion 2 because 4 is less than 6, so "1" (in the reference) is a hit. Similarly, the second "1" is a hit. However, "3" is not a hit because 6 is not less than 6 in the second range.
TEXT TEXT(number)  

The TEXT function converts a specified value to a string.

"=TEXT(ABS(3-5))" returns "2"
TRIM TRIM(text)  

The TRIM function removes spaces before and after a specified value.

" =TRIM(" Paul ")" returns "Paul"
TRUNC TRUNC(value1, value2)  

The TRUNC function shortens (or "truncates") the first value to the number of decimal places in the second value. If the second value is absent or set to zero, the function shortens to an integer.

"=TRUNC(33.333, 2)" returns "33.33"
UPPER UPPER(text)  

The UPPER function converts the specified text to all uppercase letters.

"=UPPER("ucsd")" returns "UCSD"
VALUE VALUE(input)

The VALUE function converts a specified value to a number.

"=VALUE("12:00:00")" returns "0.5"

" =VALUE("1")" returns "1"

"=VALUE("1/16/2017")" returns "42751"

WEEKDAY WEEKDAY(date, [type])

The WEEKDAY function returns the day of the week for the specified date as a number. You can type "1", "2", or "3" to determine whether the first day of the week is a Sunday or Monday.

"=WEEKDAY("4/15/2016", 3)" returns "4"

WEEKNUM WEEKNUM(date, mode)

The WEEKNUM function returns the number of the week in a year for a specified date object.

"=WEEKNUM(DATE(2017, 1, 8))" returns "2" because the date is in the first week of the year

"=WEEKNUM(DATE(2017, 1, 8), 2)" returns "1" because the "2" indicates the counting starts on a Monday.
WORKDAY WORKDAY(startDay, numDays)

The WORKDAY function returns a date object based on the number of days after a start day.

"=WORKDAY(DATE(2017, 2, 16), 1)" returns "42783"

"=DATE(2017,2,16)" returns "42782"

YEAR YEAR(dateValue)

The YEAR function returns the year of a given date object.

"=YEAR(DATE(2017,1,16))" returns "2017"