How to Embed a Canvas App in Power Pages

Prerequisites

Before you begin, ensure that you have the following:

• Access to Power Platform:
  • A Power Apps environment with permissions to create and manage Canvas apps.
  • A Power Pages environment to embed the Canvas app.
• An existing Canvas app:
  • If you don’t have one, create a basic app in Power Apps Studio.

Step 1: Prepare Your Canvas App

1. Open Power Apps Studio:

   Navigate to Power Apps and log in.

2. Select or Create a Canvas App:

   Click Apps > New App > Canvas App to create a new app. If using an existing app, open it for editing.

3. Make Your App Responsive:

   Ensure the app is responsive by enabling responsiveness in settings:

   • Go to File > Settings > Screen size + orientation.
   • Enable the toggle for Scale to fit.

4. Publish the App:

   Once satisfied with the app, click File > Save > Publish to Teams or Web. Note the App ID. You can find it under Settings > Advanced Settings > App ID.

Step 2: Create or Access Your Power Pages Site

1. Navigate to Power Pages:

   Go to Power Pages.

2. Create a New Site:

   Click Create a site. Choose a template and configure the basics of your site.

3. Access an Existing Site:

   If you already have a Power Pages site, select it from the list.

Step 3: Embed the Canvas App into Power Pages

1. Go to the Page Designer:

   Select the desired site and navigate to Pages > Edit to open the page editor.

2. Add a Section to the Page:

   Add a new section by clicking the + icon. Choose a section layout that suits your app dimensions.

3. Insert an iFrame Component:

   In the section, select + Add Component. Choose the HTML or Embed Code option.

4. Add the Canvas App Embed Code:

   <iframe src="https://apps.powerapps.com/play/e/{AppID}?source=iframe" 
       width="100%" 
       height="600px" 
       frameborder="0" 
       allowfullscreen></iframe>

   Replace {AppID} with the actual ID of your Canvas app.

5. Configure iFrame Dimensions:

   Adjust the width and height attributes to fit the layout. For dynamic sizing, set width="100%" and use CSS for responsive height.

6. Save and Publish:

   Click Save and then Publish to make the changes live.

Step 4: Test the Embedded App

1. Open the Site in a Browser:

   Navigate to the published site URL.

2. Verify Functionality:

   Ensure the Canvas app is visible and interactive. Test responsiveness and app performance.

3. Debug Any Issues:

   If the app does not appear, verify the App ID and ensure the app is published. Check browser console for iFrame-related errors.

Additional Tips

Security Considerations:

• Ensure appropriate permissions are set for app users.
• Use Dataverse for secure data access.

Styling:

Apply CSS in Power Pages to style the iFrame container.

.canvas-app-container iframe {
    border: none;
    box-shadow: 0 2px 10px rgba(0, 0, 0, 0.2);
}

Monitoring:

Use Power Platform Admin Center to monitor app usage and performance.

Adding a Subgrid in a PowerApps Canvas App - Part II (Advanced)

Adding a Subgrid in a Canvas App - PowerApps

Introduction

Adding a subgrid in a Canvas App in PowerApps involves displaying related records (child data) for a selected parent record. While Canvas Apps do not have a built-in subgrid control like Model-Driven Apps, you can simulate a subgrid using a Gallery control and filtering based on relationships.

In this blog, I am going to show:

• Search functionality for parent or child records.
• Enable editing of child records directly in the subgrid.
• Hide the child Gallery if there are no related records.

For this example, we will use the same table structure from a previous blog:

• Parent Table: Orders (fields: OrderID, CustomerName, OrderDate)
• Child Table: OrderDetails (fields: OrderDetailID, OrderID, ProductName, Quantity)

Example Tables

Parent Table: Orders (fields: OrderID, CustomerName, OrderDate)

Child Table: OrderDetails (fields: OrderDetailID, OrderID, ProductName, Quantity)

Steps to Implement

1. Set Up Your Data Sources

• Ensure the Orders and OrderDetails tables are available as data sources in your app.
• Open your Canvas App in PowerApps Studio.
• Click Data in the left-hand menu and connect to your data sources.

