Column Types

Column Types

The Datalexing system is a powerful system for managing and organizing data, relying on organizing data into tables containing rows and columns. The column is considered an essential part of this structure, as it is used to store a set of values of the same data type. Columns can be configured in multiple ways to suit your project's needs. Each table in the applications consists of many table cells, which are parts of the information. These table cells are arranged in columns and rows. Rows represent records. Here, information about a specific entity (such as a customer, order, etc.) is retained. Columns represent fields. The field corresponds to specific data such as ID, color, postal code. What is a Column? The table consists of rows and columns, which are displayed in table view. In the table, the column is a set of values of the same data type; each column is a data structure holding a specific data type. Columns are used to maintain table relationships. You can take a column from one table and merge it into any other table. This is because the table is not just a means of storing your data; it is an object that can contain other objects, each of which can collect data. In Datalexing, each column can be configured to suit your needs. You can sort, filter, and hide columns in a specific view without affecting other views in the table. Column Types A column can store many different types of data, but the important thing is that it can store only one type of information, not a mixture. Columns feature different data types, such as text, numbers, dates, binary values (true/false), participants, choice, link, email, file, and more: | Column Type | Description | | ✏️ Single Line Text | The single-line text field allows entry of short, unique pieces of text into the table. | | 🗒️ Long Text | The long text field can contain long paragraphs or multiple lines of text. | | 🔌 Link to Table | The linked table field creates a link between two existing tables by linking data across associated rows. | | #️⃣ Number | The number field is a type of field that contains numeric values. | | ⭐ Rating | The rating field is used to evaluate your rows for classification or quality assessment. | | ✅ Boolean | The boolean value represents information in true/false format. | | 📆 Date | The date field allows entry or update of a date and time in a cell. | | ✍🏻 Last Modified | The "Last Modified" field type returns the date and time of the last modification for the row. | | 💾 Created At | The "Created At" field type automatically displays the date and time a row was created by the user. | | 🔗 Link | The link field holds a single link. | | ✉️ Email | The email field type allows entry of a single email address in a cell in the correct format. | | 📁 File | The file field allows you to easily upload one or more files from your device or from a URL. | | 1️⃣ Single Select | The single-Select field type has specific options to choose only one option from a set of options. | | 🔢 Multiple Select | The multiple-select field contains a list of choices to choose multiple options from a set of options. | | ☎️ Phone Number | The phone number field formats a string of numbers as a phone number, in the form (XXX) XXX-XXXX. | | ➗ Formula | A value can be calculated in each row using a formula based on values in cells in the same row. | | 👥 Collaborator | Assign participants by selecting names from a list of workspace members. | | 🧮 Count | The count field automatically counts the number of rows associated | | 🔦 Search | You can search for a specific field in an associated row using the search field. | | 📦 Collect | Aggregate data and gain valuable insights from linked tables. | | ✨ Artificial Intelligence | With the help of artificial intelligence, you can generate new values based on values from other columns. Such as translation, language and spelling correction, paraphrasing, text shortening, text lengthening, summarization, sentiment analysis. | To learn more about Column Settings Click here Footer Aggregation Aggregations in the footer play a crucial role in calculating values derived from a specific column within a table. You can calculate the value of the aggregation in the footer by looking at all the rows in the tables. These resulting values are displayed in footer cells for easy reference. This allows for quick assessment and summarization of column values across a range of rows, except for the search column. Please note that these aggregated values cannot be referenced. The aggregation in the footer provides a summary of the column by default, with the footer summary for each column being empty. To get a specific summary for a particular column, follow these steps: 1. In any table, click on the footer bar at the bottom of the table to select the aggregation function. 2. Select from the various available functions for the selected column. Once an aggregation is created, the results will be displayed for each column in the footer area. General Functions The column type will determine the types of footer summaries available. Some summary functions will appear in columns like Creation At, Single Select, Single Line, Phone Number, Email, File, Multiple Select, Formula, Linked Table, Long Text, URL, Last Modified, and which are: - Empty - Filled - Percentage of Empty - Percentage of Filled - Unique (Except for Linked Table, File, Created At, and Multiple Choice columns) Date Column Functions In addition to general functions, some functions will only appear in Date, Last Modified, and Creation At columns: Oldest Date Newest Date Logical Data Columns Functions The aggregation function for Logical Data columns allows you to see the count or percentage of selected and unselected cells. - Selected - Unselected - Percentage of Selected - Percentage of Unselected Numeric and Rating Columns Functions In addition to general functions, you can use the footer aggregation for the Number column to calculate the sum, minimum, or maximum of numeric data in columns. For numeric and rating columns: - Minimum - Maximum - Sum - Average - Median - Standard Deviation - Variance

