Automatically Test & Validate Mailbox for new users in dynamics CRM

 

Schedule a Flow

The flow is scheduled to run automatically once a month.

Initialize Variables

• Page Number: Starts at 1, to keep track of which page of data is being processed

• Record Count: Starts at 0, to count the total records processed.

These steps prepare the workflow to handle data processing efficiently, especially when dealing with paginated data or large datasets.

Manage Spreadsheet Data

    Delete Existing Data from the Spreadsheet

• The flow connects to a spreadsheet stored in One Drive.

• It runs a specific office script to remove existing data from the file.

Excel Template

function main(workbook: ExcelScript.Workbook) {

    // Access the active worksheet

    const sheet = workbook.getActiveWorksheet();

    // Get the table "Table1"

    const table = sheet.getTable("Table1");

    if (table) {

        // Clear existing table rows (but keep headers)

        const rowCount: number = table.getRowCount();

        if (rowCount > 0) {

            table.getRangeBetweenHeaderAndTotal().clear(ExcelScript.ClearApplyTo.contents);

        }

        // Define the new range for the table

        const newRange = sheet.getRange("A1:J2");

        // Resize the table to the new range

        table.resize(newRange);

        console.log("Data cleared from Table1 and the table resized to A1:J2.");

    } else {

        console.log("Table1 not found.");

    }

}

                                        Office Script

 

Wait for Spreadsheet Refresh

• A delay of 30 seconds is added to ensure the spreadsheet has time to refresh and process the changes before any further actions are taken.

   

        This simple process ensures the spreadsheet is cleared and ready for new data input or updates. It’s         an efficient way to maintain clean, updated records in automated workflows

Processing Paginated Data with a Loop

1. Set Up a "Do Until" Loop
   The loop continues until a condition is met. In this case, the flow keeps running until the Page Number variable equals 0, signaling no more pages to fetch.

1. Fetch Rows from the "Mailboxes" Table

   • The action retrieves data from the "Mailboxes" entity using an OData FetchXML query.
   • The query includes parameters like page (set to the current Page Number) and filters to specify conditions (e.g., only active records or certain users/groups).

2. Key Features of the FetchXML Query

   • Retrieves up to 5,000 rows per page.
   • Filters out irrelevant records (e.g., mailbox names with certain patterns or disabled users).
   • Joins with related entities like systemuser and businessunit for more detailed filtering (e.g., users with a specific role or within certain business units).

This workflow is an efficient way to handle large datasets by processing them in chunks and applying custom filtering logic.

Final Steps: Processing and Pagination Logic

1. Check the Row Count
   After retrieving records, the flow evaluates the number of rows fetched.

   • If the row count is greater than 0, there are more records to process.
   • If not, it indicates the end of the data, and the flow stops.

2. Increment Variables (When there are more rows):

   • Page Number: Increases by 1 to move to the next page of data.
   • Record Count: Adds the number of rows fetched to track the total records processed.

3. Print the Data
   The retrieved data is exported to an Excel spreadsheet for further use or reporting.

4. Stop Pagination (When no rows remain):
   The Page Number is set to 0, signaling the loop to exit.

5. This ensures all data is processed efficiently, even for large datasets spread across multiple pages, while keeping track of progress throughout the workflow.

Writing and Retrieving Data from an Excel Spreadsheet

1. Export Data to Excel

   • Data is written to a specified spreadsheet (/33233.xlsx) stored in OneDrive using a custom script.
   • The script processes the data (in JSON format) and inserts it into the spreadsheet.

2. Wait for Spreadsheet Refresh

   • A 30-second delay is introduced to allow the spreadsheet to refresh and save changes.
   • This ensures the data is fully updated before the next action.

3. Retrieve Rows from the Table

• Once the spreadsheet refreshes, the flow retrieves rows from a specific table (Table1) within the file for further processing or analysis.
4. This ensures smooth handling of data updates and retrieval in automated workflows, maintaining data integrity throughout the process.

Processing Spreadsheet Records with Error Handling

1. Loop Through Records

   • The flow iterates over each record retrieved from the Excel table in the spreadsheet.

2. Try Block: Validate and Process Data

   • The Get User Profile (V2) action retrieves user details based on the Email Address field in each record.
   • If the email is valid, this block executes without errors.

3. Catch Block: Handle Validation Failures

• If the Try block encounters an error (e.g., invalid email address or user not found), the flow switches to the Catch block.
• In the Catch block, a row in the spreadsheet (Table1) is updated to reflect the failure (e.g., adding error details).
4. This design ensures data processing continues even if some records fail validation. It logs errors systematically, maintaining data quality and transparency in the workflow.

This Catch Block section of the flow demonstrates how errors are logged systematically into an Excel spreadsheet when validation fails. Here's a concise explanation for blogging purposes:

---

Logging Validation Failures in an Excel Spreadsheet

1. Action: Update a Table

   • The flow updates a specific row in an Excel table (Table1) using the Mailbox ID as the key column to identify the record.

2. Failure Details

   • Specific fields, such as Validation, are updated to reflect the failure.
   • In this example, the Validation column is set to "Failed," providing clear feedback for error tracking.

3. Error Transparency

• By logging validation failures directly in the spreadsheet, it becomes easy for users to review and address issues in the data processing pipeline.

Final Update to the Table Based on Validation Outcome

1. Condition: Check UPN with Email Address

   • The condition evaluates if the User Principal Name (UPN) is equal to the Email Address in the dataset.

2. If True (Validation Success)

   • Action: Updates the corresponding row in the Excel table with the success details.
   • Key columns, such as System User ID, Full Name, and others, are populated with valid data.

3. If False (Validation Fails)

• Action: Updates the table with failure-specific details.
• The Validation column explicitly records the failure reason for visibility.

Update a Mailboxes Row

Purpose:

After successfully validating or failing the user details (based on the UPN and email comparison), the flow updates corresponding records in the Mailboxes entity table.

Key Configurations:

• Table Name: Mailboxes
• Row ID: Uses the Mailbox ID to identify the specific row for updates.
• Fields to Update:
  • Email Address O365 Admin Approval Status: Reflects the approval status.
  • Email Address Status: Indicates whether the email is active or pending.
  • Enabled For (Appointments, Contacts, and Email): Specifies if these functionalities are enabled.
  • Postpone Mailbox Processing Until: A timestamp for deferring mailbox processing.
  • Mailbox Name and Status Reason: Updates these fields based on processing logic.