·
2 min read

More Regular Expressions for Canvas apps

For regular readers of this blog, you may recall a blog post by Carlos Figueira on working with SQL server time values.  In it, Carlos used the Mid, Find, Substitute, and other functions to parse an ISO 8601 duration string such as “PT2H1M39S” which represents 2 hours, 1 minute, and 39 seconds.

Carlos’ solution works great but is many lines of nested formulas.  One couldn’t help but see the opportunity for a regular expression to help with the parsing.  Canvas apps have had the IsMatch function for a while now for testing a regular expression, great for validation, but we have lacked a way to extract the string that was matched.

That is until now.   I’m very pleased to announce that we have added two new functions to Canvas apps:

  • Match for returning a single match, complete with sub-matches.
  • MatchAll for returning multiple matches.

With these functions, the core of Carlos’ time parsing solution is reduced from 30+ lines to a one liner:

Match( "PT2H1M39S", "PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?" )

Sure, the pattern looks like a random combination of punctuation marks and is not a very user friendly syntax.  Welcome to regular expressions.  Regular expressions are the standard for pattern matching across almost all computer languages and many tools, from JavaScript to Visual Studio Code. We tried to make it a little easier for novices with the Match enum values for common sequences such as MultipleDigits.

In introducing these functions we took advantage of an emerging trend in regular expressions: named sub-matches.  The result of Match is a record with named groups as fields making it easier to use the result.  In our example above, Match returns:

{
    hours: "2",
    minutes: "1",
    seconds: "39",
    FullMatch: "PT2H1M39S",
    StartMatch: 1,
    SubMatches: [ "2", "1", "39" ]
}

This is accomplished with the (?<name>…) syntax used above.  The FullMatch, StartMatch, and SubMatches fields are also present, consistent with more traditional regular expression usage.

Now we just need a convenient way to use the fields of a calculated record in a formula. We don’t quite have that yet (we’re working on that too) but in the meantime we can use the ForAll function to create a record context for all the records of a table. Conveniently the MatchAll function returns a table, and in our case only one match will result in only one record so we only need to look at the First result record. All put together, the full replacement for Carlos’ original blog post becomes:

First(
    ForAll(
        MatchAll( "PT2H1M39S", "PT(?:(?<hours>\d+)H)?(?:(?<minutes>\d+)M)?(?:(?<seconds>\d+)S)?" ),
        Time( Value( hours ), Value( minutes ), Value( seconds ) )
    )
).Value

As I said, we are working on a cleaner solution which we hope to have out soon. And then we can refine this solution even further. 🙂

The documentation for the Match and MatchAll functions has all the details on using these functions.

As always, questions, comments, and feedback are most welcome.

Note: This feature is still rolling out to all regions.  If you don’t see this functionality yet, it will be coming very soon.  Look for version 3.19012 in File > Account.

Greg Lindhorst
Greg Lindhorst