Formulas

Formulas Datalexing formula fields let you calculate a value automatically for every cell in a field, based on a formula you define. The syntax works much like the formulas you already know from spreadsheet tools — combined with a fast, growing function library built for tables. A formula is built from two building blocks: - Operators — symbols (+, =, >, etc.) that act directly between two values. - Functions — named, predefined calculations (e.g. SUM, IF, LEFT) that take arguments, do something with them, and return a result. On this page - Operators - Function categories at a glance - 📝 Text functions - 🔢 Number functions - ✅ Logical (True/False) functions - 📃 List & Aggregation functions - 🔁 General & Reference functions - 🗓️ Date & Time functions - 🔗 Link functions - Editor's notes — what changed Operators Operators are symbols that perform arithmetic, comparisons, or text joins directly between two values, without needing to write a function name. Most operators have a matching function that does the exact same thing (listed below) — use whichever reads better in your formula. | Operator | Function equivalent | Description | Example | | -------- | ----------------------- | ------------------------------------------------------------------------- | -------------------- | | + | ADD | Adds two numbers and returns the sum. | 1 + 2 = 3 | | - | MINUS | Subtracts the second number from the first. | 10 - 2 = 8 | | * | MULTIPLY | Multiplies two numbers together. | 10 * 3 = 30 | | / | DIVIDE | Divides the first number by the second. | 10 / 5 = 2 | | = | EQUAL | Returns TRUE if the two values are equal, FALSE otherwise. | 10 = 10 → TRUE | | != | NOT_EQUAL | Returns TRUE if the two values are not equal, FALSE otherwise. | 10 != 10 → FALSE | | > | GREATER_THAN | Returns TRUE if the first value is greater than the second. | 10 > 11 → FALSE | | >= | GREATER_THAN_OR_EQUAL | Returns TRUE if the first value is greater than or equal to the second. | 10 >= 10 → TRUE | | < | LESS_THAN | Returns TRUE if the first value is less than the second. | 10 < 11 → TRUE | | <= | LESS_THAN_OR_EQUAL | Returns TRUE if the first value is less than or equal to the second. | 19 <= 1 → FALSE | Function categories at a glance Every function in Datalexing belongs to one of the categories below, shown by an icon. Here's what each one actually covers: | Category | Covers | | -------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Text | Reading, cleaning, and transforming text strings. | | Number | Arithmetic, rounding, and mathematical calculations. | | Logical | Comparisons and conditions that return TRUE or FALSE. | | List & Aggregation | Summarizing values pulled in through a link/lookup field (sums, averages, counts, filters...). | | General & Reference | Functions that pull in or combine data across any type — a field's raw value, a linked table's value, a row's ID — rather than being limited to text or numbers. | | Date & Time | Creating, formatting, and measuring dates and timestamps. | | Link | Building and reading clickable buttons and hyperlinks. | *Related: Column Types · Formulas Guide · *Score Cards

General & Reference Functions

🔁 General & Reference Functions Functions that reference a piece of data — a field, a linked row, the current row itself — or combine values without being limited to one data type. | Function | Description | Syntax | Parameters | Example | | ------------ | -------------------------------------------------------------------------------------------------------- | ----------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------- | | FIELD | Returns the value of another field in the same row, by name. | FIELD(Field Name) | Field Name — the name of the field to retrieve. | FIELD('Priority') = 'High' | | LOOKUP | Returns the value(s) of a field in a linked table, for the rows connected through a link field. | LOOKUP(Link Field Name, Field Name in Linked Table) | Link Field Name — the link field connecting to the other table.Field Name in Linked Table — the field to retrieve from the linked table. | LOOKUP('Assigned To', 'Full Name') = 'Fahad Al-Otaibi' | | ROW_ID | Returns the current row's unique identifying number. Takes no arguments. | ROW_ID() | None. | CONCAT('Row ', ROW_ID()) = 'Row 1' | | CONCAT | Joins any number of values — text, numbers, or the result of other formulas — into a single text string. | CONCAT(Value, Value, ...) | Value — any number of text, numbers, or formula results to join. | CONCAT(ROW_NUMBER, '- ', FIELD('Name')) = '1- Fahad' | | WHEN_EMPTY | Returns a fallback value when the first argument is empty; otherwise returns the first argument. | WHEN_EMPTY(Value, Default Value) | Value — the value to check.Default Value — the value used if the first is empty. | WHEN_EMPTY(FIELD('Nickname'), 'N/A') | | MINUS | Subtracts the second number from the first. Same as the - operator. | MINUS(Number, Number) | Number 1 — the number to subtract from.Number 2 — the number to subtract. | MINUS(3, 1) = 2 |

