Wednesday, December 25, 2024

Create Address Autofill and Validation in Model-Driven Apps Using PCF Control (Google Maps Places API)

How to Create Address Autofill and Validation in Model-Driven Apps Using PCF Control

How to Create Address Autofill and Validation in Model-Driven Apps Using PCF Control

Model-Driven Apps in Microsoft Power Platform provide a robust way to deliver enterprise solutions. However, custom functionalities like address autofill and validation require extending the default capabilities using PowerApps Component Framework (PCF). In this blog, we will walk through creating an Address Autofill and Validation PCF control step-by-step.

Prerequisites

Ensure you have the following tools before starting:

  • Node.js
  • Power Platform CLI
  • Visual Studio Code (my preferred IDE)
  • Dataverse Environment
  • API for Address Data (e.g., Google Maps API)

Step 1: Set Up Your Development Environment

// Install Node.js and Power Platform CLI
npm install -g pac

// Create a New PCF Control Project
pac pcf init --namespace AddressAutofill --name AddressAutofillControl --template field

// Navigate to the Project Directory
cd AddressAutofillControl

// Install Dependencies
npm install
        

Step 2: Design the Control

Edit ControlManifest.Input.xml to define properties:

<property name="address" display-name-key="address" type="SingleLine.Text" usage="bound" />
<property name="validatedAddress" display-name-key="validatedAddress" type="SingleLine.Text" usage="output" />

<resources>
    <js path="AddressAutofillControl/index.js" order="1" />
    <css path="AddressAutofillControl/style.css" order="1" />
</resources>
        

Step 3: Add Address Autofill Functionality

Install and configure Google Maps Places API:

// Install the API
npm install @googlemaps/js-api-loader

// Import and initialize the API
import { Loader } from '@googlemaps/js-api-loader';

const loader = new Loader({
    apiKey: 'YOUR_GOOGLE_MAPS_API_KEY',
    version: 'weekly',
    libraries: ['places'],
});
        

Create the input field:

public init(context: ComponentFramework.Context<IInputs>, notifyOutputChanged: () => void, state: ComponentFramework.Dictionary, container: HTMLDivElement): void {
    const inputElement = document.createElement('input');
    inputElement.id = 'addressInput';
    inputElement.type = 'text';
    inputElement.placeholder = 'Enter your address';
    container.appendChild(inputElement);

    loader.load().then(() => {
        const autocomplete = new google.maps.places.Autocomplete(inputElement);
        autocomplete.addListener('place_changed', () => {
            const place = autocomplete.getPlace();
            if (place.formatted_address) {
                context.parameters.address.raw = place.formatted_address;
                notifyOutputChanged();
            }
        });
    });
}
        

Step 4: Add Address Validation Logic

Define and integrate validation logic:

// Validation function
private validateAddress(address: string): boolean {
    const regex = /^[A-Za-z0-9\s,.-]+$/;
    return regex.test(address);
}

// Highlight validation status
public updateView(context: ComponentFramework.Context<IInputs>): void {
    const inputField = document.getElementById('addressInput') as HTMLInputElement;
    const address = context.parameters.address.raw;

    if (this.validateAddress(address)) {
        inputField.style.borderColor = 'green';
    } else {
        inputField.style.borderColor = 'red';
    }
}
        

Step 5: Build and Test the PCF Control

// Build the control
npm run build

// Test locally using the harness
npm start
        

Step 6: Deploy the PCF Control to Dataverse

// Add the control to a solution
pac solution add-reference --path ./AddressAutofillControl

// Publish the solution
pac solution publish
        

Step 7: Integrate the PCF Control into the Model-Driven App

In the form editor, add the PCF control to the desired field, configure properties, and publish the changes.

Sunday, December 22, 2024

How to Embed a Canvas App in Power Pages

How to Embed a Canvas App in Power Pages

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)

Simulate a Subgrid in PowerApps Canvas App

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

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

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

Calling a SQL Server Stored Procedure from Power Fx

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)

Power Fx Common Expressions

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.

Friday, December 20, 2024

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

Custom Page Implementation Guide

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

Thursday, December 19, 2024

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

JavaScript Subgrid Selection

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

