Monday, 2 January 2023

How to automatically refresh rollup fields with Power Automate

18:04 Posted by Benitez Here , , , , , , , , No comments

Chances are you've landed on this blog post because you've searched for how to automatically refresh a rollup field. You're in the right place as this is what I cover in this this #WTF episode vlog.

What is a rollup field?

Rollup fields first came out in Dynamics CRM 2015 and there’s a couple of good blog posts by Jukka Niiranen, another long time Microsoft MVP in our community. 

  1. CRM 2015 Rollup Fields: The Gotchas
    • This talks about what a rollup field is.
  2. Analyzing Customer Behavior Data with CRM 2015 Rollup Fields
    • This talks about about how it works underneath the hood.
So what is it?

It’s a field that is an aggregated value of child records in Dynamics 365 and Dataverse. The supported functions are SUM, COUNT, MIN, MAX and AVERAGE.

Now the key thing here as mentioned in Jukka’s blog post is that a rollup field is refreshed every hour, but also is mass calculated 12 hours from when the rollup field was created or updated. This means it’s not performed in real-time. There are some use cases though where you do need it to be refreshed in real-time.

If you've searched on how to automatically refresh rollup fields, most of the answers say to use plugins or even a custom workflow action which requires code. If you know code, no problem.

With cloud flows, it’s straight forward to do and stick around till the end of this post because I’ll tell you whether it’s worth going through this effort with cloud flows.

Disclaimer

Now before we dive into how to refresh rollup fields with cloud flows, this is already blogged about by another person in New Zealand. His name is Boru Wang, go check out his blog. I’m still going to cover it in this episode with some differences to his blog post. I’m going to share two different methods using cloud flows that you can follow.

The CalculateRollupField function in the web API

The way to refresh rollup fields outside of plugins and custom workflow actions is to call the CalculateRollupField function in the Dynamics 365 and Dataverse API. 

Quick break here to understand what is a function in Dynamics 365 and Dataverse,

Functions… represent re-usable operations you can perform using the Web API.. to perform operations that have no side-effects. These functions generally retrieve data and return either a collection or a complex type. Each of these functions has a corresponding message in the organization service.


There’s two parameters for the CalculateRollupField function.


The first one is Target, which is the table the rollup field lives in. This value is the EntitySetName of the table.

The second one is the FieldName, what is the logical name of the rollup field. 

What this Microsoft Learn documentation doesn’t tell you, is that you also need to provide the GUID which is the global unique identifier of the row in the Target table, in order for the rollup field in that row to be refreshed.

This is found in another Microsoft Learn documentation where it explains the syntax to use in your get request.


This basically explains that because the calculaterollupfunction requires a reference to an existing row, you need to provide the GUID for that row in the table the rollup field lives in, hence the Target parameter required.

The GET request to call CalculateRollupField function

This is what the GET request will look like.

Request URI below:
[Organization URI]/api/data/v9.2/CalculateRollupField(Target=@EntitySetName,FieldName=@FieldName)?@EntitySetName={'@odata.id':'EntitySetNameValue(GUID)'}&@FieldName='FieldNameValue'
There’s the two parameters of Target and FieldName, and the GUID reference to the row in the table.

You have full flexibility on what labels you want to use for your parameters. I’m using 
  • EntitySetName for the Target parameter 
  • and FieldName for the FieldName parameter 
Keep it simple and self explanatory.

Putting this altogether, this is what the actual request will look like.

https://orgfedadf52.crm6.dynamics.com/api/data/v9.2/CalculateRollupField(Target=@EntitySetName,FieldName=@FieldName)?@EntitySetName={'@odata.id':'accounts(d465d691-1d87-ed11-81ad-000d3ad0f935)'}&@FieldName= 'ben_numberofcontacts'
So let’s breakdown this GET request for your learning. To keep it simple for this WTF episode I created a rollup field for the Account table that performs the COUNT function to show the number of contacts related to the Account through the Account Name lookup field.


What we need to build our GET request to call the CalculateRollupField function is the following,

1. The Organization URI which is the Dataverse or Dynamics 365 environment organization URL.



2. The EntitySetName of the table. 


You can find this by browsing to make.powerapps.com, view the table in your solution, navigate to Tools under Table properties and select API link to table definition. Then search for EntitySetName and you’ll find it.


3. The GUID of the table row for the rollup field to be refreshed in.



4. The logical name of the rollup field.


