What formula fields do
A formula field calculates its value automatically. It can use other fields in the same item, fixed values that you enter, or another formula. The result is read-only and updates when the values it uses change.
Create a formula
- Open a list, open its list settings, then Fields and tap +.
- Name the field and select Formula as its type.
- Open Formula, choose a function and fill in its parameters using the choices offered.
- Choose number formatting if the formula returns a number, then save the field.
Compatible fields and nested formulas are offered according to the required input type. Formula results are calculated values, not values you type into each item.
Choose formula inputs
Each value a formula needs is called a parameter. When you open a parameter, you can normally choose from:
- Field: uses the value from another field in the current item.
- Value: uses a fixed number, text value or date that you enter.
- Formula: calculates the parameter with another formula.
For formulas with a variable number of inputs, such as Add or Concatenate, completing the current last input adds another input. Optional extra inputs can be removed by swiping them.
Column: Where available on a variable-input function, choose a field to use its populated values from active items in the current list. Numeric columns ignore values that cannot be parsed as numbers. This differs from Field, which reads only the current item. Column values are also available for text inputs where the picker offers them.
Maths formulas
Maths formulas return a number. Each number parameter can use a number field, a fixed number or a nested formula that returns a number.
Add
What it does: Adds two or more numbers together.
Parameters: Value 1, Value 2, followed by any additional values you want to add.
How it works: Listbase totals every supplied value. For example, 10 + 5 + 2 returns 17.
Subtract
What it does: Subtracts one number from another.
Parameters: Starting Value and Value to Subtract.
How it works: The second value is taken away from the first, so the order matters. For example, 10 minus 3 returns 7.
Multiply
What it does: Multiplies two or more numbers.
Parameters: Value 1, Value 2, followed by any additional values you want to multiply.
How it works: Listbase multiplies all supplied values together. For example, Quantity 4 multiplied by Unit Price 2.50 returns 10.
Divide
What it does: Divides one number by another.
Parameters: Dividend, the number to divide, and Divisor, the number to divide it by.
How it works: The first value is divided by the second. For example, 12 divided by 3 returns 4. Dividing by zero returns 0.
Minimum
What it does: Returns the smallest of two or more numbers.
Parameters: Value 1, Value 2, followed by any additional values you want to compare.
How it works: Listbase checks every supplied value and returns the lowest one.
Maximum
What it does: Returns the largest of two or more numbers.
Parameters: Value 1, Value 2, followed by any additional values you want to compare.
How it works: Listbase checks every supplied value and returns the highest one.
Average
What it does: Calculates the Mean, Median or Mode of one or more numbers.
Parameters: Type (Mean, Median or Mode) and Value 1, followed by any additional values you want to include. A value can also be a whole numeric column from the current list.
How it works: Mean adds the values and divides by their count. Median returns the middle value after sorting. Mode returns the most frequently occurring value. Choose Column for a value parameter to calculate across that numeric field for all active items in the current list; blank or invalid values are ignored.
Whole-column inputs
Where Column is offered, select a numeric field to expand all usable values from that field across active items in the current list. This is especially useful with Average, Add, Multiply, Minimum and Maximum.
Absolute
What it does: Returns a number without its negative sign.
Parameters: Number.
How it works: Positive values stay the same and negative values become positive. For example, -12 returns 12.
Modulo
What it does: Returns the remainder after division.
Parameters: Number and Divider.
How it works: Listbase divides the Number by the Divider and returns what is left over. For example, 10 divided by 3 leaves a remainder of 1. A Divider of zero returns 0.
Power
What it does: Raises a number to a chosen power.
Parameters: Number and Power.
How it works: The Number is multiplied by itself as directed by the Power. For example, 3 to the power of 2 returns 9.
Round
What it does: Rounds a number to a chosen interval, using the selected rounding direction.
Parameters: Number, Type (Default, Round down or Round up) and Precision.
How it works: Precision is the positive interval to round to. Default rounds to the nearest interval; Round down uses the lower interval and Round up the higher interval. A Precision of 1 uses whole numbers, 0.1 tenths and 5 multiples of five. Zero leaves the number unchanged; negative precision is treated as positive.
Average tie behaviour: If several values share the highest frequency, Mode returns the first of those values in input order. With no usable values, Average returns 0.
Text formulas
Text formulas either return text or return a number that describes text. Text inputs can come from most non-number and non-date fields, a fixed text value or a compatible nested formula.
Length
What it does: Counts the characters in a piece of text.
Parameters: Text.
How it works: Every character is counted, including spaces and punctuation. The result is a number.
Concatenate
What it does: Joins two or more pieces of text.
Parameters: optional Separator, then two or more Text values.
How it works: Values are joined in order using the Separator between them. Leave Separator empty to join without extra characters; enter a space or comma as the separator when needed.
Substring
What it does: Extracts part of a piece of text.
Parameters: Text, Start Position and optional Length.
How it works: Positions start at 1, so position 1 is the first character. Length controls how many characters are returned. If Length is omitted, Listbase returns everything from the start position to the end. Decimal positions and lengths are treated as whole numbers by removing the decimal part.
String Reverse
What it does: Reverses the order of the characters in text.
Parameters: Text.
How it works: The last character becomes the first, and so on. For example, ABC returns CBA.
Find
What it does: Finds the first occurrence of one piece of text inside another.
Parameters: Text to look in and Text to find.
How it works: It returns the position of the first match, starting at 1. The search is case-sensitive. If there is no match, or the search text is empty, it returns 0.
Replace
What it does: Replaces matching text with different text.
Parameters: Text, Target Text and Replacement Text.
How it works: Every exact, case-sensitive occurrence of Target Text is replaced. If Target Text is empty, the original text is returned unchanged.
Change Case
What it does: Changes text to upper or lower case.
Parameters: Text and Case.
How it works: Use Upper to produce capital letters. Other values produce lower-case text. For example, Upper changes “Listbase” to “LISTBASE”.
Date formulas
Date formulas work with calendar dates. They use the device's current calendar and ignore the time of day.
Difference Between Dates
What it does: Returns the number of whole days, months or years from one date to another.
Parameters: First Date, Second Date and Unit. Unit can be Days, Months or Years and defaults to Days.
How it works: Listbase counts from the First Date to the Second Date. The result is positive when the second date is later and negative when it is earlier. Months and years are counted as whole calendar units.
Date Add
What it does: Adds or subtracts days, months or years from a date.
Parameters: Date, Amount and Period. Period can be Days, Months or Years.
How it works: A positive Amount moves forward and a negative Amount moves back. Only the whole-number part of Amount is used. Calendar rules are applied when month lengths and leap years differ.
Date Index
What it does: Gets the day, month or year from a date.
Parameters: Date, Component and Return As. Component can be Day, Month or Year. Return As can be Name or Index.
How it works: Day as Name returns the weekday name, while Day as Index returns the device calendar's weekday number. Month as Name returns the month name, while Month as Index returns 1 to 12. Year returns the calendar year as either a number or text.
Logic formulas
Logic formulas choose values, find information or calculate across items in the current list.
Condition
What it does: Returns one value when a test is true and another value when it is false.
Parameters: Value to Check, Comparison, Comparison Value, Output if True and optional Output if False.
How it works: First choose a number, text or date to check. Listbase then offers comparisons that suit that type. The true and false outputs must return the same type. If Output if False is left empty, Listbase returns 0 for a number result or a blank value for text and dates.
- Number comparisons: equal to, not equal to, greater than, greater than or equal to, less than, and less than or equal to.
- Text comparisons: equal to, not equal to, contains, does not contain, begins with, and ends with. Text comparisons ignore capitalisation. Begins with and Ends with also ignore accents.
- Date comparisons: equal to, not equal to, before, after, on or before, and on or after. Dates are compared by calendar day.
Lookup
What it does: Finds the first matching active item in a list and returns a field from it.
Parameters: Match value, Look in, Match against and Return value.
How it works: Match value comes from the current item, a fixed value or a nested formula. Look in selects the list to search. Match against selects a compatible field in that list. Return value selects the field to bring back from the first match.
Number matches compare numeric values. Date matches compare the calendar day. Text matches ignore capitalisation, accents and spaces at the beginning or end. If the match value is blank, or no item matches, Lookup returns 0 for a number result or a blank value for text and dates.
Sumif
What it does: Adds a number field across items in the current list that meet a condition.
Parameters: Sum Field, Condition Field, Rule and, when the rule needs one, Target Value.
How it works: Listbase checks every active item in the current list. For each item that matches the rule, it adds the value from Sum Field. Blank or invalid values in the Sum Field count as zero. Target Value can be a field, a fixed value or a nested formula.
Countif
What it does: Counts populated field values across items in the current list that meet a condition.
Parameters: Count Field, Condition Field, Rule and, when the rule needs one, Target Value.
How it works: Listbase first finds all active items that match the rule. It then counts only those items where Count Field contains a usable value. Blank text, invalid dates and empty linked or multiple-entry fields are not counted.
assets/images/screens/condition-formula-editor.png. Replace this placeholder with an image and descriptive alt text.
Sumif and Countif: Both use a field to test, a rule and (where required) a target, and evaluate active items in the current list. Sumif totals a selected numeric Sum Field. Countif counts matching items whose selected Count Field has a stored value; it is not simply a count of every matching row.
Nested formulas
A nested formula uses one formula as a parameter inside another. This lets you calculate a result in stages without creating several separate fields.
For example, you can use Multiply inside Round to calculate Quantity × Unit Price and then round the result. Listbase only offers nested formulas that return the type required by the parameter.
Listbase also prevents references that would create a circular loop, such as Formula A using Formula B while Formula B uses Formula A.
Missing inputs and results: An empty or invalid input may be treated as 0 or blank depending on its expected type. Divide and Modulo return 0 when dividing by zero; Find returns 0 when text is not found. For Lookup, an empty match or no matching item returns 0 for a numeric result or blank for text and dates.