Text Functions

📝 Text Functions Functions for reading, cleaning, searching, and transforming text strings. | Function | Description | Syntax | Parameters | Example | | --------------- | ----------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------ | | LEFT | Extracts a given number of characters starting from the left of a text string. | LEFT(Text, Number) | Text — the source text.Number — how many characters to extract from the left. | LEFT('abcde', 2) = 'ab' | | RIGHT | Extracts a given number of characters starting from the right of a text string. | RIGHT(Text, Number) | Text — the source text.Number — how many characters to extract from the right. | RIGHT('abcde', 2) = 'de' | | LEN | Returns the number of characters in a text string. | LEN(Text) | Text — the text to measure. | LEN('abc') = 3 | | LOWER | Converts a text string to lowercase. | LOWER(Text) | Text — the text to convert. | LOWER('ArrAnge') = 'arrange' | | UPPER | Converts a text string to uppercase. | UPPER(Text) | Text — the text to convert. | UPPER('aPpLe') = 'APPLE' | | TRIM | Removes leading and trailing whitespace from a text string. | TRIM(Text) | Text — the text to clean up. | TRIM(' abc ') = 'abc' | | REVERSE | Reverses the order of characters in a text string. | REVERSE(Text) | Text — the text to reverse. | REVERSE('abc') = 'cba' | | SEARCH | Returns the position of the first character where a search text is found within a source text; returns 0 if it isn't found. | SEARCH(Text, Search Text) | Text — the text to search within.Search Text — the text to look for. | SEARCH('a b c test', 'test') = 7SEARCH('none', 'test') = 0 | | REPLACE | Finds a piece of text within a string and replaces every occurrence with another piece of text. | REPLACE(Source Text, Search Text, Replacement Text) | Source Text — the original text.Search Text — the text to find.Replacement Text — the text to replace it with. | REPLACE('test a b c test', 'test', '1') = '1 a b c 1' | | REGEX_REPLACE | Replaces every match of a regular expression pattern within a text string with a replacement string. | REGEX_REPLACE(Text, Pattern, Replacement) | Text — the original text.Pattern — the regular expression to match.Replacement — the text to insert in place of each match. | REGEX_REPLACE('abc', 'a', '1') = '1bc' | | T | Returns the value if it's text; returns blank if it isn't. | T(Value) | Value — the value to check. | T(10) = (blank)T('Hello') = 'Hello' | | TOTEXT | Converts a value (number, date, boolean, etc.) into text. | TOTEXT(Value) | Value — the value to convert. | TOTEXT(10) = '10' |

Number Functions