2. Add a Gallery for the Parent Table (Orders)

• Insert a Gallery control to display the Orders table.
• Set the Items property of the Gallery to:

Orders

• Customize the Gallery to display fields like CustomerName and OrderDate.

3. Add a Gallery for the Child Table (OrderDetails)

• Insert another Gallery control for the child table.
• Set the Items property to:

Filter(OrderDetails, OrderID = GalleryOrders.Selected.OrderID)

• Customize this Gallery to display fields like ProductName and Quantity.

4. Add Search Functionality

• Add a Text Input control (e.g., txtSearch) for the search box.
• Modify the Items property of the parent Gallery:

Search(Orders, txtSearch.Text, CustomerName, OrderID)

• Modify the Items property of the child Gallery:

Filter(OrderDetails, 
    OrderID = GalleryOrders.Selected.OrderID && 
    (IsBlank(txtSearch.Text) || 
     Search(OrderDetails, txtSearch.Text, ProductName, OrderID) <> Blank()))

5. Enable Editing for Child Records

• Replace static labels in the child Gallery with Text Input controls for editable fields.
• Bind the Default property of each Text Input control:
• ProductName:

  ThisItem.ProductName

• Quantity:

  ThisItem.Quantity

• Add a Save button to update changes:

Patch(OrderDetails, ThisItem, {ProductName: TextInputProductName.Text, Quantity: Value(TextInputQuantity.Text)})

6. Hide the Child Gallery if There Are No Related Records

• Set the Visible property of the child Gallery to:

CountRows(Filter(OrderDetails, OrderID = GalleryOrders.Selected.OrderID)) > 0

Note: Test the app to ensure that filtering, search, and editing functionality work as expected. Use formatting and layout options for better visual distinction between parent and child records.

Adding a Subgrid in a PowerApps Canvas App - Part I

Adding a Subgrid in a Canvas App - PowerApps

Introduction

Adding a subgrid in a Canvas App in PowerApps involves displaying related records (child data) for a selected parent record. While Canvas Apps do not have a built-in subgrid control like Model-Driven Apps, you can simulate a subgrid using a Gallery control and filtering based on relationships.

Scenario Example

For this example, we will use:

• Parent Table: Orders (fields: OrderID, CustomerName, OrderDate)
• Child Table: OrderDetails (fields: OrderDetailID, OrderID, ProductName, Quantity)

Steps to Implement

1. Set Up Your Data Source

Ensure you have two related tables:

• OrderID in OrderDetails references the OrderID in Orders.

2. Add a Parent Gallery

1. Insert a Vertical Gallery for the parent records.
2. Set the Items property of the Gallery to:

   Orders

3. Customize the layout to display fields like OrderID and CustomerName.

3. Add a Subgrid (Child Gallery)

1. Insert another Gallery to act as the subgrid.
2. Place it inside the template area of the parent Gallery.
3. Set the Items property of the child Gallery:

   Filter(OrderDetails, OrderID = ThisItem.OrderID)

4. Add labels or controls to display fields like ProductName and Quantity.

4. Enhance the Subgrid

Adjust the layout of the child Gallery and enable scrolling:

• Use a border or background color to visually distinguish the child Gallery.

5. Add Interactivity

To dynamically select parent records, set the following variable on parent record selection:

Set(selectedOrderID, ThisItem.OrderID)

Then, use the variable to filter the child Gallery:

Filter(OrderDetails, OrderID = selectedOrderID)

6. Test the Subgrid

Preview your app and ensure the child Gallery updates correctly for selected parent records.

Example Layout Code

Parent Gallery Items:

Orders

Child Gallery (Subgrid) Items:

Filter(OrderDetails, OrderID = ThisItem.OrderID)

Optional Variable for Selected Parent Record:

Set(selectedOrderID, ThisItem.OrderID)

Optional Enhancements

• Add search functionality for parent or child records.
• Enable editing of child records directly in the subgrid.
• Hide the child Gallery if there are no related records.

© 2024 PowerApps Blog. All Rights Reserved.

Validating phone number to US Format in Dynamics 365 Power Apps Model Driven Apps