This can be found in the Advanced properties of your column that represents the rollup field in make.powerapps.com.


That's it. We’ll be using the in-built HTTP connector of cloud flows to perform the request that calls the CalculateRollupField function.

Scenarios

Now that we know how to form our GET request, the following scenarios is what I cover for the two methods of automatically refreshing a rollup field.


  1. Create a Contact where the Account Name lookup column is populated.
  2. Create a Contact where the Account Name lookup column is not populated.
  3. Modify a Contact by changing the Account Name lookup column from one account to another.
  4. Modify a Contact by clearing the Account Name lookup column so that it no longer related to an Account.
  5. Delete a Contact so that is no longer related to an Account.

Method 1 - single cloud flow

This is what my cloud flow looks like.


I have a trigger of when a Contact row is added, modified or deleted. In the Settings I also have a condition for this flow to only trigger when the Account Name field contains data. I don’t want this flow to trigger if the Account Name is not populated, in other words blank.


In the select columns field, I have the logical name of the Account Name field because I only want this flow to trigger when this field has been updated.


Next, I have an action that references my Environment Variable that retrieves my Azure Key Vault secret value from the Azure portal. This Azure Key Vault secret is used for the authentication in the HTTP request.


The following are a couple of links on how to use Environment Variable for an Azure Key Vault secret value.
The next actions is the HTTP request action where I'm using an application user which requires creating an app registration in Azure portal as the authentication for the API request to the web API. To learn how to do this, check out A Visual Guide To Power Platform Service Principal Setup by Matthew Devaney.

In my HTTP request I have specified the request method which is GET where we’re calling the CalculateRollupField function, followed by the URI that we walked through earlier, and then we have my authentication details. 


I’m using another Environment Variable that contains the Environment URL for my Dataverse to use in the URI. The GUID of the account row/record is inserted as dynamic content, it's retrieved from the trigger response of the Contact, and the Azure Key Vault secret is inserted as a dynamic content value from my Environment Variable. 

The following are references to what each of these authentication fields mean.


1 - Authentication is Active Directory OAuth to reference the app registration in Microsoft Azure portal.

2 - This is the Tenant ID from the app registration in Microsoft Azure portal.

3 - This is the environment URL of the Dataverse (or Dynamics 365) environment.

4 - This is the Client ID from the app registration in Microsoft Azure portal.

5 - The option to select is Secret.

6 - This is the Client Secret from the app registration and we're using the Environment Variable to retrieve the Azure Key Vault secret value.

Scenario 1 - Create a Contact record associated to an Account record

OK now we're ready to run through our scenarios.

This is what the Account looks like prior to the automation being executed, the Account only has 2 contacts and this is what is displayed in the rollup field.

Create a Contact and select an Account, then save.


When we review the run history, the rollup field on the Account is refreshed.

The Number of Contacts rollup field has refreshed and is now correctly displaying 3.

Scenario 2 - Create a Contact record with no association to an Account record

Create a Contact, then save.


The cloud flow is not triggered which is correct due to the condition we specified, which is to only trigger if the Account Name lookup field contains data.

Method 2 - Using Parent flows and Child flow to make the automation reusable

Before we proceed with the remaining three scenarios, I want to switch over to Method 2 which is using multiple cloud flows. Shout out to George Doubinski for suggesting this method which I agreed is much better than Method 1. 

We’re using the concept of Parent and Child flows to make the refresh of a rollup field reusable. In other words if you have more than one rollup field in your Dynamics 365 or Dataverse environment, this method enables the child flow to be called by multiple parent flows. You build it once and it is reused indefinitely. Genius.

To use a child flow, create it within a solution. Select the trigger, manually trigger a flow trigger, and enter in your inputs of EntitySetName, TargetID and FieldName.


These will come from the parent flows so that this child flow can be called multiple times to perform the refresh of rollup fields across tables and rows. 

The next two actions are the same from Method 1 however the difference with the HTTP request is that I’m inserting the output values from the trigger as these will come from the parents flows. 


The last two actions will send a response back to the parent flow that called it.

OK let’s check out the parent flow. Here's the parent flow. 


The trigger is the same as Method 1 and the last action is run child flow, which calls the child flow that refreshes the rollup field based on the parameters provided. These parameters come from the input values that we defined in the trigger of the child flow.

Scenario 3 - Modify a Contact by updating the Account Name field to another Account record

Modify a contact record by updating the Account Name to another account. 