🔢 Number Functions Arithmetic and mathematical calculations. | Function | Description | Syntax | Parameters | Example | | ---------- | -------------------------------------------------------------------------------------------------------- | ---------------------------- | ----------------------------------------------------------------------------------------- | ------------------------------------------------ | | ADD | Adds two numbers together. Same as the + operator. | ADD(Number, Number) | Number 1 — the first number.Number 2 — the second number. | ADD(1, 2) = 3 | | MULTIPLY | Multiplies two numbers together. Same as the * operator. | MULTIPLY(Number, Number) | Number 1 — the first number.Number 2 — the second number. | MULTIPLY(2, 5) = 10 | | DIVIDE | Divides the first number by the second. Same as the / operator. | DIVIDE(Dividend, Divisor) | Dividend — the number to divide.Divisor — the number to divide by. | DIVIDE(10, 2) = 5 | | ROUND | Rounds a number to a given number of decimal places. | ROUND(Number, Digits) | Number — the value to round.Digits — how many decimal places to round to. | ROUND(1.12345, 2) = 1.12 | | TRUNC | Truncates a number to its integer part, discarding any decimals (no rounding). | TRUNC(Number) | Number — the value to truncate. | TRUNC(1.49) = 1 | | FLOOR | Rounds a number down to the nearest integer. | FLOOR(Number) | Number — the value to round down. | FLOOR(1.51) = 1FLOOR(-1.49) = -2 | | CEIL | Rounds a number up to the nearest integer. | CEIL(Number) | Number — the value to round up. | CEIL(1.49) = 2CEIL(-1.51) = -1 | | ABS | Returns the absolute (non-negative) value of a number. | ABS(Number) | Number — the value to convert. | ABS(-1.51) = 1.51 | | SIGN | Returns 1 for a positive number, -1 for a negative number, 0 for zero. | SIGN(Number) | Number — the value to evaluate. | SIGN(-9.0) = -1 | | GREATEST | Compares two numbers and returns the larger one. | GREATEST(Number, Number) | Number 1, Number 2 — the values to compare. | GREATEST(1, 2) = 2 | | LEAST | Compares two numbers and returns the smaller one. | LEAST(Number, Number) | Number 1, Number 2 — the values to compare. | LEAST(1, 2) = 1 | | MOD | Returns the remainder after dividing the first number by the second. | MOD(Number, Number) | Number 1 — the dividend.Number 2 — the divisor. | MOD(5, 2) = 1MOD(-3, 2) = -1 | | POWER | Raises a number to the power of an exponent. | POWER(Number, Exponent) | Number — the base.Exponent — the power to raise it to. | POWER(3, 2) = 9POWER(25, 0.5) = 5.0 | | SQRT | Returns the square root of a number. Returns NaN for negative or invalid input. | SQRT(Number) | Number — the value to find the square root of. | SQRT(9) = 3SQRT(-4) = NaN | | EXP | Returns Euler's number (e ≈ 2.718) raised to the power of the given number. | EXP(Number) | Number — the exponent to raise e to. | EXP(1) = 2.718EXP(0) = 1 | | LOG | Returns the exponent to which a base must be raised to produce a given number. | LOG(Base, Number) | Base — the base of the logarithm.Number — the value to find the exponent for. | LOG(3, 9) = 2 | | EVEN | Returns TRUE if a number is even, FALSE otherwise (including for invalid input). | EVEN(Number) | Number — the value to check. | EVEN(2) = trueEVEN(5) = false | | TONUMBER | Converts a text value into a number, if possible. | TONUMBER(Text) | Text — the text to convert. | TONUMBER('10') = 10 | | IS_NAN | Returns TRUE if a value is NaN (Not a Number), FALSE otherwise. | IS_NAN(Number) | Number — the value to check. | IS_NAN(1 / 0) = true | | WHEN_NAN | Returns a fallback value if the first argument evaluates to NaN; otherwise returns the first argument. | WHEN_NAN(Number, Fallback) | Number — the value to check.Fallback — the value to use if it's NaN. | WHEN_NAN(1 / 0, 4) = 4WHEN_NAN(1, 4) = 1 |

Logical (True/False) Functions