Power Apps Guide

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

Send an email to all users of a team in Dynamics 365 when a checkbox is checked

Dynamics 365 Team Email Notification

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

"I have created one team in Dynamics 365. I want to send an email to all the users when one of the checkboxes on the form is enabled.

(I am able to fetch the team name, but not able to fetch the users' email IDs of that group.) Any idea how to send an email to all the users of the team?"

To send an email to all users of a team in Dynamics 365 when a checkbox is enabled, you can follow these steps:

1. Understand Dynamics 365 Team and Users Relationship

  • In Dynamics 365, each Team is associated with multiple Users.
  • The relationship is typically stored in the TeamMembership table, linking Teams and Users.

2. Fetch Users' Email Addresses from the Team

To fetch the email addresses of users in a team, you can query the TeamMembership and SystemUser tables:

  • Team Table: Get the Team's GUID.
  • TeamMembership Table: Use the Team's GUID to retrieve associated User IDs.
  • SystemUser Table: Fetch email addresses for those User IDs.

3. Use a Plugin, Workflow, or Power Automate

Option 1: Using a Plugin

If you're using a plugin, the logic can look like this:

Trigger Point:

  • The plugin is triggered when the checkbox field is updated.

Plugin Code Sample:


public void Execute(IServiceProvider serviceProvider)
{
    IPluginExecutionContext context = 
        (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
    IOrganizationServiceFactory serviceFactory = 
        (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
    IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

    // Fetch the team ID from the context or related entity
    Guid teamId = new Guid("TEAM_GUID");

    // Query to fetch users in the team
    QueryExpression query = new QueryExpression("teammembership")
    {
        ColumnSet = new ColumnSet(false)
    };
    query.Criteria.AddCondition("teamid", ConditionOperator.Equal, teamId);
    EntityCollection teamMemberships = service.RetrieveMultiple(query);

    // Collect user emails
    List emails = new List();
    foreach (var teamMembership in teamMemberships.Entities)
    {
        Guid userId = teamMembership.GetAttributeValue("systemuserid");
        Entity user = service.Retrieve("systemuser", userId, new ColumnSet("internalemailaddress"));
        string email = user.GetAttributeValue("internalemailaddress");
        if (!string.IsNullOrEmpty(email))
        {
            emails.Add(email);
        }
    }

    // Send email logic
    // Create and send email using the collected email addresses
}
    

Option 2: Using Power Automate

  1. Trigger: Set the trigger as When a record is updated for the entity containing the checkbox.
  2. Actions:
    • Use the team ID to fetch the team record.
    • List team members using the List Rows action on the TeamMembership table with the teamid filter.
    • Fetch user emails from the SystemUser table.
    • Send an email using the collected email addresses.

Option 3: Using a Workflow

  1. Trigger: The workflow is triggered when the checkbox is updated.
  2. Steps:
    • Retrieve users in the team using the RetrieveMultiple method.
    • Send an email to all retrieved users.

Calculate Age from Date of Birth using PowerFx

Calculate Age in PowerFX

Calculate Age using Power FX formula

To calculate age using Power FX (the formula language used in Microsoft Power Apps), you can use a formula that calculates the difference between the current date and the date of birth (DOB).

PowerFX Code:

// Assume DateOfBirth is a Date field in your app
Age = DateDiff(DateOfBirth, Today(), Years)

Explanation:

  • DateOfBirth: The field that contains the user's birthdate.
  • Today(): This function gets the current date.
  • DateDiff: This function calculates the difference between two dates.
  • Years: The unit for the difference, which will return the age in years.

Example Use Case in a Label Control:

  1. Suppose you have a form with a DateOfBirth field.
  2. Add a label to your Power App.
  3. In the Text property of the label, enter the following formula:
"Age: " & Text(DateDiff(DateOfBirth.SelectedDate, Today(), Years))

This will display the age in years based on the selected date of birth in the DateOfBirth field.

Considerations:

  • This method assumes the person has already had their birthday this year. If the birthday hasn't occurred yet in the current year, the result will be one year less.
  • If you need to adjust this for precise age calculation (e.g., considering if the birthday has passed this year), you can add more logic to handle that.

Migrate KB Articles with all attachments from Dynamics 365/CRM

KB Articles Migration Guide

KB Articles Migration Guide

Yesterday, someone from a WhatsApp group asked me to help with "How to migrate KB Articles with all attachments from CRM to another system".

Migrating Knowledge Base (KB) Articles along with their attachments from Microsoft Dynamics 365 CRM to another system requires careful planning. Below is a detailed step-by-step approach to achieve this:


1. Analyze the Target System

Before starting the migration, ensure the target system supports the following:

  • KB Articles: The system should have an equivalent data structure for KB Articles.
  • Attachments: Ensure the target system can handle attachments and metadata such as filenames and file types.

2. Export KB Articles and Attachments from CRM

In Dynamics 365 CRM, KB Articles are stored as records in the KnowledgeArticle entity. Attachments are stored in the Annotation entity.

Option A: Use Power Automate or Power Platform Dataflows

  1. Export KB Articles:
    • Use Dataverse (Common Data Service) connector in Power Automate to extract KB Articles from the KnowledgeArticle table.
    • Export key fields such as:
      • Title
      • Content
      • Author
      • Keywords
      • State (Published/Draft)
      • Created On / Modified On
  2. Export Attachments:
    • Extract the corresponding records from the Annotation table where the objectid field links to the KnowledgeArticleId.
    • For each attachment:
      • Save the notetext (description), filename, and the encoded file (documentbody).
  3. Store Data:
    • Save the exported data (articles and attachments) in a structured format like CSV, JSON, or Azure Blob Storage.

Option B: Use a Third-Party Tool

  • Tools like KingswaySoft, Scribe, or XrmToolBox plugins (e.g., "Attachment Manager") can help you export KB Articles and attachments in bulk.

3. Transform Data for the Target System

  • Clean and prepare the exported data to match the schema of the target system.
  • Convert the CRM-specific fields (e.g., documentbody for attachments, which is base64 encoded) into formats required by the target system.

4. Import KB Articles and Attachments into the Target System

Option A: Use Target System's API

  1. KB Articles:
    • Use the target system’s API to create articles.
    • Map fields from the exported data (e.g., title, content, metadata) to the corresponding fields in the target system.
  2. Attachments:
    • Upload attachments using the target system’s API by:
      • Decoding the documentbody (base64).
      • Attaching the file to the corresponding article.

Option B: Use a File-Based Import

If the target system supports file-based imports:

  1. Prepare files (e.g., CSV, JSON, or XML) for articles and attachments.
  2. Use the target system’s import functionality to upload the data.

5. Validate the Migration

  • Check that all KB Articles have been imported correctly.
  • Ensure that attachments are linked to the correct articles and are accessible.
  • Test article searchability and formatting in the target system.

Sample Power Automate Flow

A high-level view of how to use Power Automate for this migration:

  1. Trigger: Run manually or on schedule.
  2. Action (Dataverse): Fetch records from KnowledgeArticle table.
  3. Action (Dataverse): Fetch related Annotation records for attachments.
  4. Action (Apply to Each):
    • Save articles and attachments to a target storage location or directly post to the target system API.

Custom Migration Tool for Dynamics 365

However, I suggested using a custom console application built with C# to create your own migration tool.

The code leverages the Dataverse Web API along with the Microsoft.Xrm.Sdk and Microsoft.Crm.Sdk namespaces.

Below is the sample implementation:

Prerequisites

  1. Add the following NuGet packages:
    • Microsoft.PowerPlatform.Dataverse.Client
    • Microsoft.CrmSdk.CoreAssemblies
    • Newtonsoft.Json (if needed for API calls to the target system)
  2. Set up an Azure App Registration for authentication to Dynamics 365 CRM.
  3. Provide necessary permissions to the app (Dataverse scope for KnowledgeArticle and Annotation).

Code

using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk;
using System;
using System.IO;
using System.Text;
using System.Collections.Generic;

namespace KBMigration
{
    /// <summary>
    /// A console application designed to migrate Knowledge Base (KB) Articles
    /// along with their related attachments from Dynamics 365 CRM to a local directory.
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {
            // Step 1: Initialize the connection to the Dataverse (Dynamics 365) CRM
            string connectionString = "AuthType=OAuth;Url=https://yourorg.crm.dynamics.com;Username=yourusername;Password=yourpassword;AppId=your-app-id;RedirectUri=your-redirect-uri;";

            try
            {
                // Establish a service client to interact with CRM
                var serviceClient = new ServiceClient(connectionString);

                // Check if the connection is successful
                if (!serviceClient.IsReady)
                {
                    throw new InvalidOperationException("Failed to connect to CRM. Please check your credentials and network connection.");
                }

                Console.WriteLine("Connected to CRM successfully!");

                // Step 2: Retrieve and process KB Articles
                var articles = RetrieveKBArticles(serviceClient);

                if (articles.Entities.Count == 0)
                {
                    Console.WriteLine("No KB Articles found in CRM.");
                }
                else
                {
                    foreach (var article in articles.Entities)
                    {
                        Guid articleId = article.Id;
                        string title = article.GetAttributeValue<string>("title");
                        string content = article.GetAttributeValue<string>("content");

                        Console.WriteLine($"Processing Article: {title} (ID: {articleId})");

                        var attachments = RetrieveAttachments(serviceClient, articleId);
                        SaveArticleAndAttachments(articleId, title, content, attachments);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }

        static EntityCollection RetrieveKBArticles(ServiceClient serviceClient)
        {
            // Implementation for retrieving KB Articles
        }

        static EntityCollection RetrieveAttachments(ServiceClient serviceClient, Guid articleId)
        {
            // Implementation for retrieving attachments
        }

        static void SaveArticleAndAttachments(Guid articleId, string title, string content, EntityCollection attachments)
        {
            // Implementation for saving articles and attachments
        }
    }
}
        

Potential Enhancements

  • Retry Logic: Add logic for retrying failed operations (useful for network-related errors).
  • Pagination Handling: If there are too many KB Articles or attachments, implement pagination to handle large datasets efficiently.
  • Parallel Processing: Consider parallel processing to speed up the migration (using Task.WhenAll or Parallel.ForEach).

Key Considerations

  1. Authentication

    Use Azure Active Directory authentication for security (replace Username/Password with ClientSecret for production).

  2. Scalability

    For large datasets, consider using pagination with RetrieveMultiple.

  3. Custom Fields

    If you have custom fields in KB Articles, include them in the ColumnSet.

  4. Migration to Target System

    Replace the SaveArticleAndAttachments method with an API call or logic to import the data into your target system.

Example of Advanced UI customization in PowerApps, using custom HTML, CSS, and JavaScript

PowerApps Canvas Apps Customization

PowerApps Canvas Apps allow limited customization directly with HTML, CSS, and JavaScript. However, you can use HTML Text Controls, integrate PowerApps Portals for advanced customization, or use Power Automate with Azure Functions for JavaScript-like logic. Here's an example of advanced UI customization using HTML web resources in a PowerApps Canvas App:

Scenario

Create a responsive and visually appealing data table with Search/Filtering Functionality using advanced HTML and CSS styling embedded in a PowerApps Canvas App.

Steps to Implement

1. Design Your HTML/CSS Web Resource

Create an HTML file with embedded CSS for a styled table. Save it as a web resource in your Dataverse environment.

Example HTML (HTML File):

<!DOCTYPE html>
<html>
<head>
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <style>
    body {
      font-family: Arial, sans-serif;
      margin: 0;
      padding: 0;
    }

    .custom-table-container {
      margin: 20px;
      overflow-x: auto; /* Enables horizontal scrolling on smaller screens */
    }

    .search-container {
      margin: 10px 20px;
      text-align: right;
    }

    .search-input {
      width: 100%;
      max-width: 300px;
      padding: 10px;
      border: 1px solid #ddd;
      border-radius: 5px;
      font-size: 16px;
    }

    .custom-table {
      width: 100%;
      border-collapse: collapse;
      margin: 20px 0;
    }

    .custom-table th,
    .custom-table td {
      border: 1px solid #ddd;
      padding: 10px;
      text-align: left;
    }

    .custom-table th {
      background-color: #0078D7;
      color: white;
    }

    .custom-table tr:nth-child(even) {
      background-color: #f2f2f2;
    }

    .custom-table tr:hover {
      background-color: #ddd;
    }

    /* Responsive Styles */
    @media (max-width: 768px) {
      .custom-table th,
      .custom-table td {
        font-size: 14px;
        padding: 8px;
      }
    }

    @media (max-width: 480px) {
      .custom-table th,
      .custom-table td {
        font-size: 12px;
        padding: 6px;
      }

      .custom-table-container {
        margin: 10px;
      }
    }

    @media (max-width: 320px) {
      .custom-table th,
      .custom-table td {
        font-size: 10px;
        padding: 4px;
      }
    }
  </style>
</head>
<body>
  <div class="search-container">
    <input
      type="text"
      id="searchInput"
      class="search-input"
      placeholder="Search the table..."
      onkeyup="filterTable()"
    />
  </div>

  <div class="custom-table-container">
    <table class="custom-table" id="dataTable">
      <thead>
        <tr>
          <th>ID</th>
          <th>Name</th>
          <th>Value</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>1</td>
          <td>John Doe</td>
          <td>100</td>
        </tr>
        <tr>
          <td>2</td>
          <td>Jane Smith</td>
          <td>200</td>
        </tr>
        <tr>
          <td>3</td>
          <td>Sam Wilson</td>
          <td>150</td>
        </tr>
        <tr>
          <td>4</td>
          <td>Sara Connor</td>
          <td>180</td>
        </tr>
      </tbody>
    </table>
  </div>

  <script>
    function filterTable() {
      // Get the input field and its value
      const input = document.getElementById("searchInput");
      const filter = input.value.toUpperCase();

      // Get the table and rows
      const table = document.getElementById("dataTable");
      const rows = table.getElementsByTagName("tr");

      // Loop through all rows (skip the header row)
      for (let i = 1; i < rows.length; i++) {
        const row = rows[i];
        let isVisible = false;

        // Check each cell in the row
        const cells = row.getElementsByTagName("td");
        for (let j = 0; j < cells.length; j++) {
          const cell = cells[j];
          if (cell) {
            const text = cell.textContent || cell.innerText;
            if (text.toUpperCase().indexOf(filter) > -1) {
              isVisible = true;
              break;
            }
          }
        }

        // Show or hide the row based on the match
        row.style.display = isVisible ? "" : "none";
      }
    }
  </script>
</body>
</html>

2. Add a JavaScript File for Dynamic Data

Create a JavaScript file that injects data dynamically into the table.


function populateTable(data) {
  const tableBody = document.getElementById("data-table").querySelector("tbody");
  tableBody.innerHTML = ""; // Clear existing rows

  data.forEach(row => {
    const tr = document.createElement("tr");
    Object.values(row).forEach(value => {
      const td = document.createElement("td");
      td.textContent = value;
      tr.appendChild(td);
    });
    tableBody.appendChild(tr);
  });
}

3. Host the HTML/JS Resources

  • Upload the HTML and JavaScript files as web resources in Dataverse.
  • Copy the URLs of the hosted files.

4. Embed HTML in PowerApps

  1. Add an HTML Text Control in your Canvas App.

  2. Embed the URL of your hosted HTML file inside the control.

    Example for the HTMLText property:

    "<iframe src='https://<your-hosted-html-file-url>' width='100%' height='400px' frameborder='0'></iframe>"

5. Pass Data from PowerApps to HTML

To dynamically populate the table:

  1. Add a Power Automate Flow or use an API to pass the data to your web resource.
  2. For example, use the following pattern in your Canvas App:

    Set(dataPayload, JSON(DataSource));
    HtmlTextControl.HtmlText = 
        "<script>" & "populateTable(" & dataPayload & ");" & "</script>";

Key Features

  • Table columns dynamically.
  • Case-insensitive search.
  • Responsive layout for both the search bar and table.

Thanks for visiting!

Power Automate Optimization: Filter Rows vs Trigger Conditions - When and Why to Use Each

Filter Rows vs Trigger Conditions in Power Automate Filter Rows vs Trigger Conditions in Power Automate Why your flow...