We can now see the parent flow has triggered and see the child flow succeeded. 


Navigate to the account record and the rollup field has automatically refreshed.  The number correctly increments by 1.


The limitation with this scenario? Even though the account for the contact has been updated and the rollup field is refreshed in this account, the rollup field in the previous account the contact was associated to was not updated. I’ll talk more about this towards the end of the blog post.

Scenario 4 - Modify the Contact record by clearing the Account Name field

Clear the Account Name to another account. 


The parent flow does not trigger.

The limitation with this scenario is that even though the account lookup field was cleared, the account rollup field has not refreshed.

Scenario 5 - Delete the Contact record

Delete the contact. The parent flow does not trigger.


The limitation with this scenario is that even though the contact has been deleted, the parent flow does not trigger because of the condition on the trigger. The parent flow will only trigger if the account name lookup column contains data. 

But what if you remove the condition from the trigger?

Even if we removed the condition and deleted the contact record, the parent flow does trigger but it fails.


This is because the parameter value for the TargetID which is the GUID of the account record is missing in the trigger.

Is there a workaround to this limitation in order to refresh the previously associated account record?

So what is the underlying limitation with using cloud flows to refresh rollup fields? Well there’s functionality missing in the Dataverse connector as of when this WTF episode was published. 

This missing jigsaw piece is the pre-image and context of the event. In other words the Dataverse trigger does not know what the previous value was for the account when updating the account name lookup field for the Contact or deleting the Contact. The trigger response only knows of the current value it was updated to. The pre-image and context functionality exists in the traditional SDK of Dynamics 365 and Dataverse but has not yet been exposed as a capability in the Dataverse connector.

Is it worth automatically refreshing rollup fields with cloud flows?

Now for the million dollar question – is it worth using flow to refresh a rollup field in near-time? If your requirement is for rollup fields to be refreshed on create, then yes, worth it ✔️ 

Otherwise until the pre-image and context is supported in the Dataverse connector, I don’t think it’s worth it ❌

Summary

I shared two methods with you in refreshing rollup fields.
  • Method 1 - using a single cloud flow
  • Method 2 - using Parent and Child flows to make the automation reusable
The following are the scenarios I went through with you for Method 1 and Method 2, 


where only scenario number 1 was in my opinion an all round-success for refreshing the rollup field. Scenarios number 3, 4 and 5 were not yielding the behaviour I would have like since pre-image and context is not yet supported.

However when the product team does support it, then definitely Method 1 and Method 2 would work. Method 2 is the one that allows you to reuse the refresh rollup field automation by multiple parent flows.

I hope you learnt something new. Don’t forget to subscribe to my YouTube channel and till next time, Bye!

Friday, 17 December 2021

How to create in-app notifications for model-driven apps the low code way

07:21 Posted by Benitez Here , , , , , , , No comments

In-app notifications, also known as "toast notifications" popped up (see what I did there... 😂) in August 2021 as announced on the Power Apps blog site. This was also blogged about by the following in our #MicrosoftCommunity

Chime Okure

Sara Lagerquist - Microsoft MVP

David Rivard

Phil Topness - Power CAT
So you're probably wondering, "OK E, what else can you share with us in relation to In-App Notifications for Model-Driven apps?" 

Thanks for wondering 🙂 - I'll cover a couple of things in this episode and in my #WTF episode vlog I demo it all.

What I'm covering in this #WTF episode

1. A handy tool

At the end of September fellow Microsoft MVP, Ivan Ficko, went live with an awesome tool in XrmToolBox.
By using Ivan's In-App Notification Builder it makes enabling and developing in-app notifications so much more easier.

Linn Zaw Win another fellow Microsoft MVP covered the tool in his blog post. 

2. Two methods

I'll share how to create two types of notifications in terms of user reach
  1. How to notify a single user
  2. How to notify users from a team - think of notifying multiple users at the same time

The Use Case

As a Claims Assessor

I need to be notified when a claim requires further investigation,

So that I can assign it to myself and review

The end users

Christie Cline - the claims advisor creating the claim in the model-driven app
Alex Weber and Adele Vance - the claims assessors who need to review the claim

The In-App Notification Builder tool in XrmToolBox

First you'll need to install the XrmToolBox and connect to your Dataverse or Dynamics 365 environment. The most up-to-date how-to video I can find is from the lovable Jonas Rapp, watch this video for details on how to set up XrmToolBox.