Validating Phone Numbers to US Format in Dynamics 365 Power Apps

To validate phone numbers to a US format in Dynamics 365 Power Apps Model-Driven Apps, you can use JavaScript. I am going to show you how to enforce formatting and validation when users input a phone number.

1. Determine US Phone Number Format

The typical US phone number format is (XXX) XXX-XXXX or +1 (XXX) XXX-XXXX. You can validate this using regular expressions.

2. Add JavaScript Web Resource

1. Navigate to Solutions in your Dynamics 365 instance.
2. Create a new JavaScript web resource or update an existing one.
3. Add the following script:

function validatePhoneNumber(executionContext) {
    // Get the form context
    var formContext = executionContext.getFormContext();

    // Get the phone number field value
    var phoneNumber = formContext.getAttribute("telephone1").getValue();

    if (phoneNumber) {
        // Define the US phone number regex pattern
        var regexPattern = /^\(?([0-9]{3})\)?[-.\s]?([0-9]{3})[-.\s]?([0-9]{4})$/;

        if (!regexPattern.test(phoneNumber)) {
            // Show an error message if the number is invalid
            formContext.getControl("telephone1").setNotification(
                "Please enter a valid US phone number in the format (XXX) XXX-XXXX or XXX-XXX-XXXX."
            );
        } else {
            // Clear the notification if the number is valid
            formContext.getControl("telephone1").clearNotification();
        }
    }
}
        

3. Associate the Script with a Form Event

1. Open the entity form where the phone number validation should occur.
2. Navigate to Form Properties.
3. Add the JavaScript web resource you created.
4. Add an OnChange event to the phone number field (e.g., telephone1) and bind it to the validatePhoneNumber function.

4. Test the Validation

Open the form in your Dynamics 365 app. Enter different phone numbers to verify the validation logic.

5. Optional Formatting

If you'd like to automatically format the number to (XXX) XXX-XXXX after validation, extend the script:

function formatPhoneNumber(executionContext) {
    var formContext = executionContext.getFormContext();
    var phoneNumber = formContext.getAttribute("telephone1").getValue();

    if (phoneNumber) {
        var regexPattern = /^\(?([0-9]{3})\)?[-.\s]?([0-9]{3})[-.\s]?([0-9]{4})$/;
        if (regexPattern.test(phoneNumber)) {
            var formattedPhone = phoneNumber.replace(regexPattern, "($1) $2-$3");
            formContext.getAttribute("telephone1").setValue(formattedPhone);
            formContext.getControl("telephone1").clearNotification();
        }
    }
}
        

Add this function to the OnChange event of the field in addition to validation.

Key Considerations

• Replace telephone1 with the actual logical name of your phone number field if it differs.
• Test thoroughly to ensure compatibility with your app and workflows.
• Consider user experience; avoid overly restrictive validation if not necessary.

Calling a SQL Server Stored Procedure from Power Fx

Introduction

Calling a SQL Server stored procedure from Power Fx (the formula language used in Power Apps) is still in preview. I was asked whether it’s possible to call database operations in SQL Server through a Custom Connector or a Power Automate flow. If your Power Apps environment has an established SQL Server connection, you can use the SQL Server connector to simplify the process.

1. Prerequisites

SQL Server Connection

• Go to Data > Add data > Select SQL Server.
• Provide the necessary credentials and connect to the appropriate database.

Stored Procedure Setup

Ensure that the stored procedures already exist in your SQL Server database. Example:

CREATE PROCEDURE GetCustomerById
    @CustomerId INT
AS
BEGIN
    SELECT * FROM Customers WHERE CustomerId = @CustomerId;
END;
            

SQL Permissions

Make sure the SQL Server user has EXECUTE permissions for the stored procedure.

2. Directly Call Stored Procedures

If your SQL Server connector supports it, stored procedures will appear as available actions. Verify that the stored procedures are properly configured in your database.

3. Using Power Automate as a Bridge

Create a Flow

• Open Power Automate and create a new flow.
• Add the SQL Server connector and choose the Execute stored procedure action.
• Specify the procedure name and required parameters.