✅ Logical (True/False) Functions Comparisons and conditions that evaluate to TRUE or FALSE. | Function | Description | Syntax | Parameters | Example | | ----------------------- | --------------------------------------------------------------------------------------------------------- | ------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------- | | IF | Evaluates a condition and returns one value if it's true, another if it's false. | IF(Condition, True Result, False Result) | Condition — the test to evaluate.True Result — returned if the condition is true.False Result — returned if the condition is false. | IF(FIELD('Status') = 'On', 'It is on', 'It is off') | | EQUAL | Returns TRUE if two values are equal, FALSE otherwise. Same as =. | EQUAL(Value 1, Value 2) | Value 1, Value 2 — the values to compare. | EQUAL('a', 'a') = true | | NOT_EQUAL | Returns TRUE if two values are not equal, FALSE otherwise. Same as !=. | NOT_EQUAL(Value 1, Value 2) | Value 1, Value 2 — the values to compare. | NOT_EQUAL(1, 2) = true | | GREATER_THAN | Returns TRUE if the first value is greater than the second. Same as >. | GREATER_THAN(Value 1, Value 2) | Value 1, Value 2 — the values to compare. | GREATER_THAN(1, 2) = false | | GREATER_THAN_OR_EQUAL | Returns TRUE if the first value is greater than or equal to the second. Same as >=. | GREATER_THAN_OR_EQUAL(Value 1, Value 2) | Value 1, Value 2 — the values to compare. | GREATER_THAN_OR_EQUAL(1, 1) = true | | LESS_THAN | Returns TRUE if the first value is less than the second. Same as <. | LESS_THAN(Value 1, Value 2) | Value 1, Value 2 — the values to compare. | LESS_THAN(2, 1) = false | | LESS_THAN_OR_EQUAL | Returns TRUE if the first value is less than or equal to the second. Same as <=. | LESS_THAN_OR_EQUAL(Value 1, Value 2) | Value 1, Value 2 — the values to compare. | LESS_THAN_OR_EQUAL(1, 3) = true | | AND | Returns TRUE only if all conditions are true. | AND(Condition 1, Condition 2) | Condition 1, Condition 2 — the conditions to check. | AND(true, false) = false | | OR | Returns TRUE if at least one condition is true. | OR(Condition 1, Condition 2) | Condition 1, Condition 2 — the conditions to check. | OR(true, false) = true | | NOT | Reverses a boolean value. | NOT(Condition) | Condition — the value to reverse. | NOT(true) = false | | CONTAINS | Returns TRUE if a search text is found anywhere within a source text. | CONTAINS(Source Text, Search Text) | Source Text — the text to search within.Search Text — the text to look for. | CONTAINS('test', 'e') = true | | ISBLANC | Returns TRUE if a value is empty or blank, FALSE otherwise. | ISBLANC(Value) | Value — the value to check. | ISBLANC(FIELD('Notes')) = true | | IS_NULL | Returns TRUE if a numeric value is null, FALSE otherwise. Currently supported for number fields only. | IS_NULL(Value) | Value — the value to check. | IS_NULL(FIELD('Score')) = false |

Link Functions

🔗 Link Functions Build and read clickable buttons and hyperlinks. | Function | Description | Syntax | Parameters | Example | | ---------------- | ------------------------------------------------------------------------------------- | -------------------------------------- | -------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------- | | LINK | Creates a hyperlink to the given URL. | LINK(URL) | URL — the web address the link points to. | LINK('``[https://your-text-here.com](https://your-text-here.com)``') | | BUTTON | Creates a clickable button with a label, linking to the given URL. | BUTTON(URL, Label) | URL — the web address the button points to.Label — the text shown on the button. | BUTTON('``[https://your-text-here.com](https://your-text-here.com)``', 'Open') | | GET_LINK_URL | Returns the URL from a field created with LINK or BUTTON. | GET_LINK_URL(Link or Button Field) | Link or Button Field — the field to read the URL from. | GET_LINK_URL(FIELD('Website')) = '``[https://your-text-here.com](https://your-text-here.com)``' | | GET_LINK_LABEL | Returns the label text from a field created with BUTTON (or LINK, if it has one). | GET_LINK_LABEL(Link or Button Field) | Link or Button Field — the field to read the label from. | GET_LINK_LABEL(FIELD('Website')) = 'Open' |

Date & Time Functions