Once you've installed XrmToolBox, you'll need to add the In-App Notification Builder tool by navigating to the Configuration tab and selecting Tool Library.


Search for In-App Notification Builder, select it and then click Install.


After it as been added,  
  1. Click on Tools.
  2. Use the filter to search for In-App Notification Builder.
  3. It will then appear in the results.

Select the tool and a new tab will load with the In-App Notification Builder tool. The following should load for you.


The remainder of this section will cover the different features in the tool. The first one is App. This will display a list of all the model-driven apps your security role has access to. In my use case, I have a Claims model-driven app and this is the app I'll be using.


Next to it is the Notification Status button. If you see the red circle and white square, it means in-app notifications have not been enabled for the model-driven app. 


Personally, this is one of the great features of Ivan's in-app notification builder tool. Why? If you take a look at the official documentation it provides you with details on how to enable in-app notifications within the model-driven app. To some it may not make sense or would be too much for a person who is not familiar with updating the browser console. If you take a look at step 5 - to someone who has never updated the browser control, this step would not make sense at all.

This is where the Notification Status button comes in handy because with ONE click, it will enable in-app notifications immediately for the model-driven app. When you click on the button you'll be prompted to confirm and once confirmed it will be enabled.


A green circle with a white triangle should now appear.


I'll come back to the Test and Get Code features soon. Let's focus on the features under Data and Preview. Basically anything you update on the left will update the preview of the in-app notification in real-time.


Pretty cool right?!

In the screenshot below is how the features on the left under Data and Actions map to the in-app notification under Preview on the right.


This is what we'll go through next.

The Title field represents the first line in the notification. Make it clear what the in-app notification is about for the user. 


Next we have the Body field. This will display under the Title in the in-app notification. Formatting can be applied to the text, hyperlinks can be added, and an image can also be added. Note, the image is in the form of a URL.


The next two features are Toast Type and Expires In which is explained in the docs.microsoft.com documentation. There are two Toast Type values in the drop down field.
  1. Toast means it will be a 'pop up' experience - think slices of bread in the toaster that pops up when ready to be eaten 😋
  2. Whereas Hidden means it will not 'pop up' but there will be an integer/number value by the bell icon in the model-driven app to indicate there are new notifications for the user.
The Expires In field values represent when the notification should be deleted if not dismissed as per the documentation.

The Icon feature allows you to change the icon that will appear on the in-app notification. There are default icons you can choose from. If you want to display a custom icon, this is supported. You would need to create a web resource in your solution associated to your environment. In this web resource is where you would upload the icon you would be using. 

In my use case I've created a web resource that has an icon which is used for my Claims app so I selected it as an example to show you that custom icons are supported.


The Actions feature allows you to create clickable hyperlinks that will direct the user to either a URL, Record in Dataverse or Dynamics 365, list view, a custom page or even a dashboard in Dataverse or Dynamics 365. In my use case I created two actions.

The first action directs the user to the claim record that was created. 
  • The Text field is what the end user sees as a hyperlink. 
  • The Open As field determines the end user experience of viewing what they are are being directed to.
    • Dialog - Opens in the center dialog.
    • Inline - Default. Opens in the current page.
    • newWindow - Opens in a new browser tab.
  • The Type field represents what the user is being directed to. In my use case I'm directing them to a record.
  • Entity field is displayed if the Type field equals Record. This is where you select the Dataverse or Dynamics 365 Table of the record.
  • Form field is displayed if the Type field equals Record. This is useful if you have several Entity Forms in a table.
  • The Id will be left as-is as this will be defined in Power Automate.
You'll notice that the URL field is disabled as this is defined by the values you've selected in the Entity, Form and ID fields.


I've applied the similar properties to the second action which directs the user to the member record associated to the claim. The only differences here are the Text, Open As, Entity and Form values.


Alright! That's pretty much it for creating your in-app notification. 

But wait - there's more! The other GREAT feature is that you can Test your in-app notification. It's so cool because if you did not use this tool, you'd have to execute your in-app notification each time in order to see it. With the tool, in three clicks you can test it immediately for any user. 

At the top you'll see a Test button, click it! Next select a user you want to test it with. This should be a user you know the credentials for as you'll need to log in as the selected user in order to see the test in-app notification.


Voila - you've now tested an in-app notification using Ivan's In-App Notification Builder tool! High five 🙌🏻

