Linn's Power Platform Notebook

Quick Tip: Finding the Right Many-to-Many Relationship When Adding Subgrids

2026-01-19T00:31:00.000+10:00

When you add a subgrid to a model-driven app form and select "Show related records", you'll see a list of tables related to your current form's table.

For one-to-many (1:N) child tables, life is pretty straightforward. The lookup column name appears at the end in brackets, making it easy to distinguish between multiple relationships with the same table.

But many-to-many (N:N) relationships? That's where things get messy. The dropdown just shows the related table name, and if you've got multiple many-to-many relationships with the same table, good luck figuring out which option corresponds to which relationship.

Here's a quick workaround that's saved me plenty of headaches:

1. Click the ellipsis (...) at the top and select "Switch to Classic"

2. In the classic form designer, open the subgrid properties (by selecting the subgrid and click Change Properties)

3. Fire up your browser's Developer Tools (F12 or Ctrl+Shift+I)

4. Click the "select element" button in Developer Tools (or press Ctrl+Shift+C)

5. Point to and select the Entity dropdown in the "Specify the primary data source for this list or chart" section

6. Expand the <select> tag in the HTML inspector

You'll now see all the options with the actual relationship names sitting right next to each entity name. From there, you can work out exactly which relationship name matches the subgrid you want to configure (say, the second Contact in the list) and choose the correct one from the dropdown.

Not the most elegant solution, but it gets the job done when you're dealing with multiple N:N relationships to the same table.

Dataverse Connector's "Return Full Metadata" Parameter in Power Automate

2025-07-10T18:06:00.006+10:00

I stumbled across something interesting today whilst working on a Power Automate cloud flow. There's this "Return Full Metadata" parameter (with key in the code view x-ms-odata-metadata-full) in the Dataverse connector that I'd never really paid attention to before. The official documentation describes it as "The header parameter to customer to opt-in returning full odata metadata in response."

Not exactly the most enlightening explanation, is it? It doesn't tell you what kind of OData metadata we're talking about, and more importantly, it doesn't explain how this parameter actually behaves.

What the Documentation Doesn't Tell You

So naturally, I did what any curious developer would do - I set the parameter to "Yes" and ran the flow to see what would happen. The result? The output JSON looked exactly the same as before.

This got me thinking. After a bit more experimentation, I discovered the real story behind this parameter:

The default value is actually "Yes" - even when you don't specify the parameter at all. The documentation completely fails to mention this crucial detail. If you want to see the difference, you need to set the parameter to "No" to get a slimmer version of the output JSON.

The Real Impact: What You're Actually Getting

When you set "Return Full Metadata" to "No", you'll notice that several OData properties are stripped out from the response:

@odata.type
@odata.id
@odata.editLink
@odata.associationLink
@odata.navigationLink

The good news? All the useful metadata that you actually need for business scenarios remains intact:

@OData.Community.Display.V1.FormattedValue (for displaying formatted values like option set labels and lookup name text)
@Microsoft.Dynamics.CRM.lookuplogicalname (for identifying the logical name of lookup targets especially for polymorphic lookups)
@odata.nextLink (for paging to retrieve the next page of results when the response exceeds the page size limit)
All your actual data fields

The Performance Question

Let's be honest - when was the last time you needed to know the @odata.editLink in your business logic? For 99% of use cases, you're interested in the actual data and maybe some formatting information, not the internal OData plumbing.

Whilst I haven't done extensive performance testing, common sense suggests that smaller JSON payloads should result in:

Faster network transfer
Reduced memory usage
Quicker JSON parsing
Better overall flow performance

This is particularly relevant when you're working with large datasets or when your flows are running frequently.

Thanks to Mark Mulvina for conducting the benchmark testing for the quick time trial comparing performance when using the 'Return Full Metadata' property in the Dataverse 'List Rows' action in Power Automate.

My Recommendation

I believe the default value for "Return Full Metadata" parameter (when not specified) should be "No" rather than "Yes" but I guess Microsoft did not wait to break the existing flows. Most developers don't need the full OData metadata for their business logic, and when they do, they can explicitly opt-in.

For your flows, I'd suggest:

Set "Return Full Metadata" to "No" for all your standard data retrieval operations
Only set it to "Yes" when you specifically need the OData navigation properties
Test your existing flows to see if they actually depend on any of the metadata properties that get stripped out

A Word of Caution

Before you rush off to change all your existing flows, make sure you're not inadvertently using any of the metadata properties that get removed. It's unlikely, but worth checking if you have any complex flows that manipulate the raw JSON output.

And this setting didn't work if your List Rows action is using FetchXML query.

Final Thoughts

This is yet another example of why it's worth digging into the technical details rather than just accepting the surface-level documentation. The "Return Full Metadata" parameter is a simple but effective way to optimise your Dataverse connector calls, but you'd never know it from reading the official docs.

Have you encountered this parameter before, or have you noticed any performance improvements after setting it to "No"? I'd be curious to hear about your experiences in the comments below.

Troubleshooting Copilot Studio Autonomous Agent "Incomplete" Status

2025-07-07T23:21:00.002+10:00

If you've been working with Copilot Studio's autonomous agents, you've probably encountered the frustrating "Incomplete" status. The agent runs, but doesn't complete successfully, leaving you wondering what went wrong. I've dealt with this issue multiple times, and here are two common scenarios with practical solutions.

The Mystery of Zero Completed Steps

The most puzzling scenario is when your agent run shows "Incomplete" status with 0 completed steps. The agent literally does nothing - no actions, no progress, just stops dead in its tracks. All you see is the trigger input and rationale, which doesn't help much.

Finding the Real Error

Here's the trick most people miss: the Activity map view hides the actual error. Click on the "View as" dropdown and switch from "Activity map" to "Transcript". This reveals the Copilot interaction details where the real error message lives.

In my case, I found the Error Code OpenAIMaxTokenLengthExceeded buried in the error message. The agent was choking on too much input data before it could even start processing.

The Root Cause

The culprit was a lengthy JSON payload from the trigger input. When you're passing complex data structures from Power Automate flows or other sources, it's easy to exceed the token limits without realising it. The agent receives this massive input, tries to process it, and immediately fails.

According to Microsoft Support, Copilot Studio typically enforces a practical limit of around 4,096 tokens per interaction. This includes both the input and the output.

As a rough estimate:

1 token ≈ 4 characters of English text.
4,096 tokens ≈ 3,000 words.

The Solution

I went back to the Power Automate flow that triggers the agent and added an AI Prompt step before the agent call. This step cleans up and simplifies the JSON input, removing unnecessary fields and condensing the data structure. Think of it as pre-processing your data to make it digestible for the agent.

The key is being selective about what information the agent actually needs versus what you're passing along "just in case."

The Power Platform Community forum thread has also discussed about similar issue for "An error has occurred. Error code: OpenAIMaxTokenLengthExceeded" error.

When Dataverse Actions Fail Silently

The second scenario involves agents that start processing but fail during Dataverse operations. You'll see some completed steps, but the run ends with "Incomplete" status and a generic HTTP 400 error from the Dataverse connector.

Getting to the Real Problem

The error message connectorRequestFailure The connector 'Microsoft Dataverse' returned an HTTP error with code 400 tells you almost nothing useful. Here's how to dig deeper:

Copy the "Row Item" JSON data from the failed step
Create a new on-demand Power Automate flow
Add the same Dataverse action as in the Copilot agent action (like "Update a row")
For the Table Name, use the trim() function in the custom value hack as mentioned in my blog post instead of selecting from the list - this makes the step dynamic and allows you to paste in the Row Item data
Paste the copied JSON data and test the flow

When you run this test flow, you'll get the actual error message that explains what's wrong with the data.

What I Found

In my situation, the agent was populating a Choice column with a value that wasn't in the valid options list. The agent "thought" it was providing a reasonable value, but Dataverse rejected it because it wasn't one of the predefined choices.

The Fix

I updated the agent's general instructions and knowledge sources to explicitly specify the valid options for that Choice column. Instead of letting the agent guess, I gave it a clear list of acceptable values.

The lesson here is that agents need explicit guidance about data constraints, especially for Choice columns, lookups, and other fields with restricted values.

Prevention is Better Than Debugging

Both scenarios highlight the importance of being specific with your agent instructions. Don't assume the agent will figure out data constraints or input limitations on its own.

For triggers, consider what data the agent actually needs and pre-process complex inputs. For Dataverse operations, document the valid values for constrained fields in your knowledge sources.

The Copilot Studio interface doesn't always surface errors clearly, but with these troubleshooting techniques, you can get to the root cause faster and build more reliable agents. Once the changes are done, you can re-run the same agent run by resubmitting the particular cloud flow run.

PowerFx to Add Row Numbers to Data Tables Directly

2025-05-31T13:16:00.002+10:00

The Problem

I recently hit a common but annoying issue while building a canvas app for Dynamics 365 Project Operations. The requirement was straightforward: display project tasks in the same order they appear in the D365 Project Tasks grid.

When I connected the Project Task table directly to a data table control, the rows showed up in creation order instead of the logical sequence users expect. Tasks that should appear at the top were buried at the bottom.

My first instinct was to sort by the msdyn_displaysequence column in ascending order. This got me closer, but revealed another issue: the D365 grid shows clean row numbers (1, 2, 3...) while msdyn_displaysequence contains decimal values like 1.5, 2.3, 4.7.

This makes perfect sense from a data perspective. When someone inserts a task between positions 1 and 2, the system assigns it 1.5 instead of renumbering every subsequent row. Smart design, but not great for user experience.

The Traditional Approach (And Why I Avoided It)

Most solutions I found online suggest creating a collection with row numbers added.

This works, but it creates a local copy of your data. That means dealing with refresh logic, potential sync issues, and the overhead of maintaining a collection that mirrors your data source.

I wanted a cleaner approach that works directly with the data source.

The Solution: PowerFx Row Numbering

After digging through community solutions, I found inspiration in Matthew's blog post about generating row numbers in collections. I adapted his approach to work directly with data sources instead of creating separate collections.

Here's the formula that solved my problem: (replace with your own dynamic)

ForAll(
    Sequence(
        CountIf(
            Filter(
                'Project Tasks',
                Project.Project = [@ModelDrivenFormIntegration].Item.Project
            ),
            true
        )
    ),
    With(
        {
            sortedTasks: SortByColumns(
                Filter(
                    'Project Tasks',
                    Project.Project = [@ModelDrivenFormIntegration].Item.Project
                ),
                "msdyn_displaysequence",
                SortOrder.Ascending
            )
        },
        Patch(
            Last(FirstN(sortedTasks, Value)),
            { Index: Value }
        )
    )
)

Thanks to Alexandre Perret for suggestion to remove the redundent SortByColumns() and to use the CountIf() instead of CountRows() due to cache issue for Dataverse data source.

And here's the end result.

How It Works

Let me break this down into digestible pieces:

Step 1: Create the sequence

Sequence(CountRows(filtered_and_sorted_tasks))

This generates a sequence from 1 to the total number of tasks. If you have 10 tasks, you get [1, 2, 3, 4, 5, 6, 7, 8, 9, 10].

Step 2: Process each position

ForAll(sequence, ...)

For each number in our sequence, we execute the patching logic.

Step 3: Get the task at each position

