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:
- Create a many-to-many relation to itself with a matching condition to filter previous records.
- 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])