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) )

JavaScript to automatically select a record in a subgrid when the form loads

Yesterday, someone from a WhatsApp group asked me to help on the following scenario:

“Any idea, when form loads, check form type if not new and has related record in subgrid then I need to select record on subgrid automatically. Any suggestion how we can use JavaScript?”

To achieve the requirement where a form loads, checks if the form type is not "new," and selects a related record in a subgrid if it exists, you can use JavaScript in your model-driven app. Here's how to implement this:

Step-by-Step Implementation

1. Setup Your Subgrid and Form

• Ensure the subgrid is configured to display related records.
• Identify the subgrid's name (you can find it in the form editor by selecting the subgrid and checking its properties, e.g., subgrid_name).

2. Add the JavaScript to Your Solution

async function onFormLoad(executionContext) {
    // Get form context
    const formContext = executionContext.getFormContext();

    // Check if form type is not "new"
    if (formContext.ui.getFormType() !== 1) { // 1 = Create form (New)
        try {
            // Wait for the subgrid to load and get its rows
            const subgridRows = await waitForSubgridLoad(formContext, "subgrid_name");

            // If subgrid has rows, perform your logic
            if (subgridRows.length > 0) {
                // Example: Select the first record or fetch data from an API
                const firstRow = subgridRows[0];

                // Call an API or perform additional operations (example API call)
                const apiData = await fetchApiData(firstRow.getData().getEntity().getId());
                console.log("API Data:", apiData);

                // Select the row in the subgrid
                firstRow.setSelected(true);
            }
        } catch (error) {
            console.error("Error handling subgrid or API:", error);
        }
    }
}

// Wait for the subgrid to load
function waitForSubgridLoad(formContext, subgridName) {
    return new Promise((resolve, reject) => {
        const subgrid = formContext.getControl(subgridName);
        if (!subgrid) {
            reject(new Error(`Subgrid '${subgridName}' not found.`));
            return;
        }

        // Subgrid may not load immediately; wait for it to finish loading
        subgrid.addOnLoad(() => {
            try {
                const rows = subgrid.getGrid().getRows();
                resolve(rows);
            } catch (err) {
                reject(err);
            }
        });
    });
}

// Fetch data from an API (example function)
async function fetchApiData(recordId) {
    const apiUrl = `https://your-api-endpoint.com/data/${recordId}`; // Replace with your actual API endpoint
    const response = await fetch(apiUrl, {
        method: "GET",
        headers: {
            "Content-Type": "application/json",
            "Authorization": "Bearer YOUR_ACCESS_TOKEN" // Replace with actual token if needed
        }
    });

    if (!response.ok) {
        throw new Error(`API request failed with status: ${response.status}`);
    }

    return response.json();
}

3. Add the JavaScript to Your Form

• Upload the JavaScript file to your solution as a web resource.
• Open the form editor for the entity.
• Go to Form Properties > Events tab.
• Add the web resource under the Form Load event.
• Add the function onFormLoad and ensure you pass the executionContext parameter.

Key Notes:

1. Custom Selection Logic:
   • If you want to select a specific record based on certain criteria, you can loop through the rows collection and match the record based on your conditions:

     rows.forEach(function (row) {
         var data = row.getData();
         var entity = data.getEntity();
         if (entity.getId() === "desired_record_id") {
             row.setSelected(true);
         }
     });

Microsoft Power Fx formula to Auto Populate User Name and Time Stamp

Yesterday, someone from a WhatsApp group asked me to help with the following scenario:

I have a Power Apps requirement:
Created 2 fields: First Response By and First Response On on the form.
When a case is assigned to a user, that user's value should auto-populate in the First Response By field, and the time should be filled in the First Response On field.

To achieve this Power Apps requirement, you can use Power Fx formulas to configure the behavior for the First Response By and First Response On fields. Here's a step-by-step guide:

Steps to Implement:

1. Add Fields to the Form:

• Ensure the fields First Response By (Text/Person field) and First Response On (DateTime field) are added to your data source (e.g., Dataverse table, SharePoint list, etc.) and included in the form.

2. Set Default Value for "First Response By":

When the case is assigned to a user, this field should auto-populate with the assigned user's name.

• Go to the OnChange property of the field used to assign the case (e.g., "Assigned To").
• Add this formula:

If(
  IsBlank(ThisItem.'First Response By'),
  UpdateIf(
    YourDataSource,
    ID = ThisItem.ID,
    { 'First Response By': AssignedTo.DisplayName }
  )
)

• Replace YourDataSource with the name of your table and AssignedTo with the name of your user assignment field.

3. Set Time for "First Response On":

To capture the timestamp when the case is assigned, you can use the OnChange property of the same "Assigned To" field.

• Add this formula:

If(
  IsBlank(ThisItem.'First Response On'),
  UpdateIf(
    YourDataSource,
    ID = ThisItem.ID,
    { 'First Response On': Now() }
  )
)

4. Handle Updates in the Form:

If you're working with a Power Apps form, ensure these updates are reflected correctly:

• Use the Patch function to update the fields in your data source whenever the "Assigned To" field changes:

Patch(
  YourDataSource,
  ThisItem,
  {
    'First Response By': AssignedTo.DisplayName,
    'First Response On': If(IsBlank('First Response On'), Now(), 'First Response On')
  }
)

Optional Enhancements:

• Add validation to ensure these fields are not overwritten after the first update.
• Use conditions to handle edge cases (e.g., if a case is reassigned).