‘Distribution List’ Emails and Appointments with Power Automate

Following on from my previous post, where we created a custom page that allows you to send emails or appointments to a list of contacts, such as a distribution list. This post will focus on building out the power automate flow that does the magic behind the scenes of the custom page.

Quick recap GIF at the bottom of this page or check out the post: ‘Distribution Lists’ in model-driven apps

Trigger the flow from your custom page

I always prefer to use the ‘Flow Button for mobile’ trigger rather than the Power Apps one (especially not v1), I find them more prone to being temperamental and breaking for no reason. This data is gathered in the custom page, at this point all data is in a string format:

• selecting the lists to include creates a comma separated string of GUIDs for each Distribution List selected (DistListArray_Value)

• the email address of the user who pressed the button, so they can be made owner/sender of the appointment/email (UserEmail_Value)

• where the activity is an email ‘To’, ‘CC’, ‘BCC’ or ‘Required’. (ActivityPartyType_Value)

Compose some useful values for later

I have trust issues using trigger inputs within a flow. I find them easier to use in if first captured in well named compose steps. The name ‘ActivityPartyType’ is way more useful to reference than ‘text_1’. Plus if you need to change any of your trigger inputs, you only need to change them in the trigger and the compose step, nothing else breaks.

The UserEmail_Value and the ActivityPartyType_Value neevr change during the flow so no need to use a variable for these.

UserEmail_Value
Action: Compose
Inputs: triggerBody()['text']

ActivityPartyType_Value
Action: Compose
triggerBody()['text_1']

Initialise variables

But variables are also useful to make numbers behave like numbers (integers), arrays to be arrays and to set the same value differently in conditional steps.

ActivityPartyTypeInt this step calculates which activity party type out recipients will be ( ‘To’, ‘CC’, ‘BCC’ or ‘Required’), full list of activity party codes here.

if(equals(outputs('ActivityPartyType'),'Appointment'),5,if(equals(outputs('ActivityPartyType'),'To'),2,if(equals(outputs('ActivityPartyType'),'Cc'),3,if(equals(outputs('ActivityPartyType'),'Bcc'),4,2))))

ListRowsArray
Action: Initialize variable
Type: Array
This is where we will combine the contacts from the selected distribution lists.

RecordURL
Action: Initialize variable
Type: String
This is the URL we will push back in the flow response with a link to the email/appointment record created

Find Dataverse User ID

We need to ensure the user who pushed the button on the custom page, becomes the sender of the appointment/email. To do that we need to find their Dataverse User GUID.

LookupUserID
Action: List Rows (Dataverse)
Table name: Users
Select columns: systemuserid
Filter rows: internalemailaddress eq '@{outputs('UserEmail')}'
Row count: 1

Find or fail

Since the user accessing the custom page is doing that from within Dataverse I can be pretty sure they do exist as a user but it’s always nice to fail gracefully. The condition checks if any data has come back from the list rows action, if yes we can capture the Dataverse GUID for later use, if not - you have bigger problems.

UserFound
Action: Condition Control
Condition:
empty(body('LookupUserID')?['value'])
is equal to
@{false}

True - UserID
Action: Compose
Inputs: first(outputs('LookupUserID')?['body/value'])?['systemuserid']

False - Terminate
Action: Terminate
Status: Failed

Define who the Activity (appointment or email) is ‘From’

Now we have the Dataverse GUID for the user, we can start to build out the ‘Activity Parties’ involved with the activity. This defines the user as the person the email or appointment will be from.

ActivityPartyArray
Action: Initialize variable
Type: Array
Value:

[{
    "participationtypemask": 1,
    "partyid@odata.bind": "systemusers(@{outputs('UserID')})"
}]

Find the relationship name

For this part, you need to know the relationship name between Contact and your list table, in this case ‘Distribution list’. Yours may be different to the relationship table name/the one I used below. In your solution you can find the correct relationship name to use in the filter below.

Combine the Distribution List members into an array

In this step we will turn a comma separated string of GUIDs for each Distribution List selected, into a list of contacts who belong to those list(s). It appears in this step I had a moment of insanity and used the inputs directly from the trigger, oops.

UniqueListOfContacts (loop)
Action: Apply to Each
Output from previous step: JSON(triggerBody()['text_2'])

GetContacts
Table name: Contacts
Action: List Rows (Dataverse)
Select columns: contactid
Filter rows: (statecode eq 0 and emailaddress1 ne null) and (TABLE-RELATIONSHIP-NAME/any(o1:(o1/DISTRIBUTION-LIST-TABLE-GUID-NAME eq @{items('UniqueListOfContacts')?['Value']})))

Union-MergeRemoveDuplicates - this step allows us to combine the array on contacts generated from the list rows step, with the array on contacts already on the list, rather than appending each contact one by one. It also removes any duplicate values as once contact could belong to many lists.
Action: Compose
Inputs: union(outputs('GetContacts')?['body/value'],variables('ListRowsArray'))

SetListArray - you cannot use the variable value within the setting on variable, hence using compose first then setting the variable (not appending) with the new combined list
Action: Set Variable
Value: outputs('Union-MergeRemoveDuplicates')

Append Contacts as ‘Activity Parties’

Now we need to turn the list of contacts, into ‘Activity Parties’ to be added to the email or activity appointment, in this case we will need to append one by one but there are no duplicates so it’s the most efficient we can do here.

AppendActivityParties (loop)
Action: Apply to Each
Output from previous step: variables('ListRowsArray')

AppendActivityPartArray
Action: Append to array variable
Value:

{
  "participationtypemask": @{variables('ActivityPartyTypeInt')},
  "partyid@odata.bind": "contacts(@{items('AppendActivityParties')?['contactid']})"
}

Create appointment or email?

Finally, the hard work is done, now we just need to pull it together by checking if we need to create an email or an appointment. In this case its either Appointment or Email but you could scale this out to more activities and use a Switch Control instead.

Appointment
Action: Condition Control
Condition: outputs('ActivityPartyType') is equal to Appointment

True - Create Appointment

CreateAppointment

Action: Add a new row (Dataverse)
Table name: Appointments
End Time: @{addHours(utcNow(),1)}
Start Time: @{utcNow()}
Subject: @{null}
Owner: systemusers/@{outputs('UserID')}
Activity Parties: @{variables('ActivityPartyArray')}

AppointmentRecordURL
Action: Set Variable
Value: https://@{uriHost(outputs('CreateEmail')?['body/@odata.id'])}/main.aspx?pagetype=entityrecord&etn=email&id=@{outputs('CreateEmail')?['body/activityid']}

False - Create Email

CreateEmail
Action: Add a new row (Dataverse)
Table name: Email Messages
Owner: systemusers/@{outputs('UserID')}
Activity Parties: @{variables('ActivityPartyArray')}systemusers/@{outputs('UserID')}

EmailRecordURL
Action: Set Variable
Value: https://@{uriHost(outputs('CreateEmail')?['body/@odata.id'])}/main.aspx?pagetype=entityrecord&etn=email&id=@{outputs('CreateEmail')?['body/activityid']}

Send the Record URL back to the custom page

Now we use the respond action to send the URL of the created email or appointment back to the user to open, draft and send. Magic!

The end

Hopefully by now you have been able to put together the custom page in this blog post and this flow, to be able to manage your Distribution Lists in your model driven app. If nothing else I hope it got your creative juices flowing on other ways you can build similar things like this that would otherwise be custom code, a bad UX or just a no can do.

P.S In case I lost you anywhere along the way you can find below an image of the full flow end to end. I like to use parallel actions where possible in the hope of it being a little bit quicker but it’s definitely not essential.