Tips and Tricks

Duration in Years

There is no way to represent a duration in years due to a different number of days in a year. You should adjust the formula expression to use the AdjustYear function.

Duration in Months

There is no way to represent a duration in months due to a different number of days in a month. You should adjust the formula expression to use the AdjustMonth function.

Duration in Days

To represent duration in days, you can use a duration literal - a number followed by a single character representing the unit of measurement. Use d for days. For example, 5d represents 5 days. Additionally, you can use the Days function to construct a duration in days. For instance, Days([Numeric Column]) converts the numeric value from "Numeric Column" into a duration in days.

To adjust the date or timestamp value, you can simply use the arithmetical operator + or operator - between the date/timestamp and the duration.

For example:

New Line for Text

To insert a new line into text, please use the following string "\n". For example, all of the following formulas:

will produce the same result:

aaa
bbb
ccc

Extracting Substring from Text

There is no function which finds the position of one string in another, so you should use the following functions instead:

Extracting Column Value from Previous Record

To extract a column value from the previous record, follow these steps:

  1. Create a many-to-many relation to itself with a matching condition to filter previous records.
  2. Use the newly created relation to create a summary column with the "Index" aggregate function and corresponding sorting to extract the value from the most recent record.

Extracting Column Value from a Related Record

If you have a reference column in a table and wish to extract an additional column value from the related table, you need to create a lookup column using the corresponding relation. After that, you’ll be able to use the newly created column in the formula.

Summarize Column Values over Related Records

If you wish to summarize a column value from the related table, you need to create a summary column using the corresponding relation. After that, you’ll be able to use the newly created column in the formula.

Formula URL Parameter Encoding

Ensure that all calculated parameters for the Formula - URL column are encoded using the URLEncode function.

For example:

"https://www.google.com/search?q=" & URLEncode([Text Column])

Min, Max, Sum, or Count Functions and Several Records

If you’ve seen the formula where the Min, Max, Sum, or Count function is used with a single argument, it’s most likely that the user is trying to calculate the corresponding value for all records in the table.

By design, all these functions expect at least two arguments and calculate their value using these arguments within a single record.

To calculate the Min, Max, Sum, or Count value over all records in the table, follow these steps:

  1. Create a many-to-many relation with itself.
  2. Use the newly created relation to create a summary column with the corresponding aggregate function to calculate the value.

After that, you’ll be able to use the newly created summary column in the formula.

How to Create a Unique Random Identifier with Numbers and Letters

Please consider using the Guid function for that. It returns a unique string with 32 characters.

For example:

Your provided text is already quite well-written, but there are a few minor grammar and spelling adjustments to make it even clearer:

Filter Records by Date and Timestamp Columns

To check if a date or timestamp falls within a specific date range, please use the following or similar formulas:

You can vary the comparison operator and use the Between function and logical operators to create complex conditions.

How to Construct a Time Value

To construct a time value, you have the following options:

How to Construct a Date Value

To construct a date value, you have the following options: