Today we've added couple of extensions to formula functions.

URLs

  1. URLEncode(text) is now aware of international characters and escapes them using UTF-8 most sites expect in the URL.
  2. URLDecode(text) function performs decoding of URL parameters containing UTF-8 characters. This function is a counterpart of URLEncode()
  3. URLParam(url, param) function extracts and decodes the value of the parameter specified by name from URL's query string. If there is no such parameter, NULL is returned. If there are multiple parameters under the same name, their values are concatenated in a comma-separated string. For example:

    URLParam("google.com?q=teamdesk", "q") = "teamdesk"

  4. URLParam(url, param, index) function helps dealing with identically named parameters. If there are many, you can retrieve parameter's value by its name and 1-based index.

    URLParam("google.com?q=teamdesk&q="dbflex", "q", 2) = "dbflex"

Formatting

Format(date, fmt), Format(time, fmt) and Format(timestamp, fmt) support a number of additional standard and custom formatting patterns. For example,

Format(#2012-02-14#, "MM/yy") = "02/12"

Padding

  1. PadLeft(string, width, filler) right-aligns the characters in the string by padding them with specified filler character on the left, for a specified total width.
  2. PadRight(string, width, filler) left-aligns the characters in the string by padding them on the right with specified filler character, for a specified total width.

In both function filler parameter is optional and defaults to space. If the width of the string exceeds specified width the string is not padded.

Checkboxes

Two new functions, All(values, list) and Any(values, list) will help you dealing with text columns with Choices option set to checkboxes. For these columns, the value is a comma-separated concatenated string of checked values.

These two functions accept comma separated lists of values in both values and list arguments. First function checks that all values specified in its list argument exist in a values argument. Second function ensures that at least one value from list exists in values.

That's, All("A,B,C,D", "D,A") evaluates to true, while All("A,B,C,D", "D,E") evaluates to false because of E is not present in A,B,C,D. In contrast, both Any("A,B,C,D", "D,A") and Any("A,B,C,D", "D,E") evaluate to true because there is at least D that is present in A,B,C,D.

So, now the filter of the view that asks the user for checkbox column can be rewritten as simply as Ask(All([Text], [?Text])) or Ask(Any([Text], [?Text])) depending on a type of comparison you prefer.

Dates

MonthsBetween(date, date), QuartersBetween(date, date) and YearsBetween(date, date) return the number of boundaries [beginning of month, quarter, year] crossed between the specified dates. That's the result of MonthsBetween(#2012-01-01#, #2011-12-31#),
QuartersBetween(#2012-01-01#, #2011-12-31#) and
YearsBetween(#2012-01-01#, #2011-12-31#) is 1.

Enjoy!

Author
Kirill Bondar
Date
Share