Test the Flow

Run the flow with sample inputs to ensure the stored procedure executes correctly.

Expose the Flow in Power Apps

• Use the Power Apps trigger in the flow to make it callable from Power Apps.
• Save and publish the flow.

4. Integrate the Flow in Power Fx

Add the Flow to Power Apps

• Go to Data > Add Data.
• Search for and add the flow you just created.

Call the Flow Using Power Fx

Use this formula to trigger the flow:

Set(ResultVariable, YourFlowName.Run(Parameter1, Parameter2))
            

Replace YourFlowName with the name of your flow and pass the necessary parameters.

Handle Returned Data

If the flow returns data, you can store and display it:

ClearCollect(ResultsCollection, YourFlowName.Run(Parameter1, Parameter2))
            

Bind ResultsCollection to UI components like galleries or tables.

If the procedure only performs an action:

Notify("Procedure executed successfully!", NotificationType.Success)
            

5. Display or Process Data in Power Apps

Use collections to bind returned data to UI elements. For action-based procedures, show success notifications to the user.

Additional Notes

• Custom Connector Option: For complex scenarios, consider creating a Custom Connector in Power Apps to directly execute SQL operations, including stored procedures.
• Error Handling: Implement error handling in Power Automate to manage exceptions during stored procedure execution.
• Security: Ensure your Power Apps/Power Automate plan supports the SQL Server connector and stored procedure execution.

Limitations to Consider

• Connector Dependency: Directly calling stored procedures depends on the SQL Server connector’s capabilities.
• Plan Requirements: Ensure your Power Apps licensing plan supports premium connectors like SQL Server.
• Security: Follow best practices to secure your database and use least-privilege access to prevent data exposure.

© 2024 SQL Server Integration Guide. All Rights Reserved.

Microsoft Power Fx Common Expressions (Cheat Sheet)

Microsoft Power Fx Common Expressions

Introduction

Power Fx is a low-code, Excel-like programming language for canvas apps in Microsoft Power Apps. This cheat sheet provides quick reference to commonly used formulas, syntax, and patterns to accelerate your development.

---

1. Basic Syntax

• Comments:

// Single line comment
/* Multi-line comment */

• Constants:

true, false
"Text", 123, 123.45

• Variables:

Set(variableName, value); // Global variable
UpdateContext({contextVariable: value}); // Context variable

• Operators:
• Arithmetic: +, -, *, /, ^
• Comparison: =, <>, >, <, >=, <=
• Logical: And, Or, Not

---

2. Working with Data

Tables

• Create a table:

Table({Column1: Value1, Column2: Value2}, {Column1: Value3, Column2: Value4})

• Filter rows:

Filter(TableName, ColumnName = "Value")

• Sort data:

Sort(TableName, ColumnName, Ascending)

• Add a column:

AddColumns(TableName, "NewColumn", Formula)

• Drop a column:

DropColumns(TableName, "ColumnName")

• Rename a column:

RenameColumns(TableName, "OldName", "NewName")

Records

• Create a record:

{Field1: Value1, Field2: Value2}

• Access a field:

RecordName.FieldName

• Update a record:

Patch(TableName, RecordToUpdate, {FieldName: NewValue})

---

3. Functions

Text Functions

• Concatenate:

Concatenate("Hello", " ", "World")

• Convert to Uppercase/Lowercase:

Upper("text")
Lower("TEXT")

• Trim Spaces:

Trim(" text ")

• Substring:

Mid("Text", StartIndex, Length)

Math Functions

• Rounding:

Round(Number, DecimalPlaces)

• Random number:

Rand()

• Absolute value:

Abs(Number)

Logical Functions

• Conditional:

If(Condition, TrueResult, FalseResult)

• Switch:

Switch(Expression, Value1, Result1, Value2, Result2, DefaultResult)

Date and Time Functions

• Current date/time:

Now()
Today()

• Date difference:

DateDiff(StartDate, EndDate, Unit)

• Add to a date

DateAdd(Date, Number, Unit)

---

4. User Interface Functions

• Navigate between screens:

Navigate(ScreenName, Transition)