🗓️ Date & Time Functions Create, format, and measure dates and timestamps. | Function | Description | Syntax | Parameters | Example | | -------------------- | ---------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------ | | TODATE | Converts text into a date, using the given date format. | TODATE(Text, Date Format) | Text — the text to convert.Date Format — how to interpret the text as a date. | TODATE('20210101', 'YYYYMMDD') | | TODATE_TZ | Converts text into a date, using the given date format and time zone. | TODATE_TZ(Text, Date Format, Timezone) | Text — the text to convert.Date Format — how to interpret the text.Timezone — the time zone to apply. | TODATE_TZ('20210101', 'YYYYMMDD', 'Europe/Amsterdam') | | TODAY | Returns the current date, in UTC. Takes no arguments. | TODAY() | None. | TODAY() > TODATE('2021-12-12', 'YYYY-MM-DD') = true | | NOW | Returns the current date and time, in UTC. Takes no arguments. | NOW() | None. | NOW() > TODATE('2021-12-12 13:00:00', 'YYYY-MM-DD HH24:MI:SS') | | DATETIME_FORMAT | Converts a date into text, formatted according to the given pattern. | DATETIME_FORMAT(Date, Format) | Date — the date to convert.Format — the text pattern to apply. | DATETIME_FORMAT(TODATE('2024-01-01', 'YYYY-MM-DD'), 'YYYY') = '2024' | | DATETIME_FORMAT_TZ | Converts a date into text, formatted according to the given pattern and time zone. | DATETIME_FORMAT_TZ(Date, Format, Timezone) | Date — the date to convert.Format — the text pattern to apply.Timezone — the time zone to apply. | DATETIME_FORMAT_TZ(FIELD('Due Date'), 'YYYY-MM-DD HH24:MI', 'Europe/Amsterdam') | | YEAR | Returns the year from a date. | YEAR(Date) | Date — the date to extract from. | YEAR('2024-01-01') = 2024 | | MONTH | Returns the month (1–12) from a date. | MONTH(Date) | Date — the date to extract from. | MONTH(TODATE('2021-12-12', 'YYYY-MM-DD')) = 12 | | DAY | Returns the day of the month (1–31) from a date. | DAY(Date) | Date — the date to extract from. | DAY(TODATE('20210101', 'YYYYMMDD')) = 1 | | SECONED³ | Returns the seconds component of a date/time value. | SECONED(Date) | Date — the date/time to extract from. | SECONED(TODATE('2024-01-01 08:34:02', 'YYYY-MM-DD HH24:MI:SS')) = 2 | | DATE_DIFF | Calculates the difference between two dates in a given unit (year, month, week, day, hour, minute, or second). | DATE_DIFF(Unit, Date 1, Date 2) | Unit — the unit of time to measure in.Date 1 — the starting date.Date 2 — the ending date. | DATE_DIFF('year', TODATE('2000-01-01', 'YYYY-MM-DD'), TODATE('2020-01-01', 'YYYY-MM-DD')) = 20 | | DATE_INTERVAL | Returns a date interval (e.g. "1 year", "3 months") for use in date arithmetic. | DATE_INTERVAL(Text) | Text — the interval to create. | DATE_INTERVAL('1 year') |

List & Aggregation Functions

📃 List & Aggregation Functions Summarize the values pulled in through a link or lookup field — sums, averages, counts, filtering, and more. | Function | Description | Syntax | Parameters | Example | | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------ | --------------------------- | -------------------------------------------------------------------------------------- | -------------------------------------------------------------------------- | | COUNT | Returns the number of items in a link/lookup field. | COUNT(Field) | Field — the link row field to count. | COUNT(FIELD('Related Orders')) | | SUM | Returns the sum of all values in a lookup. | SUM(Lookup) | Lookup — the looked-up values to sum. | SUM(LOOKUP('Orders', 'Amount')) | | AVERAGE | Returns the average (mean) of all values in a lookup. | AVERAGE(Lookup) | Lookup — the looked-up values to average. | AVERAGE(LOOKUP('Orders', 'Amount')) | | MIN | Returns the smallest value from a lookup. | MIN(Lookup) | Lookup — the looked-up values to compare. | MIN(LOOKUP('Orders', 'Amount')) | | MAX | Returns the largest value from a lookup. | MAX(Lookup) | Lookup — the looked-up values to compare. | MAX(LOOKUP('Orders', 'Amount')) | | STDDEV_POP | Returns the population standard deviation of the values in a lookup — use when the lookup contains an entire population, not a sample. | STDDEV_POP(Lookup) | Lookup — the looked-up values. | STDDEV_POP(LOOKUP('Orders', 'Amount')) | | STDDEV_SAMPLE | Returns the sample standard deviation of the values in a lookup — use when the lookup is only a sample of a larger population. | STDDEV_SAMPLE(Lookup) | Lookup — the looked-up values. | STDDEV_SAMPLE(LOOKUP('Orders', 'Amount')) | | VARIANCE_POP | Returns the population variance of the values in a lookup — use when the lookup contains an entire population, not a sample. | VARIANCE_POP(Lookup) | Lookup — the looked-up values. | VARIANCE_POP(LOOKUP('Orders', 'Amount')) | | VARIANCE_SAMPLE | Returns the sample variance of the values in a lookup — use when the lookup is only a sample of a larger population. | VARIANCE_SAMPLE(Lookup) | Lookup — the looked-up values. | VARIANCE_SAMPLE(LOOKUP('Orders', 'Amount')) | | ANY | Returns TRUE if at least one looked-up value meets a condition. | ANY(Lookup Condition) | Lookup Condition — a condition built on a lookup's values. | ANY(LOOKUP('Orders', 'Status') = 'Late') | | EVERY | Returns TRUE only if every looked-up value meets a condition. | EVERY(Lookup Condition) | Lookup Condition — a condition built on a lookup's values. | EVERY(LOOKUP('Orders', 'Status') = 'Paid') | | FILTER | Narrows a lookup down to only the values that meet a given condition — typically wrapped in another aggregation. | FILTER(Lookup, Condition) | Lookup — the values to filter.Condition — the rule to filter by. | SUM(FILTER(LOOKUP('Orders', 'Amount'), LOOKUP('Orders', 'Amount') > 10)) | | JOIN | Combines all values from a lookup into a single text string, separated by the given separator. | JOIN(Lookup, Separator) | Lookup — the values to combine.Separator — the text placed between each value. | JOIN(LOOKUP('Orders', 'ID'), ', ') = '101, 102, 103' |