Last(FirstN(sortedTasks, Value))

This grabs the Nth task from our sorted list. FirstN(sortedTasks, 3) gets the first 3 tasks, then Last() gives us exactly the 3rd task.

Step 4: Add the row number

Patch(task, { Index: Value })

This adds an Index column to each task record with the appropriate row number.

Real-World Impact

This approach gave me exactly what I needed: tasks displayed in the correct sequence with clean, sequential row numbers that match what users see in D365. The row numbers automatically update when the underlying msdyn_displaysequence changes, and I don't have to manage a separate collection.

The performance is reasonable for typical project sizes (50-200 tasks). For larger datasets, you might want to implement pagination or consider the collection approach after all.

When to Use This Pattern

This technique works well when:

You need row numbers that reflect a specific sort order
You want to avoid managing collections
Your dataset is reasonably sized (under a few hundred records)
The underlying data doesn't change frequently during a single session

Avoid this approach if:

You're working with thousands of records
The data changes constantly
You need complex transformations beyond adding row numbers

The Bottom Line

Sometimes the best solution isn't the most obvious one. Instead of fighting with collections or settling for confusing decimal sequences, a targeted PowerFx formula can solve the problem directly at the data source level.

The key insight here is using Sequence() with ForAll() and Patch() to systematically add computed columns to your data source. Once you understand this pattern, you can apply it to other scenarios where you need to augment data source records with calculated values.

Credit to Matthew Devaney for the original collection-based approach that inspired this solution.

Fixing Date Decrement Issues in Dynamics 365 Project Operations Tasks Grid

2025-05-27T00:14:00.003+10:00

The Problem

A recent update to Dynamics 365 Project Operations introduced a welcome enhancement: custom columns in the tasks grid are now editable. However, this improvement has also introduced an unexpected side effect that's causing headaches for organizations using custom date-only columns.

The issue manifests as a peculiar bug where custom date values in the tasks grid automatically decrement by one day every time the form is refreshed and the tasks grid reloads. For example, a date that should display as 15 March 2025, might show as 14 March 2025 after a refresh, and continue decrementing with each subsequent reload.

Root Cause Analysis

The underlying cause appears to be related to timezone handling during the data conversion process. When the tasks grid loads, the system now converts date values to UTC as part of the enhanced editing functionality. This conversion process doesn't properly account for date-only fields that should remain timezone-agnostic, leading to the unintended day subtraction.

This problem didn't exist in previous versions because custom columns were read-only in the tasks grid. The introduction of editable custom columns has changed how the system processes these date values, exposing this timezone conversion issue.

The Solution

Fortunately, Microsoft has anticipated this type of problem and provided a solution through the 2025 Release Wave 1 features. Here's how to resolve the date decrement issue:

Step 1: Enable Time Zone Independent Dates Feature

First, you'll need to enable the new time zone independent dates feature. This functionality is part of Microsoft's 2025 Release Wave 1 and is specifically designed to address timezone-related issues with project date fields.

You can find detailed information about this feature in Microsoft's official documentation: Add time zone agnostic fields to project and project tasks

Since this feature is currently in public preview, you'll need to manually enable it:

Navigate to Settings > Parameters > open the Project Parameters
Go to the Feature Control menu
Locate and enable the time zone independent dates feature

Step 2: Configure Custom Date Columns

Once the feature is enabled, you'll need to adjust the timezone settings for your affected custom date columns:

Open the custom date column configuration in the solution
Locate the Time zone adjustment setting
Change the setting to Time zone independent

This configuration tells the system to treat these date fields as timezone-agnostic, preventing the unwanted UTC conversion that causes the date decrement.

Prevention and Best Practices

To avoid similar issues in the future, consider these best practices when working with custom date columns in Dynamics 365 Project Operations:

Evaluate Date Field Requirements: Before creating custom date columns, determine whether they truly need timezone awareness or should remain timezone-independent. For most project management scenarios involving deadlines, milestones, or target dates, timezone independence is preferable.

Test After Updates: Always test custom field behavior after major system updates, particularly when Microsoft introduces new editing capabilities or data processing changes.

Monitor Feature Previews: Keep an eye on Microsoft's release waves and preview features that might address common issues in your implementation. The time zone independent dates feature is a perfect example of Microsoft proactively solving problems that organizations commonly encounter.

Documentation: Maintain clear documentation of your custom field configurations, including timezone settings, to facilitate troubleshooting and ensure consistency across your environment.

Conclusion

While the introduction of editable custom columns in the Dynamics 365 Project Operations tasks grid is a valuable enhancement, it's a reminder that system updates can sometimes introduce unexpected behaviors. The date decrement issue demonstrates the importance of thorough testing after updates and staying current with Microsoft's preview features and solutions.

By implementing the time zone independent dates feature and properly configuring your custom date columns, you can resolve this issue and ensure your project dates display accurately regardless of user timezone or form refresh frequency. This fix not only solves the immediate problem but also provides a more robust foundation for date handling in your project management workflows.

Power Automate convertTimeZone using an expression: Decoding the Mystery of Time Zone Names

2025-05-13T23:25:00.000+10:00

Ever wrestled with time zone conversions in your Power Automate flows? You're not alone! Today, we're diving deep into the time zone parameters of the convertTimeZone expression, shedding light on a few nuances that might just save you some headaches.

Like many of you, I recently stumbled upon a fascinating detail, courtesy of my insightful colleague Daniel Kerridge. Did you know that Australia has two seemingly similar time zone names: "E. Australia Standard Time" and "AUS Eastern Standard Time"? The difference? The former refers to Queensland, which happily skips daylight saving time, while the latter applies to other eastern states that do observe it. Mind. Blown.

This revelation highlights a common challenge when working with the convertTimeZone function in Power Automate: figuring out the correct time zone names to populate the sourceTimeZone and destinationTimeZone parameters. The official Power Automate documentation often points to the Azure Logic Apps Reference guide, which in turn directs you to the Microsoft Windows Default Time Zones list.

While this list includes "AUS Eastern Standard Time", you might find yourself scratching your head when looking for other Australian time zones or time zones from different parts of the world. Fear not! There are a couple of handy ways to uncover these elusive time zone values:

Method 1: The Action-Then-Code View Trick

This is a straightforward and visual approach:

Add a "Convert time zone" action to your Power Automate flow.
Use the dropdown menus in the action to select your desired source and destination time zones. Don't worry about configuring the "Base time" or "Format string" fields for now.
Switch to Code view.

In the code view, you'll see the underlying JSON representation of the action. Look for the inputs section, and within that, you'll find the sourceTimeZone and destinationTimeZone parameters populated with the exact values Power Automate uses internally. Voila! You've unearthed the correct time zone names.

Method 2: Cross-Referencing with Azure SQL Managed Instance Documentation

The list of supported time zones in Azure SQL Managed Instance documentation provides a comprehensive list of time zone IDs. We can leverage the options available in the Power Automate "Convert time zone" action and match them against these IDs.