• Show a message:

Notify("Message", NotificationType)

• Refresh data:

Refresh(DataSource)

• Toggle visibility:

!BooleanVariable

---

5. Common Expressions

• Set Variables:

Set(UserName, "John Doe");
Set(UserAge, 30);

• Navigate Between Screens:

Navigate(HomeScreen, ScreenTransition.Fade);

• Update Context Variables:

UpdateContext({IsVisible: true});

• Display Conditional Text:

If(IsLoggedIn, "Welcome back!", "Please sign in.");

• Filter Records:

Filter(Products, Category = "Electronics");

• Sort Records:

Sort(Employees, Name, Ascending);

• Submit a Form:

SubmitForm(EditForm);

---

6. Complex Expressions

• Chained Filtering and Sorting:

Sort(Filter(Products, Category = "Electronics" And Price > 100), Price, Descending)

• Dynamic Gallery Items Based on Multiple Conditions:

Filter(Products, (Category = Dropdown.Selected.Value Or IsBlank(Dropdown.Selected.Value)) And (StartsWith(Name, SearchBox.Text) Or IsBlank(SearchBox.Text)))

• Cascading Dropdowns:

Filter(Subcategories, CategoryID = DropdownCategories.Selected.ID)

• Aggregate Data Example:

Sum(Filter(Sales, Region = Dropdown.Selected.Value), Amount)

• Generating a Sequential Number for Items:

ForAll(Sequence(CountRows(Products)), {Index: Value})

• Conditional Formatting for a Gallery Item:

If(ThisItem.Stock < 10, Color.Red, Color.Green)

• Custom Error Handling:

If(IsEmpty(Filter(Products, ID = InputID.Text)), Notify("Product not found!", NotificationType.Error), Navigate(ProductDetailsScreen))

---

For more detailed documentation, visit the official Microsoft Power Fx documentation.

Using Custom Page to Pop up a Dialog Box, Allow User to Input values, Save the record in a Dataverse table

Custom Page Implementation in Model-Driven Apps

Scenario: Yesterday, someone from WhatsApp group asked me to help on below scenario, How to implement a requirement where clicking a button opens a dialog box in Model-Driven Apps, allowing users to input values for two fields and save the record in a Dataverse table.

Steps to Implement

1. Set Up the Custom Page Navigation

• Add a button to the ribbon or command bar of your Model-Driven App.
• Use Power Fx or JavaScript to configure the button’s OnSelect action.
• Trigger the navigation to the Custom Page dialog.

Power Fx Code:

Navigate( 'YourCustomPageName', ScreenTransition.None, { entityId: ThisItem.PrimaryId, entityLogicalName: "YourTableLogicalName" } )

2. Open the Custom Page as a Dialog

Use the Xrm.Navigation.navigateTo() API in a JavaScript web resource.

JavaScript Code:

function openCustomPageDialog() { var pageInput = { pageType: "custom", name: "your_custom_page_name" }; var navigationOptions = { target: 2, // Opens in a dialog width: { value: 50, unit: "%" }, // Dialog width height: { value: 50, unit: "%" } // Dialog height }; Xrm.Navigation.navigateTo(pageInput, navigationOptions); }

Bind this JavaScript function to the button in the ribbon.

3. Enable Save Functionality

Pass the record ID (entityId) of the record from which the custom page was called. This ensures updates in the custom page are applied to the correct Dataverse table record.

Power Fx Code:

Patch( Table(TableName), LookUp(Table(TableName), Table(TableName).PrimaryId = RecordId), { Field1: TextInput1.Text, Field2: TextInput2.Text } );

Display Notifications:

Success Message:

Notify("Record saved successfully!", NotificationType.Success);

Error Message:

Notify("Failed to update the record. Please try again.", NotificationType.Error)

Combined Power Fx Code:

If( Patch( Table(TableName), LookUp(Table(TableName), Table(TableName).PrimaryId = RecordId), { Field1: TextInput1.Text, Field2: TextInput2.Text } ), Notify("Record updated successfully!", NotificationType.Success), Notify("Failed to update the record. Please try again.", NotificationType.Error) )