Formulas Guide

A Simple Formula Example Consider a table with a two standard text field named "First Name" , "Last Name" Each Column Contains Names. you can create a formula field with the formula concat("Mr.",field("First Name"),field("Last Name")), the resulting table would appear as follows: | First Name | Last Name | Full Name | | ---------- | --------- | ------------------ | | First Name | Last Name | Full Name | | Ahmed | Abdullah | Mr. Ahmed Abdullah | | Khalid | Faisal | Mr. Khalid Faisal | In this scenario, the formula field cells are calculated using the text field cell for each row. Explaining a simple formula Let's dissect the formula concat("Mr.",field("First Name"),field("Last Name")) to understand its components: concat: Concat is one of many formula functions available, which joins together all the inputs into one piece of text. (: An opening parenthesis indicates the beginning of input for a formula function. 'Mr.': This is the first input to concat, simply the text "Mr," enclosed in quotes. ,: As multiple inputs are provided to concat, each input is separated by a comma. field('First Name'): This is the second input to concat, referencing the field in the same table named "First Name" For each cell in the formula field, this reference will be substituted with the value in the "First Name" for that row. ,: Again as multiple inputs are provided to concat, each input is separated by a comma. field('Second Name'): This is the third and final input to concat, referencing the field in the same table named "Second Name" For each cell in the formula field, this reference will be substituted with the value in the "Second Name" for that row. ): A closing parenthesis indicates the end of the inputs to the concat function. What is a function? A function in a formula takes a specific number of inputs, depending on its type, performs calculations using those inputs, and produces an output. Functions sometimes accept only certain types of inputs. For example, the datetime_format function only accepts two inputs: the first must be a date, and the second must be text. All available functions are displayed in the expanded formula edit box, accessible when editing a formula field. Using numbers in formulas Formulas can perform numerical calculations using standard math operators such as +, -, *, and /. You can use whole numbers or decimal numbers directly in your formula, for example: (field('number field') + 10.005)/10. Invalid Number Error If you encounter an "Invalid Number" error in a formula cell, it means that the formula for that row attempted one of the following invalid operations: - Dividing a number by zero. - Trying to convert text to a number using the tonumber function and failing because the text wasn't a valid number. - Calculating a number larger than 10^50, the maximum allowed value. Conditional calculations If conditional calculations are necessary, you can use the if function and comparison operators. For instance, the formula IF(day(field('some date')) = 1, true, false) calculates whether a date field represents the first day of a month. You can compare fields and sub-formulas using operators such as >, >=, <=, <, =, and !=. Using Dates To create a constant date within a formula, utilize the todate function, like so: todate('2020-01-01 10:20:30', 'YYYY-MM-DD HH:MI:SS'). The first argument is the desired date in text form, and the second is the format of the date text. Using Date Intervals Subtracting two dates yields the time difference between them: field('date a') - field('date b'). The date_interval function allows you to create intervals within the formula for manipulation. For calculating a new date based on a date/time interval, employ the date_interval function like this: field('my date column') - date_interval('1 year').