Rajeev Pentyala – Microsoft Power Platform

In this article, I am collating the useful formulas which are frequently used in Canvas Apps.

For all functions, I am going to use a Textbox control ‘txtInput’ as reference.

Match

Validates User inputs based on predefined or custom patterns. For example, you can confirm whether the user has entered a valid email address, SSN, etc…

Validate Email:

• IsMatch(txtInput.Text,Email)

Validate SSN:

• IsMatch( txtInput.Text, Digit & Digit & Digit & Hyphen & Digit & Digit & Hyphen & Digit & Digit & Digit & Digit )

Validate SSN using Regular Expression (RegEx)

• IsMatch( txtInput.Text, “\d{3}-\d{2}-\d{4}” )

Check presence of string

• IsMatch( txtInput.Text, “hello”, Contains & IgnoreCase)
  • Check if ‘hello’ exists in the Text input.

User

Get Current User Email

• User().Email

Get Current User Fullname

• User().FullName

Get Profile Image

• User().Image
  • Add an Image control and set ‘Image’ property with User().Image.

Date functions

Today()

• Returns the current date as a date/time value.
• The time portion is always midnight (i.e., 12:00:00). 

Now()

• Returns the current date and time as a date/time value

IsToday()

• Checks whether a date/time value is between midnight today and midnight tomorrow. Returns a Boolean (true or false) value.

Weekday()

• Weekday(Date)
  • Returns between 1-7 and Sunday is ‘1’.

DateDiff()

• DateDiff(Date1.SelectedDate, Date2.SelectedDate, Days)
  • ‘Date1’ and ‘Date2’ are ‘Date Picker’ controls.

Notify

• Notify function displays a banner message to the user at the top of the screen. The notification will remain until the user dismisses it, another notification replaces it, or the timeout expires which defaults to 10 seconds.
  • Syntax: Notify(“This is error and disappears in 2 seconds”,NotificationType.Error,2000);

Reset

• Resets a control to its Default property value. Any user changes are discarded.
  • Syntax:
    • Reset(TextInput1); // Reset control
    • Reset(Form); // Reset Form

Split

• Split function breaks a text string into a table of substrings.
  • Syntax: Split( “Apples, Oranges, Bananas”, “,” )
  • Get the last word from Split results.
    • Last(Split(“Apples, Oranges, Bananas”, “,”)).Result // Returns ‘Bananas’

Error Handling

• To display custom validation message on ‘Form’ submit, use ‘OnFailure’ event of Form and specify the message using ‘Notify’.

• To check all validations are passed or not, use Form’s Valid property. Following is the condition to disable button until validations are passed.

Get the ‘Security Roles’ of current User (Data Verse)

// Get the User record by 'Email' and Set to 'currUser' variable.
ClearCollect(
    currUser,
    First(
        Filter(
            Users,
            'User Name' = User().Email
        )
    )
);

// Read the 'Security Roles' from 'currUser' and Set it to 'userRoles' collection.
// Security roles will be assigned to 'userRoles' variable.
ClearCollect(
    userRoles,
    AddColumns(
        First(currUser).'Security Roles (systemuserroles_association)'.Name,
        "nm",
        Name
    )
);

Check whether logged in User has specific roles

// Option - I (Check multiple roles)
// Configure the 'Roles' you want to check in 'requiredRoles' variable.
ClearCollect(
    requiredRoles,
    [
        "System Administrator",
        "Sales Person"
    ]
);
// Get the Logged in user roles in to 'userRoles' variable as mentioned in above section.
// Loop through 'requiredRoles' using 'ForAll' and compare with 'userRoles'. If matched store in 'matchedRoles' variable.

ForAll(
    requiredRoles,
    If(
        CountRows(
            Filter(
                userRoles,
                nm = Value
            )
        ) > 0,
        Collect(
            matchedRoles,
            ThisRecord
        )
    )
);

// Check if 'matchedRoles' count greater than 0 and set the 'doesRolesMatched' variable.
Set(
    doesRolesMatched,
    CountRows(matchedRoles) > 0
);

// Option - II (Check single role)
// If User has "System Administrator" role, variable 'doesRoleMatched' will be set to true.
If(
    "System Administrator" in (LookUp(
        Users,
        'User Name' = User().Email
    ).'Security Roles (systemuserroles_association)').Name,
    Set(doesRoleMatched, true),
    Set(doesRoleMatched, false)
);

Miscellaneous

• IsBlank(txtInput)
  • Checks for a blank value or an empty string.
• Coalesce
  • Evaluates its arguments in order and returns the first value that isn’t blank or an empty string.
  • Use this function to replace a blank value or empty string with a different value but leave non-blank and non-empty string values unchanged.

• GUID
  • GUID() – Returns a new Guid.
    • Set( NewGUID, GUID() )
  • To generate 5 new GUIDs and set to Collection
    • ClearCollect( NewGUIDs, ForAll( Sequence(5), GUID() ) )

• Set() vs UpdateContext()
  • Both functions are used to create variables.
  • Set() – Creates a ‘Global Variable’ and can be consumed throughout entire App.
  • UpdateContext() – Creates a ‘Local Variable’ and can only be consumed in Screen.

• Copy/Clone an existing Canvas App
  • Open Canvas App using ‘Edit’ option.
  • From the menu, select File -> Save as

🙂