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.

New Line for Text

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

• "aaa\nbbb\nccc"
• List("\n", "aaa", "bbb", "ccc")
• "aaa" & "\n" & "bbb" & "\n" & "ccc"
• Concat("aaa", "\n", "bbb", "\n", "ccc")

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:

• To extract a substring before the first occurrence of a delimiter string, use the Left function. For example, Left("aaa.vs.bbb.vs.ccc", ".vs.") returns "aaa".
• To extract a substring after the first occurrence of a delimiter string, use the NotLeft function. For example, NotLeft("aaa.vs.bbb.vs.ccc", ".vs.") returns "bbb.vs.ccc".
• To extract a substring after the last occurrence of a delimiter string, use the Right function. For example, Right("aaa.vs.bbb.vs.ccc", ".vs.") returns "ccc".
• To extract a substring before the last occurrence of a delimiter string, use the NotRight function. For example, NotRight("aaa.vs.bbb.vs.ccc", ".vs.") returns "aaa.vs.bbb".
• To extract a substring from text if you know the particular position and length, use the Mid function. For example, Mid("type20A", 5, 2) returns the text value "20".
• To extract a substring from text with parts separated by a delimiter, use the Part function. For example, Part("aaa|bbb|ccc", 2, "|") returns the text value "bbb".

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])