Now that we've covered what didn't work for me, we'll start digging into the process that finally worked. At its core, I am listing the records from the D365 backend (whatever it's called this week), and then sorting, grouping, and counting them before formatting them with CSS and sending them out. Don't freak out if this sounds alien, though...I really had no idea how to do most of this when I started, so I'll break it into chunks and then go back and sew it all together.
Carrying on with my food analogies, complex expressions and flows all have a 'core element' or 'main ingredient', then we layer on other flavors to augment, highlight, and sometimes even transform the original piece into something more elegant or consumable. Our main ingredient for this dish is the List Records action for the Common Data Service. We can begin by having a Manual Trigger, and that action just to get started.
XRMToolBox to the Rescue...Again
For our List Records action, we get to capitalize on the same FetchXML that we made in the last article (D365 Reports - Parameters and Filters and Pain). This will ensure that we only get the rows and columns that we want.
From some reading I did after I finished this project, it seems like I don't necessarily need to grab the individual attributes (columns) and can just pull everything in with an "<all-attributes />" (no quotes) since I'm selecting the columns I want later. I'm not certain which is more performant, but find the way that works for you and try it out.
Also, you can experiment with the <filter> section to suit your needs. When I was working on this over the weekend, I changed the operator to "yesterday" and "last-week" just to get some data. This is another thing that FetchXML Builder makes really easy. Great tool!
Planning Ahead
This query will result in some records...or it won't. Meaning, some days (weekends?) will result in an empty array (no activities to populate the report). Rather than send a blank report to everyone, I added a Condition action to check the output of the previous step and send a different email if there was nothing to report.
There are plenty of ways to go about this, but this is the way I chose...again, find the method which works best for you. In the Condition's left side, I used the expression to check for an empty array. If that expression results in a true value, then there are no results, and we send a customized email.
empty(outputs('Get_Activities')?['body/value'])I used a Compose for the message to make it easier and more apparent for the client to adjust, and the same thing for the distribution list. By storing all of the eMail addresses in a Compose at the top of the flow, I have a central place to make edits and I can also be certain I'm sending to the same people if I branch off for something else. I chose to terminate the empty runs with a "Cancelled" so that I could discern from failed and completed (Report = Sent) runs.
Nesting Groups
The report will have two levels of lists we'll want to work through: First, we want a list of unique owners from the day's activities and then we want a list of activities for each owner. If you've already worked with Apply to Each loops, then you probably already have an idea of how this will work. Don't worry if you haven't had a chance to (intentionally) work with these yet, just think of it like a file cabinet. We will sort the folders first (and make sure we don't have any duplicates), and then we'll sort the stuff inside the folders. Easy-peasy.
We will use the technique defined in this article, Group a List for a Summary Using Power Automate, to identify the unique users and then cycle through each to build a sub-table of their respective activities.
Note that this is in the "No" path of our condition, so that if the array from our List Records does contain data (empty = false), it will work through this branch.
The expression inside the Unique Owners Compose action is below for your reference.
union(body('Select_Owners'), body('Select_Owners'))Below, you can see the beginning of the first loop which cycles through each unique owner from our list of records, and then filters that list by that GUID (the unique identifier gibberish for the owner record. We don't have their actual name...yet).
Getting the Details
Since I know I'm going to need the Owner's actual name, the first thing I'll do inside the 'child' Apply to Each loop (below) is use a Get Record to grab the User record from D365 (CDS/Dataverse).
Looking back, it might be more performant to not go looking for the Owner record with every activity on the list. Rather, do that just below the Filter Array in the 'parent' Apply to Each and store that in a Compose or something. We'll use it later as we build the HTML table.
Each Activity can be tied to a different 'parent record' type using the Set Regarding field inside Dynamics 365. I wanted to include the Company Name from that parent record in my report, which meant I needed to get the details from what-could-be a different table for each activity record. To address this, I looked at the _regardingobjectid_value which shows the name of the table where the details would be found, and then ran a Switch action to funnel into an appropriate Get Record for that table type.
To the left, you can see the path if the Set Regarding for the activity was connected to a Contact record.
One thing that I ran into was that not all records required a company name. On Opportunities, for example, I had to first get the Opportunity detail which had the Account GUID. Then, I had to get the Account detail using that, but not every Opportunity had a relationship to an Account. I addressed this by using Coalesce(), which will return the first non-null value in a list of things.
coalesce(outputs('Get_OpportunityAccount_Array')?['body/name'], outputs('Get_Opportunity_Array')?['body/name'])
Because I didn't know which path the Activity record would follow in the Switch, I needed to temporarily store the Set Regarding name (ideally, the Account name) in a centralized container so that I could access it after the switch.
Note that in both examples, the final step of that particular path is to use a Set Variable action, which means at the top of my flow, I'll need to have an Initialize Variable to 'create' that space.
Since the next step also involves variables, I'll dive into more detail with those, and how I used them, in the next article.
Recap
Even though we are only still in the core of the flow, we've covered a lot of ground:
· Using XRMToolbox's FetchXML Builder
· Thinking through alternative paths
· Using empty() to determine if our previous action produced results.
· Nesting Apply to Each loops
· Finding unique values from a list using union()
· Fun with coalesce() - Special note: I like using this one when I've got several phone number or eMail fields for a contact, but I'm not sure which one is being used.
Next week, I'll detail out how I used variables and we'll put together the HTML table and lay in some CSS over top. Not scary at all, trust me.
Get in, make mistakes, learn some things, and have fun! Try. Fail. Learn. Repeat.