Time zone ID	Time zone display name
Dateline Standard Time	(UTC-12:00) International Date Line West
UTC-11	(UTC-11:00) Coordinated Universal Time-11
Aleutian Standard Time	(UTC-10:00) Aleutian Islands
Hawaiian Standard Time	(UTC-10:00) Hawaii
Marquesas Standard Time	(UTC-09:30) Marquesas Islands
Alaskan Standard Time	(UTC-09:00) Alaska
UTC-09	(UTC-09:00) Coordinated Universal Time-09
Pacific Standard Time (Mexico)	(UTC-08:00) Baja California
UTC-08	(UTC-08:00) Coordinated Universal Time-08
Pacific Standard Time	(UTC-08:00) Pacific Time (US & Canada)
US Mountain Standard Time	(UTC-07:00) Arizona
Mountain Standard Time (Mexico)	(UTC-07:00) Chihuahua, La Paz, Mazatlan
Mountain Standard Time	(UTC-07:00) Mountain Time (US & Canada)
Central America Standard Time	(UTC-06:00) Central America
Central Standard Time	(UTC-06:00) Central Time (US & Canada)
Easter Island Standard Time	(UTC-06:00) Easter Island
Central Standard Time (Mexico)	(UTC-06:00) Guadalajara, Mexico City, Monterrey
Canada Central Standard Time	(UTC-06:00) Saskatchewan
SA Pacific Standard Time	(UTC-05:00) Bogota, Lima, Quito, Rio Branco
Eastern Standard Time (Mexico)	(UTC-05:00) Chetumal
Eastern Standard Time	(UTC-05:00) Eastern Time (US & Canada)
Haiti Standard Time	(UTC-05:00) Haiti
Cuba Standard Time	(UTC-05:00) Havana
US Eastern Standard Time	(UTC-05:00) Indiana (East)
Turks And Caicos Standard Time	(UTC-05:00) Turks and Caicos
Paraguay Standard Time	(UTC-04:00) Asuncion
Atlantic Standard Time	(UTC-04:00) Atlantic Time (Canada)
Venezuela Standard Time	(UTC-04:00) Caracas
Central Brazilian Standard Time	(UTC-04:00) Cuiaba
SA Western Standard Time	(UTC-04:00) Georgetown, La Paz, Manaus, San Juan
Pacific SA Standard Time	(UTC-04:00) Santiago
Newfoundland Standard Time	(UTC-03:30) Newfoundland
Tocantins Standard Time	(UTC-03:00) Araguaina
E. South America Standard Time	(UTC-03:00) Brasilia
SA Eastern Standard Time	(UTC-03:00) Cayenne, Fortaleza
Argentina Standard Time	(UTC-03:00) City of Buenos Aires
Greenland Standard Time	(UTC-03:00) Greenland
Montevideo Standard Time	(UTC-03:00) Montevideo
Magallanes Standard Time	(UTC-03:00) Punta Arenas
Saint Pierre Standard Time	(UTC-03:00) Saint Pierre and Miquelon
Bahia Standard Time	(UTC-03:00) Salvador
UTC-02	(UTC-02:00) Coordinated Universal Time-02
Mid-Atlantic Standard Time	(UTC-02:00) Mid-Atlantic - Old
Azores Standard Time	(UTC-01:00) Azores
Cape Verde Standard Time	(UTC-01:00) Cabo Verde Is.
UTC	(UTC) Coordinated Universal Time
GMT Standard Time	(UTC+00:00) Dublin, Edinburgh, Lisbon, London
Greenwich Standard Time	(UTC+00:00) Monrovia, Reykjavik
W. Europe Standard Time	(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna
Central Europe Standard Time	(UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague
Romance Standard Time	(UTC+01:00) Brussels, Copenhagen, Madrid, Paris
Morocco Standard Time	(UTC+01:00) Casablanca
Sao Tome Standard Time	(UTC+01:00) Sao Tome
Central European Standard Time	(UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb
W. Central Africa Standard Time	(UTC+01:00) West Central Africa
Jordan Standard Time	(UTC+02:00) Amman
GTB Standard Time	(UTC+02:00) Athens, Bucharest
Middle East Standard Time	(UTC+02:00) Beirut
Egypt Standard Time	(UTC+02:00) Cairo
E. Europe Standard Time	(UTC+02:00) Chisinau
Syria Standard Time	(UTC+02:00) Damascus
West Bank Standard Time	(UTC+02:00) Gaza, Hebron
South Africa Standard Time	(UTC+02:00) Harare, Pretoria
FLE Standard Time	(UTC+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius
Israel Standard Time	(UTC+02:00) Jerusalem
Kaliningrad Standard Time	(UTC+02:00) Kaliningrad
Sudan Standard Time	(UTC+02:00) Khartoum
Libya Standard Time	(UTC+02:00) Tripoli
Namibia Standard Time	(UTC+02:00) Windhoek
Arabic Standard Time	(UTC+03:00) Baghdad
Turkey Standard Time	(UTC+03:00) Istanbul
Arab Standard Time	(UTC+03:00) Kuwait, Riyadh
Belarus Standard Time	(UTC+03:00) Minsk
Russian Standard Time	(UTC+03:00) Moscow, St. Petersburg
E. Africa Standard Time	(UTC+03:00) Nairobi
Iran Standard Time	(UTC+03:30) Tehran
Arabian Standard Time	(UTC+04:00) Abu Dhabi, Muscat
Astrakhan Standard Time	(UTC+04:00) Astrakhan, Ulyanovsk
Azerbaijan Standard Time	(UTC+04:00) Baku
Russia Time Zone 3	(UTC+04:00) Izhevsk, Samara
Mauritius Standard Time	(UTC+04:00) Port Louis
Saratov Standard Time	(UTC+04:00) Saratov
Georgian Standard Time	(UTC+04:00) Tbilisi
Volgograd Standard Time	(UTC+04:00) Volgograd
Caucasus Standard Time	(UTC+04:00) Yerevan
Afghanistan Standard Time	(UTC+04:30) Kabul
West Asia Standard Time	(UTC+05:00) Ashgabat, Tashkent
Ekaterinburg Standard Time	(UTC+05:00) Ekaterinburg
Pakistan Standard Time	(UTC+05:00) Islamabad, Karachi
India Standard Time	(UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi
Sri Lanka Standard Time	(UTC+05:30) Sri Jayawardenepura
Nepal Standard Time	(UTC+05:45) Kathmandu
Central Asia Standard Time	(UTC+06:00) Astana
Bangladesh Standard Time	(UTC+06:00) Dhaka
Omsk Standard Time	(UTC+06:00) Omsk
Myanmar Standard Time	(UTC+06:30) Yangon (Rangoon)
SE Asia Standard Time	(UTC+07:00) Bangkok, Hanoi, Jakarta
Altai Standard Time	(UTC+07:00) Barnaul, Gorno-Altaysk
W. Mongolia Standard Time	(UTC+07:00) Hovd
North Asia Standard Time	(UTC+07:00) Krasnoyarsk
N. Central Asia Standard Time	(UTC+07:00) Novosibirsk
Tomsk Standard Time	(UTC+07:00) Tomsk
China Standard Time	(UTC+08:00) Beijing, Chongqing, Hong Kong, Urumqi
North Asia East Standard Time	(UTC+08:00) Irkutsk
Singapore Standard Time	(UTC+08:00) Kuala Lumpur, Singapore
W. Australia Standard Time	(UTC+08:00) Perth
Taipei Standard Time	(UTC+08:00) Taipei
Ulaanbaatar Standard Time	(UTC+08:00) Ulaanbaatar
Aus Central W. Standard Time	(UTC+08:45) Eucla
Transbaikal Standard Time	(UTC+09:00) Chita
Tokyo Standard Time	(UTC+09:00) Osaka, Sapporo, Tokyo
North Korea Standard Time	(UTC+09:00) Pyongyang
Korea Standard Time	(UTC+09:00) Seoul
Yakutsk Standard Time	(UTC+09:00) Yakutsk
Cen. Australia Standard Time	(UTC+09:30) Adelaide
AUS Central Standard Time	(UTC+09:30) Darwin
E. Australia Standard Time	(UTC+10:00) Brisbane
AUS Eastern Standard Time	(UTC+10:00) Canberra, Melbourne, Sydney
West Pacific Standard Time	(UTC+10:00) Guam, Port Moresby
Tasmania Standard Time	(UTC+10:00) Hobart
Vladivostok Standard Time	(UTC+10:00) Vladivostok
Lord Howe Standard Time	(UTC+10:30) Lord Howe Island
Bougainville Standard Time	(UTC+11:00) Bougainville Island
Russia Time Zone 10	(UTC+11:00) Chokurdakh
Magadan Standard Time	(UTC+11:00) Magadan
Norfolk Standard Time	(UTC+11:00) Norfolk Island
Sakhalin Standard Time	(UTC+11:00) Sakhalin
Central Pacific Standard Time	(UTC+11:00) Solomon Is., New Caledonia
Russia Time Zone 11	(UTC+12:00) Anadyr, Petropavlovsk-Kamchatsky
New Zealand Standard Time	(UTC+12:00) Auckland, Wellington
UTC+12	(UTC+12:00) Coordinated Universal Time+12
Fiji Standard Time	(UTC+12:00) Fiji
Kamchatka Standard Time	(UTC+12:00) Petropavlovsk-Kamchatsky - Old
Chatham Islands Standard Time	(UTC+12:45) Chatham Islands
UTC+13	(UTC+13:00) Coordinated Universal Time+13
Tonga Standard Time	(UTC+13:00) Nuku'alofa
Samoa Standard Time	(UTC+13:00) Samoa
Line Islands Standard Time	(UTC+14:00) Kiritimati Island

Error Handling for Project Schedule API in Power Automate

2024-11-15T12:28:00.001+10:00

Calling Project Schedule APIs within Power Automate cloud flows with Perform an Unbound Action can streamline project management tasks, but it requires careful error handling to prevent operational issues.

The Project Schedule APIs in Power Automate rely on an Operation Set to group multiple requests into a single transaction. Typically, the flow structure involves Create Operation Set at the beginning, executing API requests in sequence, and concluding with an Execute Operation Set step.

Problem: Handling Failures Mid-Flow

A common challenge arises if any API call between the Create Operation Set and Execute Operation Set steps fails. This interruption causes the flow to halt before reaching the final Execute Operation Set, leaving the Operation Set in an "Open" status (192350000) in the Operation Set table (msdyn_operationset). When Operation Set records with "Open" status reach x10 for a single user, the following error occurs in the flow because each user can only have a maximum of 10 open OperationSets as per the limitation.

ScheduleAPI-OV-0004:00000000-0000-0000-0000-000000000000: The maximum number of operation set allowed per user is 10.

Solution: Implementing Error Handling with Scopes

To prevent this accumulation of "Open" Operation Sets, consider structuring the flow with a Try-Catch pattern using Scope actions. This approach ensures that if any error, timeout, or skip event occurs during the process, a cleanup step updates the status of the incomplete Operation Set.

Steps for Error Handling

Define the Try Scope: Place all steps between the Create Operation Set and Execute Operation Set actions within a single Scope (e.g., "Try"). This scope will contain all the critical API actions.
Add a Catch Scope: Create another Scope (e.g., "Catch") and configure it to run if the Try Scope fails, times out, or is skipped.
Update Operation Set Status: Inside the Catch Scope, add a Dataverse "Update a Row" action. Set the status of the Operation Set to "Failed" by using the ID output from the Create Operation Set step, which releases the open Operation Set and allows the flow to proceed without reaching the 10-record limit.

Benefits of Using This Error Handling Approach

Implementing error handling through Scopes ensures that Operation Sets are not left in an "Open" status, preventing the flow from encountering the maximum operation limit. This approach is reliable, improves flow resilience, and maintains user productivity by automating the management of operation statuses during errors.

Using these steps, you can ensure your Power Automate flows with Project Schedule APIs remain efficient, effective, and error-resistant.

Missing Dependencies Error for Primary Name Column of Tables Created in Table Visual Designer Experience

2024-11-06T22:18:00.004+10:00

With the recent introduction of the new table visual designer in Power Apps, creating tables has become a streamlined, intuitive process. Accessible through make.powerapps.com, this designer provides an Entity-Relationship Diagram (ERD) experience, allowing users to define tables by simply describing their needs to Copilot. Additionally, it enables importing data directly from sources like SharePoint lists, Excel, or CSV files, making it even easier to build a data structure from existing sources.

First Impressions: Benefits and Limitations

For those familiar with the conventional table creation process, the visual designer can feel like a breath of fresh air. It simplifies and speeds up what used to be a multi-step procedure. However, there are a few limitations to keep in mind:

Lack of Activity Table Creation: Currently, the designer does not support the creation of activity tables for which we still need to go to the Table "advanced properties" experience.
Sync with Global Choice: For choice columns, it is not possible to create a global choice column with an existing set of choices or even a new one and share it among other choice columns created in the same table.
Inability to Create Relationships with Existing Tables: Relationships can only be defined within tables created directly in the visual designer, which could disrupt data integration in complex models with pre-existing tables.

These limitations aside, the visual designer is a valuable tool for speeding up table creation and enhancing the overall experience.

The Missing Dependencies Issue

Workaround Solution

🛈 Note

Since publishing this blog post, it appears that the product team has resolved the issue. I recently tested creating a new table using the visual designer, and it was successfully imported into another environment without any problems.

While testing out this new designer, I encountered a more serious issue that only appeared when moving tables between environments. After creating tables in the visual designer, I exported the solution containing these tables, then attempted to import it into another environment. Unfortunately, I was met with an import error due to “Missing Dependencies.”

Diagnosing the Problem

Upon closer inspection, I found that the missing dependencies were related to columns—specifically, the primary name columns of the tables created in the visual designer. Oddly, these columns were listed as missing even though they were part of the solution. It turns out that the solution.xml file had unintentionally flagged these primary name columns within the <MissingDependencies> tag, likely due to a bug in the designer, which is no longer in the public preview.

Workaround Solution

🛈 Note

One of the readers shared that setting the primary name column as "not required" also resolves the issue.

Here is another workaround solution to update the solution.xml to bypass the “Missing Dependencies” error:

Extract the Solution .zip File: Extract the exported solution .zip file.
Edit the solution.xml File: Open the solution.xml file and look for the <MissingDependency> tags that correspond to the primary name columns of the tables you created in the visual designer.
Remove the Tags for the Affected Columns: Delete the <MissingDependency> tags associated with these primary name columns. This step removes the erroneous dependency listings.
Repackage the Solution: Compress the modified files back into a .zip format.
Import the Solution: Re-import the solution. This time, it should successfully import without the missing dependency errors.

Conclusion

The new table visual designer in Power Apps offers a fantastic user experience, speeding up table creation while integrating Copilot's AI capabilities. However, for those of us working in multi-environment setups, this missing dependencies issue serves as a reminder to thoroughly test and verify our solutions when using new tools and features.

If you face this issue, following the above workaround can save time and prevent the frustration of an interrupted import. It’s a small but valuable fix that can keep your project moving forward smoothly while Microsoft addresses this bug.

Use Dataverse Elastic Tables in Power Automate Cloud Flows

2024-05-29T01:01:00.003+10:00

In this blog post, I will guide you through overcoming common challenges encountered during CRUD operations on elastic tables in Dataverse using Power Automate cloud flow.

While I was working with cloud flow run history in Dataverse, I experienced an unusual issue. In my cloud flow, I use a "List Rows" action to retrieve the data from Flow Run table (which is using Dataverse’s nonrelational database, elastic tables). The query returned the columns as specified in the documentation.

flowruns(«GUID»)

However, when I used the GUID of the flow run to populate the lookup column in the custom table, I encountered the following error.

The HTTP status code of the response was not expected (404).
"error":
{
	"message": "Could not find item '25b17f77-3abb-9596-5cd3-9275652c7f01'.",
	"details":
	[
		{
		"message": "\r\nErrors : [\r\n "Resource Not Found. Learn more: https://aka.ms/cosmosdb-tsg-not-found""
		}
	]
}

I double-checked the GUID, confirming it matches the flowrunid from the output of the "List Rows" action. I attempted to retrieve the Flow Run record using the "Get a row" action with the same GUID, but I encountered the same error.

After searching through forum solutions and Microsoft documentation, I discovered the article "Use Elastic Tables Using Code."

In the article, it explains the information that we need to set the lookup column which is referring to an elastic table.

🛈 Note

When a table record refers to an elastic table record which has partitionid column value set, you must include the partitionid column value of the elastic table record when you set the lookup column of the referencing table. You can do this by including the value as an alternate key.

As described in Partitionid value column on referencing table, when a one-to-many relationship is created and the elastic table is the referenced table, a string column and a lookup column is created on the referencing table. The string column stores the partitionid value of the referenced elastic table record.

You can set both the lookup and the string column values with their respective values by:

Using an alternate key reference to set only the lookup
Setting the two column values together in one update

It's crucial to understand that setting the GUID of the lookup without including the partitionid column value won't trigger an error, but the data won't link correctly. This is why the related columns in the subgrid didn't display properly when I used only the GUID to set the lookup value with the SQL4CDS tool.

⚠ Important

If you choose to use a partitioning strategy for your elastic table, all operations on that table or referring to records in that table MUST specify the partitionid column value to uniquely identify the record. There is no error thrown if partitionid is not specified in the lookup value of referencing table, but the lookup will fail to locate the record when you use it. You must document and enforce this requirement via code reviews to ensure that your data is consistent and partitionid is used appropriately for all the operations.

The article also includes examples of various operations using the Web API, formatted similarly to the parameters in Dataverse actions.

Populating Lookup with GUID and PartitionId

This is the format that I used to populate the lookup to the elastic table with two parameters, one for the GUID and another one for the partitionid (which is similar to populating the lookup with composite alternate key)

flowruns(flowrunid=«GUID»,partitionid='«PartitionId»')

Note: There are single quotes ( ' ) around the PartitionId parameter but not required for the GUID parameter.

Populating Lookup with Alternate Key and PartitionId

You can also use the alternate key to populate the lookup (in this case, name column is a key)

flowruns(name=«AlternateKeyValue»,partitionid='«PartitionId»')

Note: In this case, there are single quotes ( ' ) around both parameters.

Get a Row by ID and PartitionId

Similarly, you can get the data from the elastic table using "Get a Row by ID" action by populating the Partition Id parameter.

Delete a Row by ID and PartitionId

The same logic applies for the "Delete a row" action.

"List rows" action also contains the Partition Id parameter.

Retrieving both GUID and PartitionId of the Lookup

When you retrieve the value of the lookup to the elastic table (e.g. flowrun), you need to retrieve both PartitionId (string column) and GUID (lookup column) on the referencing table because you will need both information in "Get a Row by ID" action (as mentioned above)

The column name of the PartitionId (string column) will be «LookupColumn» + "pid" (e.g. if the lookup column is "lzw_flowrun", PartitionId column will be "lzw_flowrunpid")

In this blog post, I address common challenges with CRUD operations on Dataverse's elastic tables using cloud flows, highlighting the importance of including the partitionid column value to ensure proper data linkage and avoid errors.

Open New Record Form with Pre-populated Fields Using Xrm.Navigation.navigateTo

2024-03-05T00:06:00.004+10:00

This post will explain how you can pre-populate fields with various data types (lookup, partylist, etc.) when a new table form is opened using the Xrm.Navigation.navigateTo method.

There is a navigateTo (Client API reference) in the Microsoft Learn but it lacks a code example for the implementation of data object parameter to set the default values to the fields when a form is opened in the create mode. The documentation simply referred to see Set column values using parameters passed to a form which only contains an example to set column values using parameters passed to a form with Xrm.Navigation.openForm.

Setting column values using parameters with Xrm.Navigation.navigateTo is different from Xrm.Navigation.openForm and it could take some trial and error to get it right without the code example or the documentation of expected parameters. With the code example below, I hope you can easily pre-populated any field using Xrm.Navigation.navigateTo method.

 function OpenNewContact(primaryControl)  
 {  
     let formContext = primaryControl; // primaryControl parameter of the command button is same as formContext and NOT executionContext  
   
     // Define the data object with key-value pairs  
     var data = {  
       
         // Text column  
         "subject": "Do people still use fax anymore?",  
   
         // Number column  
         "numberofpages": 3,  
   
         // Yes/No column  
         "directioncode": true,  
   
         // Date column  
         "scheduledend": new Date("2024-03-02 10:54"), // Replace with your desired date  
   
         // Choice column  
         "prioritycode": 2, // Replace with the option value from your Choice options  
   
         // Lookup column (single)  
         "ownerid": {  
             "entityType": "team",  
             "id": "91a4e4ab-8c67-eb11-a812-000d3a6a350f",  
             "name": "Test Owner Team"  
         },  
   
         // Lookup column (multiple)  
         "to": [  
         {  
             "entityType": "account",  
             "id": "6e060750-ab16-eb11-a812-000d3a6aa8dc",  
             "name": "A. Datum Corporation (sample)"},  
         {  
             "entityType": "contact",  
             "id": "cc139876-a2f3-4308-829e-e5599a566142",  
             "name": "Jim Glynn (sample)"}]  
     };  
   
     // Define the parameters for the form  
     var pageInput = {  
         pageType: "entityrecord",  
         entityName: "fax", // Replace "fax" with your target entity logical name  
         data: data,  
         formId: "fa741227-f468-4864-b2a1-e0379d387e71", // Replace with your desired form ID or remove the parameter if there is only one form  
         createFromEntity: { // This will populate the lookup to the current record e.g. regardingobjectid and other mapping fields  
             "entityType": formContext.data.entity.getEntityName(),  
             "id": formContext.data.entity.getId(),  
             "name": formContext.data.entity.getPrimaryAttributeValue()  
         }  
     };  
   
     // Define the navigation options  
     var navOptions = {  
         target: 2,  
         height: {value: 80, unit:"%"},  
         width: {value: 70, unit:"%"},  
         position: 1  
     };  
   
     // Perform the navigation  
     Xrm.Navigation.navigateTo(pageInput, navOptions).then(  
         function success(result) {  
             Xrm.Navigation.openAlertDialog(  
             {  
                 text: "Record created with ID: " + result.savedEntityReference[0].id + " Name: " + result.savedEntityReference[0].name  
             });  
             // Handle dialog closed  
         },  
         function (error) {  
             Xrm.Navigation.openAlertDialog(  
             {  
                 text: error.message  
             });  
         }  
     );  
 }

Thanks Arjun Musuvathy for the suggestion and motivation to come up with this post.

How to Fix Button in Modern Command Bar Disappearing After Grid Item Selection

2024-02-24T03:48:00.002+10:00

This post provides a resolution for the issue that the modern command button added to the Main Grid, Subgrid View or Associated View doesn't appear as expected after one or more grid items are selected.

In the following scenario, a Clone button is added to the Main Grid of the table before the Delete button using the modern commanding with the Visibility property to Show all the time.

But when one or more row is selected in the grid, the button disappears.

Upon troubleshooting the behaviour with Command Checker Tool, the button is not mark as (hidden) and there is no Enable Rule or Display Rule which evaluates to false. Then, what is causing the button to disappear?

Upon further research, we found that the issue is similar to this KB article in Dynamics 365 Sales. Even though the article is referring to the classic commanding, I believe the issue applies the same to the modern commanding and only item-specific buttons are shown when one or more items are selected by design.

So, how do we make our button an item-specific command in Modern Command Bar? What are the options in the modern commanding which are equivalent to the <SelectionCountRule> or "Mscrm.AnySelection" Enable Rule as mentioned in the KB article?

The option equivalent to <EnableRule Id="Mscrm.AnySelection" /> to make the modern command button as both an item-specific command and a non-item specific command (and display in both cases) is by switching the Visiblility property as "Show on condition from formula" and set the Power Fx visible property as just true. (as mentioned in the forum threads like this and this)

But in my case, the Clone button needs to be visible when one and only grid item is selected. In that case, the Power Fx visible property equivalent to the SelectionCountRule as mentioned in this Classic versus modern visibility rule comparison is as below.

 If(CountRows(Self.Selected.AllItems) = 1,true , false)

In summary, the buttons added to the grid views with Visibility property = Show are treated as non-item specific command and the Visiblility property needs to be changed as "Show on condition from formula" to make it available when one or more grid items are selected.

Keep Your Dataverse Solution Clean and Simple

2024-02-17T07:13:00.003+10:00

Solutions are used to transport table metadata, apps, automations and other components from one environment to another in Dataverse or Power Platform projects. It is important to keep the solutions as simple as possible to improve the solution import time and to avoid unexpected behaviour in the target environment. The rule of thumb is to include only the components that are created/updated in that environment which are unmanaged components or managed components with unmanaged layer (due to being updated in that environment).

Do not Include All ObjectsThe first point is simple. When you add an existing table to the solution, do not check "Include all objects" (unless there is a specific reason or you created that table in another solution and intend to add the whole table in this solution as well). "Include table metadata" can also be left unchecked unless the table metadata (Enable Audit, Duplicate Detection, Quick Create, etc.) is being updated. By clicking the "Select objects", you can add the sub-components that are created/updated.

Do not Add Required ObjectsDo not use "Add required components" to avoid unexpected consequences since it could add a lot more unrelated components to the solution unnecessarily. You can read more details at Alex Shlega's blog post here.

Even if you avoid the actions above, there are some scenarios that the system may add related components to the solution, so be cautious not to let the system automatically add unnecessary components.
Do not Include Missing Required ComponentsFor those who are still using the classic solution explorer for any reason, always choose the "No, do not include required components." option in the "Missing Required Components" dialog. Choosing "Yes, include required components." may cause unexpected consequences as point above. You don't have to worry about that if you only use to modern solution explorer to add the components to the solution.

Adding Relationship and Related TableWhen you create/update a relationship between tables OR create a lookup column (which creates a relationship as a result) in the solution, make sure that the other table of the relationship is in the solution too. If the other table is not in the solution, you can add just the table without any component (as mentioned in the first point of this post).
e.g. if you have a Table A in the solution and you need to

create a lookup column to Table B OR
update a many-to-many relationship to Table B

but the Table B is not part of the solution yet. In that case, before creating/updating those lookup/relationship, it is better to add the Table B to the solution manually with "Include all objects" unchecked.

Otherwise, the system will automatically add that missing table (Table B in the above example) to the solution upon the creation/update of the relationships. When the system automatically add the table to the solution, only a table without any sub-component is added sometimes (which is fine). But sometimes, a table is automatically added with all objects and that is the situation that we want to avoid by adding the table to the solution manually beforehand. You can read more details at Power Platform & Dynamics CRM Tip Of The Day Tip #714.

Adding Form and Related Business RulesWhen the existing form of the table is added, the Business Rules related to that form are automatically added. If those Business Rules are not modified and not required to be in that solution, those should be reviewed and removed after adding the form.
Mobile Offline Profile and Dependent ComponentsThis one is a bit tricky and it needs to be handled depending on the solution management strategy. In short, When a solution containing a Mobile Offline Profile is exported, the system automatically adds the dependent components of the tables (from the Mobile Offline Profile) into the solution without any confirmation. (you can read more details in my blog post). As I mentioned in the blog post, one of the workaround solution is to use a Data Transporter tool or a PowerShell script in the pipeline to deploy the mobile offline profile data.But if the team's policy is to deploy the mobile offline profile as a solution component, the team needs to review the solution segmentation approach. The Mobile Offline Profile needs to be in the same solution as the other components that the offline profile is automatically adding to the solution.

In the scenario above, the Mobile Offline Profile is deployed as a separate managed solution from the managed solution which contains the table metadata (and forms, views, etc.). Due to the system automatically adding the dependent components upon exporting of Mobile Offline Profile solution, there are some table components in the Mobile Offline Profile solution and there is a multiple solution layers for some of those tables. When the new version of Table Solution 1.1 is imported, the changes are not reflected for those overlapped components because the updates are only installed to the Table Solution layer below.
If the table with all assets are added to the solution unnecessarily by the system by any chance, you can fix it by using Solution Table Integrity Manager tool by Tanguy. (as commented by one of the readers below and thanks for the recommendation too, Parvez Ghumra). With that tool, you can select all the faulty tables (e.g. managed tables with all components) and apply the Fix 1 (remove the managed tables and re-add the unmanaged components only) OR Fix 2 (remove the managed tables and re-add the unmanaged components + managed components with active layer which are modified in the current environment).

Do you have any other tips to keep the solutions clean? Or can you think of any other scenario where the system automatically add the components to the solution? Please leave a comment below to share with the other readers.

Refresh Main Form When New Child Row is Added in Subgrid via Quick Create Form in Model-Driven App

2024-02-08T08:35:00.003+10:00

This post will explain how you can refresh all the data of the main form when the user adds a new row to the child table by clicking on the + Add New button from the subgrid and adding one from the Quick Create Form of the child table.

When the user adds a new row to the child table from the subgrid and Save and Close the Quick Create Form, the subgrid (from which the Quick Create Form was triggered) is refreshed. Similarly when the user deletes or deactivate the row, the subgrid is refreshed. But sometimes, there is a synchronous background process for the child table which rolls up/calculate and update the data of the parent row (e.g. total amount, no. of confirmed attendees, etc.). In that case, the updated data on the main form is not reflected immediately because only the subgrid is refreshed.

To refresh the main form when there is any row change to the subgrid, this solution is to add a function to be called when the subgrid control is loaded/refreshed. That function can count the number of rows and refresh the main form accordingly by comparing the row count against the previous count.

First of all, set the global variable for the initial loading of the subgrid.

 var gridRowCount = -1;

OnLoad function of the form, add event handler to the subgrid control, use addOnLoad() method and the function will be called the initial loading of the subgrid and every time the subgrid is refreshed.

 formContext.getControl("Subgrid_Child_Tests").addOnLoad(subgridOnLoad);

In the function, getTotalRecordCount() of the subgrid control to keep it in the global variable for the initial load (when the global variable is -1) and compare it against the row count in subsequent refreshes. When the row count is different from the previous value, data.refresh() to refresh the data in main form .

 this.subgridOnLoad = function (executionContext)  
 {  
      let formContext = executionContext.getFormContext();  
      let subgridTotalRecordCount = formContext.getControl("Subgrid_Child_Tests").getGrid().getTotalRecordCount();  
      if (gridRowCount === -1)  
      {  
           gridRowCount = subgridTotalRecordCount;  
      }  
      else if (gridRowCount !== subgridTotalRecordCount)  
      {  
           gridRowCount = subgridTotalRecordCount;  
           formContext.data.refresh();  
      }  
 }

The solution can be used not only refresh the data in the main form, the triggered function can perform any other operations (such as refreshing another subgrid, open up another quick create/custom page on the side panel, etc.)

The whole script will look something like this and you will need to register the formOnLoad function in the OnLoad event of your form.

 var gridRowCount = -1;  
   
 this.formOnLoad = function (executionContext)   
 {  
      let formContext = executionContext.getFormContext();  
      formContext.getControl("Subgrid_Child_Tests").addOnLoad(subgridOnLoad);  
 }  
   
 this.subgridOnLoad = function (executionContext)  
 {  
      let formContext = executionContext.getFormContext();  
      let subgridTotalRecordCount = formContext.getControl("Subgrid_Child_Tests").getGrid().getTotalRecordCount();  
      if (gridRowCount === -1)  
      {  
           gridRowCount = subgridTotalRecordCount;  
      }  
      else if (gridRowCount !== subgridTotalRecordCount)  
      {  
           gridRowCount = subgridTotalRecordCount;  
           formContext.data.refresh();  
      }  
 }

Note: The idea of this solution started from this forum thread.

How to Embed Audit History on a Form in Model-Driven App

2024-01-29T22:17:00.010+10:00

This post will explain how you can embed an audit history on the form of a table using an iFrame control and JavaScript.

If you do not want the users to click multiple times on Related tab and then Audit History, you can show it on the form directly or under a separate tab by adding an iFrame control and set the URL of the iFrame using JavaScript.

🛈 Note

Please be aware that the current Audit History is one of the certain legacy UI which is enabled as part of hybrid experience in Unified Interface. When Microsoft implement the Audit History in full Unified Interface experience, you will have to re-implement this functionality.

To add an iFrame to the form, select the External website component in the form designer and set the site URL with a dummy URL. Set the name of the iFrame control as well which will be used in the JavaScript below. (for sample code, "IFRAME_audit" is used as the name of an iFrame)

Embedding the Audit History in the IFrame will require JavaScript to populate the entity ID and object type code parameters. WebAPI request will be required to get Object Type Code and the sample JavaScript function below can be called from the Form OnLoad event or TabStateChange event (if an iFrame is placed on a different tab).

 this.formOnLoad = function (executionContext)  
 {  
   setAuditIframeURL(executionContext);  
 }

 this.setAuditIframeURL = function (executionContext)  
 {  
      let formContext = executionContext.getFormContext();  
      Xrm.Utility.getEntityMetadata(formContext.data.entity.getEntityName()).then(  
      function success(entityMetadata)  
      {  
           if (entityMetadata && entityMetadata.ObjectTypeCode)  
           {  
                let objectTypeCode = entityMetadata.ObjectTypeCode;  
                let auditIFrameUrl = location.protocol + "//" + location.hostname + "/userdefined/areas.aspx?oId=" + formContext.data.entity.getId().replace("{", "").replace("}", "") + "&oType=" + objectTypeCode + "&inlineEdit=1&navItemName=Audit History&pagemode=iframe&rof=true&security=852023&tabSet=areaAudit&theme=Outlook15White";  
                formContext.getControl("IFRAME_audit").setSrc(auditIFrameUrl);  
           }  
      },  
      function (error)  
      {  
           Xrm.Navigation.openAlertDialog(  
           {  
                text: error.message  
           });  
      });  
 }

For some reasons, it shows a bit differently (Filter dropdown and Delete Change History button are not visible), so the users will not be able to filter the audit details by a specific column or delete the entire change history for that particular record from the custom audit iFrame.

One of the commenters, Sanket Kumar, also provided another script to show the OOB "Audit History" tab under by default so that the user has one less click to view Audit History without opening the Related tab.

This is the original form with just a General tab.

The following function can be called from the Form OnLoad event. The purpose of the script is focusing the Audit History tab under the Related navigation menu and focusing back to the previously focus.

 this.formOnLoad = function (executionContext)   
 {   
      showAuditTab(executionContext);   
 }


 this.showAuditTab = function (executionContext)   
 {  
      let formContext = executionContext.getFormContext();   
      const navigations = formContext.ui.navigation.items;  
      const tabs = formContext.ui.tabs;  
      const focusedTab = tabs.get().find(tab => tab.getDisplayState() == 'expanded')  
      navigations.get("navAudit").setFocus(); // set focus on audit history tab  
      focusedTab.setFocus(); // set focus on the original default tab  
 }

By doing so, the Audit History tab is visible by default without clicking the Related tab as if the user manually opened the Audit History tab under Related tab and focus back to the General tab.

Hiding a Column Without Removing From a View in Model-Driven App

2023-04-07T13:37:00.003+10:00

Sometimes, there is a requirement to hide a column from a view (main grid or subgrid view) even though the column is required for the view to be functional. Since the column is still required, it cannot be removed using the modern view designer.

One of the examples is to hide the column that is used for sorting because it is showing up as a subtitle in the lookup dropdown because it is the 2nd column of the lookup view. Kailash Ramachandran has blogged about the solution for such a problem by removing the column only in the LayoutXml with the “Select Columns” option in the View Designer tool in XrmToolBox.

Another requirement with a similar problem is to hide the Path column in the associated view of the SharePoint documents in the model-driven app (as commented over here).

If the Path (relativelocation) column is removed from the view in the view designer, the grid control is not able to render anymore.

As in the solution above, I tried to load the Document Associated Grid with the View Designer tool in XrmToolBox but I was getting NullReferenceException error and was unable to save the view. Since the tool was last updated in 2016, there might be some issues with the new properties introduced in the later version of the view schema.

Since View Designer tool is not working well with Document Associated Grid, the only solution is to update the LayoutXml manually. To do so, create a new solution, add the view into the solution, export and extract the solution.zip file and edit the customizations.xml. In the XML file, find the cell element for Path (with name="relativelocation") cell, replace the width attribute with (ishidden="1") as in the screenshot below (or remove the whole element).

That will hide the column from the view but it will still remain in the <fetchxml> so that the grid control will still be functional.

Dynamically List Rows From Any Dataverse Table with FetchXML in Power Automate

2022-11-20T06:28:00.002+10:00

With the List Rows action of Microsoft Dataverse, we can use FetchXML to query the data. For such queries, all we need is to populate the Table name and Fetch Xml Query parameters. But what if we only have the FetchXML? What if we want to query dynamically from different tables based on the FetchXML? One of the use cases would be storing the FetchXML in the config table where the user admin maintains the value and the cloud flow would query the data based on the FetchXML and process the data regardless of which table it is.

The solution is to populate the Table name parameter of the List Rows action dynamically based on the entity name from the FetchXML. The only problem is that the entity name from the FetchXML is the logical name and the Table name parameter accepts the EntitySetName (the plural version of the logical name). Luckily, we can easily retrieve the EntitySetName of the table by another List Rows action query from the Entity table.

In this post, you will also learn how to retrieve the EntitySetName (the plural name) of the table dynamically from the logical name of the table by retrieving the Entities metadata table.

In the sample child flow below, the input parameter is the FetchXML and it returns the number of rows from any table based on the FetchXML as an output parameter to the Power App or parent flow.

The first step is to query the entitysetname column from Entities table where the name is equal to the entity name from the FetchXML. To extract is with the string manipulation, use the first(split(last(split functions as mentioned in the Power Automate Gymnastics Reference Guide and get the value between <entity name=" VALUE ">.

first(split(last(split(triggerBody()['text'], '<entity name="')), '">'))

The next step is to populate the Table name parameter with the value from the List rows above. Since there will only be one row, use first() function and take the entitysetname value. And populate the Fetch Xml Query with the FetchXML from the input parameter.

first(outputs('List_Entities_by_Logical_Name')?['body/value'])?['entitysetname']

Finally, return the row count (length of the array) from the output of the 2nd List rows action.

length(outputs('List_rows_by_FetchXML')?['body/value'])

Calling the child flow from the parent will result in something similar to the one below. The input is the FetchXML (List accounts with the name starting with A) and the result is row_count = 6.

Generate Document From Word Template and Send As Email Attachment Using Cloud Flow

2022-10-31T20:56:00.005+10:00

With Word templates in Dynamics 365 or a model-driven app, users can generate and download standardised documents automatically populated with data from a particular row. There is also a process action called “SetWordTemplate” that will attach the generated document from a word template as a note attachment on a record. The user can download the generated document, attach it to the email and send it out from the system.

In this blog post, you will learn how you can automate the process above by generating a document from a Word Template and sending it as an email attachment using Power Automate cloud flow. This solution can be used when the system needs to automatically send out the pre-populated document on a specific trigger. (e.g., automatically send an in-principle approval letter to the applicant once the case status is changed to Approved)

I presented this solution at the Auckland Power Platform User Group and if you prefer to watch the demo, you can check out the recording on YouTube.

There are other different ways to achieve the same outcome but different ways have different pros/cons. You can use the Word Online (Business) connector to populate a Microsoft Word template but the flow needs to be updated if there is any addition/removal of the fields on the template. By using the Word Templates in Dataverse, the user can manage/modify the Word Template without any change to the cloud flow. You can read more about creating Word Templates in Microsoft Learn.

There are some prerequisites for this solution.

The table needs to be enabled for Business Process Flows (BPF) or else, the table cannot be selected in the Target parameter of SetWordTemplate Action
The table needs to be enabled for Attachments (including notes and files) or else, the SetWordTemplate action will be failed with the “Creating Entity with an invalid parent.” error
The table needs to be enabled for Activity or else, the table cannot be set as Regarding value in an Email

The solution involves x3 components.

Environment variable to store the name of the Word Template
Process action to call the out-of-the-box SetWordTemplate action
Cloud flow to call the action above and send the generated document as an email attachment

1. The environment variable is just Text data type variable to store the name of the Word Template which will be used in the cloud flow to pick up the correct template.

2. Action to call the out-of-the-box SetWordTemplate action is a global (unbound) action with an Entity Reference type mandatory input parameter for Template and one Entity Reference type optional input parameter for each table (from which the documents are going to be autogenerated).

For each table, add a condition to check if the input parameter contains data and populate into the Target parameter of the SetWordTemplate action.

3. These are all the steps included in the cloud flow which calls the custom Set Word Template action and send it as an email attachment.

Trigger and initialise variable
Populate Word template
Get Note attachment content
Send Email with attachment
Delete a Note attachment

1. Trigger and initialise variable

The trigger can be anything depending on your business requirement. In this example, it triggers when the Status Reason (statuscode) is updated to Approved or Rejected. Four variables are initialised in the beginning of the flow. Note ID is to keep the GUID of Note auto-generated by the custom Set Word Template action to delete the Note at the end of the flow. File Name, Mime Type and Document Body variables are used to keep the values of the attachment file from Note and set it to email attachment.

2. Populate Word template

Depending on the value, different Word templates can be used to generate the document. In this example, the Approval Letter Template is used when the Status Reason of the Application is Approved. The GUID of the Document Template can be retrieved by the List Rows action where the name of the template is same as the value in Approval Letter Template environment variable.

To call the custom Set Word Template action from the flow, we need to use the method mentioned in this blog post to set the values of the EntityReference (Lookup) parameters as a JSON object. To make sure that the "Perform an unbound action" step is rendered as a generic steps, I set the Action Name as below.

trim('lzw_SetWordTemplate')

The GUID of the Document Template is the first object from the List rows step above (which can be specified using the expression below. The Application GUID is part of the trigger output.

first(outputs('List_Document_Template_-_Application_Approval_Letter')?['body/value'])?['documenttemplateid']

3. Get Note attachment content

After the custom Set Word Template action, the populated document will be created as a Note attachment related to a record (Application in this example). The next step is to retrieve the content of the Note attachment and set to the variables. The List rows action will return an array, so the values of the first object can be retrieved by using the expressions below.

Note ID

first(outputs('List_Notes_-_Last_Created_for_Contact')?['body/value'])?['annotationid']

File Name

first(outputs('List_Notes_-_Last_Created_for_Contact')?['body/value'])?['filename']

Mime Type

first(outputs('List_Notes_-_Last_Created_for_Contact')?['body/value'])?['mimetype']

Document Body

first(outputs('List_Notes_-_Last_Created_for_Contact')?['body/value'])?['documentbody']

4. Send Email with attachment

The next step is to send an email with the attachment. The detailed information can be found in this blog post. For this example, a new email is created with

Queue (with hardcoded GUID) as a sender
Contact from the Application as a recipient and
the Application itself as Regarding of an Email.

Then, Activity MIME Attachment is created with "null" value for

Attachment (Attachments)
custom text value "email" for Entity
Email GUID from the step above to Item (Email Messages)
Document Body, File Name, Mime Type variables to respective columns

After that, SendEmail bound action is called with the IssueSend = Yes.

5. Delete a Note attachment

The last step is to delete the Note attachment generated by the custom Set Word Template action.

If the requirement is to generate a PDF document (instead of Word document file) based on the Word template and send as an email, you can follow the instructions in this blog post to invoke an HTTP request to {dynamicsURL}/api/data/v9.0/ExportPdfDocument API. The Web API endpoint ExportPdfDocument is part of the Export sales records to PDF functionality, so it might not be available in the Dataverse environment where Dynamics 365 Sales module is not installed.

Formatting Section or Field Label as Bold/Italic in Model-driven App

2022-09-19T20:23:00.004+10:00

In the form designer for Power Apps model-driven app (or Dynamics 365 app), there is no way to add some styling to the labels of the controls (field, tab, section, grid, etc.). But if you want to emphasise some of the words in your label, you can achieve some limited formatting by using the Unicode version of bold/italic characters. You may also add some emojis like emoji Meg.

Search something like "bold text generator" or "italic text generator" in your favourite search engine and enter one of those tools (e.g. yaytext). Enter the text that you want to style and copy the version that you like.

Paste it to the label input of the control in the form designer.

Debugging Pre Operation Plug-In Without Plug-In Trace Log

2022-09-05T22:36:00.008+10:00

Recently, I developed a plugin which triggers on pre-operation stage. As typical pre-operation plug-ins, the logic is to intercept the InputParameters and modify them based on custom logic.

After deploying the plug-in to the Development environment, I just got a generic error as below when I tested by making a change that would trigger the plug-in. There is no arrow icon to expand for the detailed error message or no button to download the error log either.

When I checked the Plug-In Trace Logs, there is no entry with an exception. I updated the plug-in trace log level in System Settings to All but there is no error message in my custom plug-in trace either.

There is no error in the plug-in trace log because the error is in the main operation due to invalid InputParameters after the completion of the custom plug-in and there was no error in the pre-operation plug-in. In that case, debugging using a plug-in profiler would not be helpful either because the error happens after the plug-in triggers.

If you are in a similar situation as me and want to see the error message in the main operation,

Open the DevTools of the browser, Open the Network tab
Clear if there are a lot of Network requests. After that, replicate the issue and click on the request (the red one) with status 403 (Forbidden) or status 500 (Internal Server Error)
In the Response tab, for that particular request
You will see the detailed message of the error. (In my case, setting a wrong object to the PrincipalAccess property 🤦‍♂️)

This method can be used to decipher some of those generic error messages like "An error has occurred.", "You do not have {0} permission to access {1} records.", etc. without more detailed message or a button to download the log file. You can read more about it in my previous blog post.

Cloud Flows with Dataverse Trigger Not Working After Solution Import

2022-08-31T21:57:00.009+10:00

This post will explain how you can identify the root cause of the issue and resolve it if some of the cloud flows with Dataverse trigger are not getting triggered after solution import even though the status of the flow is "On".

After one of the deployments, the test team reported that some of the cloud flows are not getting triggered when a row is added or updated in Dataverse. Most of the flows are working and it only happens for some of the flows, so it is not because of the "Background operations" being disabled in the environment in "Administration Mode". We checked the flow and the status of the flow is also "On". There is no error message about the problem with the flow's trigger. When we turn off the flow and turn it on, the changes in Dataverse start triggering the flow again. Upon further investigation, the affected cloud flows are missing a related row in CallbackRegistration table.

What is CallbackRegistration table?

There is no official documentation about CallbackRegistration table but it stores the trigger information of the async background operations (async classic workflow, cloud flows but not async plugin) such as trigger message (e.g. Create, Update, Delete), Table name, Scope, Filtering Columns, Filtering Expression (e.g. statecode eq 0), Run as User, etc.

In this case, the trigger information of the affected cloud flows in the CallbackRegistration table were deleted during the solution import somehow so that the flows were not getting triggered even though the flow status is "On".

How do I find the affected cloud flows when I experience the same issue?

There were dozens of flows in our system, so we could not possibly test the trigger of all flows manually. I wrote a small SQL query to query all activated cloud flows (the ones with modern flow category in workflow table) and show the related CallbackRegistration data using SQL 4 CDS XrmToolBox Tool. You can filter the flows based on the unique name of the solutions and non-Dataverse trigger flows (e.g. manual flow, recurring flow, flows with other triggers such as SharePoint, Outlook, etc.) can be excluded from the query with the name of the flows.

SELECT friendlyname, workflowid, workflow.name, workflow.statecodename, callbackregistration.name, callbackregistration.entityname, callbackregistration.messagename, SUBSTRING(clientdata, CHARINDEX('triggers', clientdata), 70) from workflow
LEFT OUTER JOIN callbackregistration ON workflowid = callbackregistration.name
LEFT OUTER JOIN solutioncomponent ON workflowid = solutioncomponent.objectid
LEFT OUTER JOIN solution on solutioncomponent.solutionid = solution.solutionid
WHERE category = 5
AND statecode = 1
AND solution.uniquename IN ('Solution1', 'PowerPlatformPlayground')
AND workflow.name NOT IN
(
'Name of the Child Flow with Manaul Trigger 1',
'Name of the Recurring Flow 2',
'Name of the Flow with other trigger 3'
)
ORDER BY callbackregistration.name, workflow.name

In the screenshot below, cloud flows from row 1-13 do not have related CallbackRegistration data. But based on the name of the flow or the trigger value in the clientdata, we can identify that all of the flows are instant flows with manual trigger except the flow in row 11 which has the SharePoint folder. If any of the flows with Dataverse trigger are missing related CallbackRegistration data (NULL value for the columns 5-7), it means those flows are not getting triggered even though the status is "On".

I am getting the following error while running the SQL query above in SQL 4 CDS Tool.

"Conversion failed when converting from a character string to uniqueidentifier."

It happens when the name column of the CallbackRegistration table contains some text value other than the GUID of the workflow row ID. Since CallbackRegistration table is not documented, I am also not sure how it can be joined effectively to the workflow table (where the cloud flows are stored)

If it happens in your environment, query the cloud flows and CallbackRegistration table in a separate query and compare manually in the Excel file.

SELECT friendlyname, workflowid, workflow.name, workflow.statecodename, SUBSTRING(clientdata, CHARINDEX('triggers', clientdata), 70) from workflow
LEFT OUTER JOIN solutioncomponent ON workflowid = solutioncomponent.objectid
LEFT OUTER JOIN solution on solutioncomponent.solutionid = solution.solutionid
WHERE category = 5
AND statecode = 1
AND solution.uniquename IN ('Solution1', 'PowerPlatformPlayground')
AND workflow.name NOT IN
(
'Name of the Child Flow with Manaul Trigger 1',
'Name of the Recurring Flow 2',
'Name of the Flow with other trigger 3'
)
ORDER BY friendlyname, workflow.name

SELECT name, entityname, messagename
FROM callbackregistration
ORDER BY name

I am not allowed to use XrmToolBox in my organisation. Is there any other option?

In that case, you can query the list of cloud flows and CallbackRegistration table with the WebAPI query below.

https://«OrgName».«CrmRegion».dynamics.com/api/data/v9.2/workflows?$select=workflowid,name&$filter=category%20eq%205%20and%20statecode%20eq%200

https://«OrgName».«CrmRegion».dynamics.com/api/data/v9.2/callbackregistrations?$select=name,entityname,message

If I encounter such an issue, shall I report it to Microsoft?

Microsoft product team is aware of some scenarios where while importing the solution issue with Callback Registrations happens and they are working to fix those issues. Some of the scenarios are:

When an environment is copied.
Current Flow owner has limited privileges in the Org where the solution flow is imported.

Other than the scenarios above, if you encounter such an issue after solution import, you can report it to Microsoft Support so that the product team can work together to get the details and find the reason and fix the issue for other scenarios as well.

Until the issue is fixed how can I prevent it from happening in my solution import?

You can add a step in your CI/CD pipeline to turn off all the flows from the solution and turn those back on after solution import.

Are there any other scenarios that could cause this issue?

I have not experienced it personally but there are some incidents where the old CallbackRegistration data of the cloud flows were not getting deleted during the solution import so that the new ones are not created. For that issue, a Microsoft Support ticket was raised and it was resolved by re-importing the solution again after the support team deleted the old CallbackRegistration data.

Summary

If you are experiencing an issue with the Dataverse cloud flows which are not getting triggered after solution import, you can use a SQL query using SQL 4 CDS XrmToolBox Tool to identify the affected cloud flows which are missing a related CallbackRegistration data. You can also use a PowerShell script to turn off/on all the flows after solution import and part of the CI/CD pipeline to prevent this issue too.

Error Handling and Show Error Message from API in Canvas App

2022-06-30T21:49:00.214+10:00

This post will explain how you can handle errors from a connector (or custom connector) and show a proper error message from the response of the API.

Whenever we use the Patch() function to create/update a row in Microsoft Dataverse, call an action of a (custom) connector or call a Power Automate cloud flow, there may be an error due to the server-side validation or an exception being thrown by the underlying API. In such a scenario, you can use the IfError function to handle the error and show a proper message to the user.

Example 1: A new row is created in the Test table in the Microsoft Dataverse with the Patch() function as in the formula below.

Patch(
    Tests,
    Defaults(Tests),
    {Text: "Power Apps Test"}
)

But there is an error from Microsoft Dataverse and the patch operation is failed with the following generic error message. Instead of having such a generic error message from the platform, we need to show a more meaningful error message (which describes more detailed information) to the user.

Example 2: Search action is called from the custom connector with the following formula.

Set(
    varSearchResults,
    CustomConnector.Search(
        {SearchText: "Power Apps"}
    )
)

But there is an error responded by the API that the custom connector is connecting to. If there is any error from the API, we need to show the detailed error message from the API and clear the search result from the previous search (if there is any).

To handle those scenarios, Microsoft announced Error Handling as an experimental features in March 2018. You can read more details in this Error Power Fx Formula reference article.

🛈 Note

IfError function is part of an experimental feature and are subject to change. That function is available only when the Formula-level error management experimental feature in Settings > Upcoming features > Experimental is turned on. (Yes, you read it right. It is still an *experimental* feature after more than 4 years and counting.)

For Example 1 (error in Patch function), we can use the formula below with IfError function to show a custom error message if there is an error in the Patch() function. The IfError function tests the formula in the first parameter. If there is an error, it returns the formula in the second parameter. In our case, there is nothing to return so the Notify() function is used to show a custom error message with NotificationType.Error for 5000 milliseconds (5 seconds).

IfError(
    Patch(
        Tests,
        Defaults(Tests),
        {Text: "Power Apps Test"}
    ),
    Notify(
        "There is an error while creating a row in Test table.",
        NotificationType.Error,
        5000
    ),
    false
);

The following error message will be shown when there is an error in the Patch() function.

Instead of just catching and showing the custom error, you can use the FirstError to get the error details from the API. (Use AllErrors table if there is more than one error. Read this Microsoft Documentation for more details). You can check out the very useful video by Phil Topness on Advanced Connector Error Handling for Canvas Apps which is part of the Power CAT Live series. The video was recorded more than a year ago, so the ErrorInfo used in the video has been changed and FirstError needs to be used instead (That explains why this is still an experimental feature). In the formula below, the response from the Dataverse connector (FirstError.Details.HttpResponse) is used to show as an error message instead of the custom text.

IfError(
    Patch(
        Tests,
        Defaults(Tests),
        {Text: "Power Apps Test"}
    ),
    Notify(
        FirstError.Details.HttpResponse,
        NotificationType.Error,
        5000
    ),
    false
);

The error message from the API is returned and we can now see that the error message is from the real-time workflow. However, there is a lot of other information from the HttpResponse and we need to extract the message part of the response JSON.

Since there is no parse JSON in Power Fx, we can use this method mentioned in the blog post by Inogic to parse JSON from a string using the Match function.

IfError(
    Patch(
        Tests,
        Defaults(Tests),
        {Text: "Power Apps Test"}
    ),
    Notify(
        Match(
            FirstError.Details.HttpResponse,
            "\""code"":""(?<code>[^""]*)"",""message"":""(?<message>[^""]*)"
        ).message,
        NotificationType.Error,
        5000
    ),
    false
);

Now, we can show the error message from the real-time workflow without any other error details.

Other than showing an error notification, there is also another way to handle the error if the data from the API is bound to a gallery or a data table. If the search data from Example 2 is bound to the gallery and there is an error from the custom connector, you might want to clear the search result from the previous search and show a proper message.

Set(varSearchResults, Blank());
Set(varMessage, "Loading...");
Set(
    varSearchResults,
    IfError(
        CustomConnector.Search(
            {SearchText: "Power Apps"}
        ),
        Set(
            varMessage,
            Match(
                FirstError.Details.HttpResponse,
                "\""code"":""(?<code>[^""]*)"",""message"":""(?<message>[^""]*)"
            ).message
        ),
        If (
            (CountRows(varSearchResults) > 0),
            Set(varMessage, ""),
            Set(varMessage, "No data available"});
        );
    );
);

In this example, the gallery is bound to the varSearchResults and varMessage is set to the value of a label in the middle of the gallery. The formula above is triggered OnSelect of the Search button. Before the search, clear the previous search results and show the "Loading..." message to the user by setting the varMessage. If there is any error from the custom connector, the error message will be displayed on the label. If there is no error but there is no data returned from the search API, the label will show a "No data available" message.

Summary

Unlike traditional programming languages, error handling in Power Fx is a bit different and such kinds of errors from API can only be handled by enabling the formula-level error management experimental feature.

How to Set Model-driven App Custom Table Icons in Dataverse

2022-04-30T18:21:00.415+10:00

This post will explain how you can set the icon for a Microsoft Dataverse custom table (entity icon) in the Power Apps maker portal.

When the custom tables are created for Dynamics 365 and model-driven app projects, updating the icon of the table is important because it is automatically assigned a puzzle piece 🧩 icon by default. The icon of the table will be displayed in the sitemap, as the lookup value icon and in the primary image area for the Create form before the data is saved for the very first time. If the table does not have a child relationship (will never be used as a lookup), the table is not added to the sitemap and there is no primary image column, setting the icon is optional because the user will not see the default puzzle piece 🧩 icon or the custom icon anyway.

Microsoft Documentation has the instructions on how to change model-driven app custom table icons but the documentation was last updated on 16 Feb 2022 and the steps are all about "Switch to classic" and updating the icon in the classic solution explorer (the Docs has been updated on 20 Jul 2022 and the steps on updating the icon on the maker portal). Going to the classic solution explorer could be confusing for the user because the Web Client icons (16x16 and 32x32) are no longer valid since the deprecation of the legacy web client and all we need to set is the .svg icon for the Unified Interface.

Here are the steps to set the icon for a table in the Power Apps maker portal.

1. Open Solution

From the Power Apps maker portal, open the unmanaged solution with the tables where icons need to be set (or the solution where the new table will be created).

2a. Table Properties (New Table)

For a new table, expand the "Advanced options" before clicking Save to Choose table image.

2b. Table Properties (Existing Table)

For an existing table, click on the Properties on the table page to open the Properties panel.

(Note: If you are still seeing the old table UI in your maker portal in your region and not as the screenshot above, click on Settings from the top menu)

The table properties panel is the same as the one for a new table. You can expand the "Advanced options" and Choose table image.

3a. Choose Table Image (New web resource)

If the icon image does not exist in the environment, a new web resource can be uploaded by clicking on the "New image web resource" button. Once the new image web resource is uploaded, the table image will be poulated with the newly uploaded image web resource.

3b. Choose Table Image (Existing web resource)

If the icon image is already existing in the environment, the web resource can be searched by the Display Name or selected from the list.

Once the table is saved after selecting the table image, the new icon will be visible in the sitemap, lookup value, primary image, etc.

Tools

Iconator

This is an XrmToolBox tool that allows you to update the table image. The tool also allows the maker to upload the new image web resources. The tool is handy when the table image needs to be set for multiple tables.

Add Image(s) functionality allows the maker to bulk upload the images and add them to the solution.
Load Entities and Images from solution functionality will only show the image web resources from the selected solution which makes it easier to search than scrolling through the whole web resources list.
The preview images are shown so that it is easier for the maker to find and select the right icon

The only drawback of this tool is not being able to specify the folder path for the web resource when the new images are added. For that case, use the WebResources Manager tool to upload the images into a particular folder path and use the Iconator to select the table images.

Icon Gallery

This is an XrmToolBox tool that allows you to preview the image web resources from the environment. This can be useful if the maker wants to preview the image before selecting it as a table image in the maker portal.

Download Icons

Here are a few places where you can generate/download .svg icons.

Icon Menu Builder for Power Apps v2

This is a Power Apps canvas app (available to be downloaded as a managed solution) which allows you to search over 45K free icons from multiple icon sets. You can change the foreground and background colours before downloading it as an .svg file.

Fluent UI Icon Power BI Dashboard

This Power BI Dashboard by Matthew Devaney allows you to search from 2,000 free Power Apps icons in over 30 different colours from the free icon set provided by Microsoft as part of the Fluent UI system. After selecting the icon and the colour, the SVG code can be copied into any of the text editing applications (Notepad++, etc.) and saved as an .svg file.

Metro Studio

This tool has a collection of over 7,000 flat and wireframe icon templates that can be easily customised to create thousands of unique icons. It also supports creating custom icon font packages from selected sets of icons

Flicon

This site allows you to search from Microsoft Office and Fabric Icons and download them as .svg files.

Iconfinder

This site offers both Free and Premium icons. For free icons, just make sure you read the licence agreement of the icon and credit the icon designer when necessary.

Premium Icon Sites

The following icon sites allow the premium members (with a monthly or yearly subscription) to download the unlimited icons in .svg format.

Microsoft PowerPoint

Microsoft PowerPoint has an option to select an image object, select "Save as Picture" from the menu and there is an option to save it as an .svg file. You need to be careful to ensure the combined shapes are roughly square. (Thanks to Alex for the tip)

Do you know any other tool or website to generate/download free icons? Please comment on this post and I will update the post to include those options.

Disable Hyperlink for Lookup Columns in Grid Views of Model-driven Apps

2022-03-30T06:52:00.001+10:00

This post will explain how you can turn off clickable lookups in the grid views of model-driven apps.

Microsoft has recently rolled out the new update for Power Apps Read-Only Grid which has a new property called "Navigation types allowed". This property will allow the system customiser to disable clickable hyperlinks for the lookup column values and only show them as text. According to Microsoft, the correct terminology for clickable hyperlinks is navigation. The clickable hyperlinks for the primary name column of the table which opens the form of the current row (e.g. Full Name in Contact) are called Primary Navigation and clickable hyperlinks for the lookup columns which opens the form of the related tables for those lookup values are called secondary navigation.

🛈 Note

The new update was rolled out to the environments in Australia region last weekend (26 Mar) and it should already be available in the following regions as well. (South America, Canada, India, France, South Africa, Germany, Switzerland, Japan, Asia Pacific, Great Britain, United Arab Emirates)

According to Microsoft's usual deployment schedule, it should be available in Europe region after the weekend of (2 Apr), North America region after the weekend of (9 Apr), China region after the weekend of (16 Apr). Not sure it will be rolled out for GCC but if so, it will be in the weekend of (23 Apr).

With this roll-out, there is a bug that disables clickable hyperlinks in some of the existing views without changing anything in those views. Microsoft product team is working on the fix for it and if you are experiencing that issue and want to apply a quick hotfix before that, follow the steps until Step 4 and keep the "Navigation types allowed" property as a default value "All".

In the grid views, the value of the lookup columns is shown as hyperlinks by default. This is useful when the user wants to open the form of the related table (e.g. Company or Owner in the above screenshot) directly from the Contact view. But sometimes, the users want to disable those hyperlinks for the lookup values because it can be a bit confusing and it opens up a different form when the user wrongly clicks on the lookup values especially if the lookup column is the first column (instead of the primary name column).

1. To change that behaviour, add Custom Controls to the view. This can be done in multiple places.

a. Add Control to the Table to apply for all main views of the table.

b. Add Custom Controls to a view to apply the change only for a particular view.

c. Add Custom Controls to a subgrid to apply the changes only to that subgrid on the form

2. Add Control

3. Select Power Apps Read-Only Grid

4. Select Devices to Show the Custom Control

5. Change Navigation Types Allowed to "Primary only"

6. Save and publish the changes.

After that, the values for the lookup columns are no longer clickable and only the primary navigation (Primary Name column) is shown as a hyperlink to open the selected row.

Summary

Microsoft is investing a lot on Power Apps grid control to support the latest Microsoft accessibility standards and adding up more and more features to allow the makers to configure based on the needs of the users. With this latest update, you can turn off clickable lookups in the grid views of model-driven apps.

Filter by Lookup with retrieveMultipleRecords JavaScript WebApi in Offline Mode

2022-03-07T22:19:00.001+10:00

This post will explain how filtering using the lookup column is different between a normal online scenario and a mobile offline scenario in Xrm.WebApi.retrieveMultipleRecords. You will also learn about how to write a script to support both scenarios.

Before setting up the Dynamics 365 app or Power Apps model-driven app in offline mode, it is important to read through this Microsoft Docs article to understand the capabilities, tips, and limitations. Even if you understand all those capabilities and limitations mentioned in the articles, you will still encounter unexpected issues when you start testing the app in offline mode. This is one of the caveats that you should be aware of.

In the example below, there are x4 Accounts and the requirement is to query the name of the Active Account which has "Sample User A1" (with GUID 033bbb38-611c-ec11-b6e7-000d3acb20df) as the Primary Contact using JavaScript. For that query, only one row of data will be returned because there is no other Account that matches the criteria above.

For such requirements, the filter would be "_primarycontactid_value eq 033bbb38-611c-ec11-b6e7-000d3acb20df". Even thought the logical name of the Primary Contact is primarycontactid, the filter needs to use _{navigation property name}_value as mentioned in this Microsoft Docs article. (read more at this blog post by Ben Gribaudo)

Xrm.WebApi.retrieveMultipleRecords("account", 
"?$select=name,_primarycontactid_value&" + 
"$filter=_primarycontactid_value eq 033bbb38-611c-ec11-b6e7-000d3acb20df and statecode eq 0").then(
	function success(result) 
	{
		Xrm.Navigation.openAlertDialog( { text: "Count: " + result.entities.length + " | " + 
		result.entities[0]["name"] }); 
	}
);

The script above will show the alert as "Count: 1 | Test A" correctly when the script is triggered on the browser (online scenario).

But when the same script is triggered on the mobile with offline mode (with all Accounts and Contacts synced), the count is shown as 4. That means the whole $filter part is ignored and the query returns all rows in the table. After checking out the documentation for Xrm.WebApi.retrieveMultipleRecords, I realised that _<name>_value syntax option is not supported for mobile clients in offline mode. For offline mode, the single-value navigation property name (e.g. primarycontactid) should be used for both retrieving ($select) and filtering ($filter).

Solution 1

To make the script work in both scenarios, the filter syntax needs to be changed by checking if the mobile client is in offline mode or not. For that case, we can use isOffline() method from getGlobalContext.client.

let filterQuery = "$filter=_primarycontactid_value eq 033bbb38-611c-ec11-b6e7-000d3acb20df and ";
if (Xrm.Utility.getGlobalContext().client.isOffline())
{			
	filterQuery = "$filter=primarycontactid eq 033bbb38-611c-ec11-b6e7-000d3acb20df and "
}
Xrm.WebApi.retrieveMultipleRecords("account", 
"?$select=name,_primarycontactid_value&" + filterQuery
filterQuery + "statecode eq 0").then(
	function success(result) 
	{
		Xrm.Navigation.openAlertDialog( { text: "Count: " + result.entities.length + " | " + 
		result.entities[0]["name"]  + " | " + 
		result.entities[0]["_primarycontactid_value"] });
	}
);

Even though the single-value navigation property name (e.g. primarycontactid) is used for retrieving in ($select), _<name>_value syntax needs to be used for reading the value from the response (e.g. result.entities[0]["_primarycontactid_value"] ) 🤷‍♂️

Solution 2

To make the script work in both scenarios without using different $filter syntax, you can query using FetchXML.

let fetchXml = "<fetch>" + 
	"<entity name='account'>" + 
		"<attribute name='name'/>" + 
		"<attribute name='primarycontactid'/>" + 
		"<filter type='and'>" + 
			"<condition attribute='primarycontactid' operator='eq' value='033bbb38-611c-ec11-b6e7-000d3acb20df'/>" + 
			"<condition attribute='statecode' operator='eq' value='0'/>" + 
		"</filter>" + 
	"</entity>" + 
"</fetch>";
Xrm.WebApi.retrieveMultipleRecords("account", "?fetchXml=" + fetchXml).then(
	function success(result)
	{
		Xrm.Navigation.openAlertDialog({ text: "Count: " + result.entities.length + " | " + 
		result.entities[0]["name"]  + " | " + 
		result.entities[0]["_primarycontactid_value"] });
	}
);

The same FetchXML query will work without any issue in both online and offline scenarios.

Summary

Setting up the model-driven app in offline mode is never smooth especially when the configurations and customisations were not initially designed with the mobile offline capabilities and limitations in mind. Reserving enough time is important not only to test the app thoroughly in the offline mode and identify the issues like these but also to update the scripts and make sure the app is working well in both online and offline scenarios.

Managing Public SharePoint Documents for Power Apps Portal and Internal Documents Using Cloud Flow (Part 8 of 8)

2022-02-20T20:55:00.003+10:00

This is the eighth post in a series of Automating SharePoint Integration with Dataverse using Power Automate. You can check out the other posts via these links (Part 1, Part 2, Part 3, Part 4, Part 5, Part 6, Part 7)

Power Apps portal has the capability to upload and display documents to and from SharePoint directly to the Document Library related to a record. But sometimes, there is a requirement to manage two types of documents, one for the public documents visible to the portal user and the private documents which are supposed to be visible only for the internal Dynamics 365/Power Apps users. This can be achieved by uploading the public documents for the portal user to the Document Location which is created first and the private documents for the internal users can be uploaded to the second Document Location. (Special thanks to Gus Gonzalez for sharing this solution by Nicholas Hayduk.)

We can automate this solution by automatically creating the x2 Document Locations with a cloud flow as soon as the record is created. In the Part 1 post, I have explained how to automatically create SharePoint Document Location OnCreate of the record. This is the extension the previous solution with the additional steps to create the second Document Location.

These are all the steps included in the cloud flow for this solution. The following cloud flow is triggered on create of the Contact and creates the public Document Location for the portal user as in the Part 1 post and then, creates the private Document Location for the internal users.

Create Public Document Location
Create Private Document Location

🔗 1. Create Public Document Location

The first part of the cloud flow is almost the same as the the Part 1 post except the SharePoint folder path and Relative URL with "_Public" postfix (as highlighted in the screenshot above).

🔗 2. Create Private Document Location

After creating the first Document Location, delay the flow for 5 seconds to make sure that the timestamp of the second Document Location is different from the first one. The steps for creating a SharePoint folder and Document Location are almost exactly the ones as the public one except the "_Private" postfix (as highlighted in the screenshot).

After creating two Document Locations, the documents uploaded to the first Document Location (red highlighted ones) can be visible on the portal and the documents uploaded to the second Document Location (green highlighted ones) can be visible only to the internal Dynamics 365/Power Apps users.

The user can see the documents from both location by manually selecting "All Locations" from the Document Location menu but that selection cannot be default. When the internal user opens the Documents tab, the Document Location in the first menu item is shown (e.g. Private Documents in this case when the Document Location name is sorted alphabetically).

Summary

When the SharePoint integration from Power Apps portals is enabled, the users can see all the SharePoint documents related to the record but we can upload the documents for internal purpose to the secondary Document Location and keep those hidden from the Portal user.