Note: If you click on the hyperlink in the model-driven app, nothing will happen as the ID of the record URL path has not been defined as this will be done in Power Automate. My test was for the visual of the in-app notification.

The Get Code feature is the final one to check out. The first two options are for pro-code developers and the last one is my bestie, Power Automate, which is what will be used to create the low-code in-app notification.


Click on the Power Automate option and a new dialog will appear. 


You copy and paste these values into a cloud flow in Power Automate. This is what I'll cover next as I'll explain the two methods that can be applied to in-app notifications.

1. How to notify a single user

In the Power Automate maker portal, create a new cloud flow. The following is what my cloud flow looks like overall.

The trigger

The connector is Dataverse and the trigger selected is When a new row is added. The Change type is Added (you could have this as added or modified). The Table I've selected is my custom Insurance Claim table. To ensure this notification is only created when an assessor is required for the claim, I've added this as a filter in the Filter rows field of my trigger.


The action - Add a new row

The action that comes next is Add a new row. This will create a new Notification record where we can apply what was created in XrmToolBox. The Notification table is selected, followed by copying as pasting the values from XrmToolBox in the In-App Notification Builder tool.


After copying and pasting, I've added some dynamic content.

  • In the Body I am referencing the Claim Number from the trigger.
  • In the url property of the View Claim action I'm referencing the Insurance Claim record from the trigger.
  • In the url property of the View Member action, I have several references.
    • I'm using an Environment Variable that contains the value of the organisation in the app URL path. Watch this part of the #WTF episode vlog to understand how to get this value.
    • I'm using another Environment Variable that contains the app ID value in the app URL path. Watch this part of the #WTF episode to understand how to get this value.
    • I'm referencing the Member record from the trigger that's associated to the Insurance Claim record
I am also setting the Owner field as the user who created the Claim. This is for demo purposes only. Ideally there would be some logic on how this field is populated.

Side tip

Environment Variables are used so that if you're deploying your solution that contains the cloud flow to a target environment, you simply update the value to the target environment value once the solution has successfully imported. This way your URLs will never break as the organisation and app id value is always unique for each environment. 

The cloud flow in action

I trigger my cloud flow by creating a new claim that requires an assessor as the claims advisor (Christie). The notification is then created and pops up in the Claims model-driven app.

2. How to notify users from a team

This is the second method that enables multiple users to be notified at the same time. The following is what my cloud flow looks like overall.

The trigger is exactly the same so I won't cover that part. In this method however we're using a couple of new actions.

List rows

I'm using the List rows action in the Dataverse connector to retrieve the users associated to a Team through the teammemberships table.


In Dataverse and Dynamics 365 there's the concept of Teams in the security model. A Team is associated to a Business Unit and users are associated to the Team. There are several Team Types available and for the purpose of this demo, I'm using Owner. I go briefly cover this in the #WTF episode vlog here.

Another Environment Variable is used that stores the value of the ID of the team record. This Environment Variable is referenced in the Filter rows field of the action so that only the Claim team is retrieved.

The end result is only users associated to the Claim team will be retrieved.

Apply to each

The next action is using an apply to each to loop through each of the users returned form the list rows action. This is so that a notification for each user can be created.

Add a new row

This is exactly the same as the steps I shared in the 1. How to notify a single user method. For the Owner field, this time the dynamic property of SystemUserId is referenced from the apply to each action.


There is ONE slight difference - a Bonus Tip 😇

Bonus Tip

A handy feature of in-app notifications is that you can @mention a user or a team. Well to be honest, I didn't know if a team could be mentioned but after testing it with Power Automate cloud flows, I can confirm it works!

If you review the docs.microsoft.com documentation, a sample is provided. This is what I followed and applied.


I simply created another Environment Variable that stores the Team ID value which can be used in the Body for the in-app notification. I cover this in my #WTF episode here.

The cloud flow in action

I trigger my cloud flow by creating a new claim that requires an assessor as the claims advisor (Christie). The notification is then created for the claim assessors, Alex and Adele, who are associated to the Claim team record. The notification pops up in the Claims model-driven app for both Alex and Adele.

Summary

By using Ivan's In-App Notification Builder tool in XrmToolBox and combining it with Power Automate cloud flows, you achieve creating low-code in-app notifications for model-driven apps 😁

Ivan's tool is so handy and remember:
  1. In one click you can enable in-app notifications for your model-driven app
  2. You can perform tests to any user to confirm what it will look like for them
Thanks all for tuning in!