User Admin PowerApp (Part 3)

The user has been notified that they have a new report, the manager has updated some fields on their user record, they now want to add some roles and teams to allow them to start work.


  • The Scenario (Part 1)
  • Notifying the manager of a new Employee (Part 1)
  • PowerApp to display and update User Data (Part 2)
  • Update Roles and Teams (This Part)

Listing Roles that user hasn’t got

The grid at the bottom of the user screen shows the roles that the user has. If we want to add a role to that list, firstly lets display the roles available to them.

On the form visible event, there is a lot going on. I have already discussed defining the XML to get the list of roles and teams for the user. In this code I also create XML to retrieve the roles the user hasn’t got. Again, FetchXML Builder is your friend.

A looping concatenation isn’t available (well not this week) in PowerApps, so you have to get around this by populating a table with one or more strings then concatenating the output of the table.Using the Set command, create a variable and populate it with a string like below using the ForAll command to repeat the string build for each of the roles the user has got.

        ( ForAll(
            "<condition attribute=""roleid"" operator=""neq"" value='" & 'role2.roleid' & "' />"

This, when you then use the ConCat command creates one string with these as many parts in in as roles the user has.

<condition attribute="roleid" operator="neq" value='0699ab9b-984c-4896-9c8a-38352fdc3c93' />
<condition attribute="roleid" operator="neq" value='d77e2e20-4eac-4fbf-b1a8-5bec6f853ebf' />

The final string combines this string with the other bits that I am interested in, selecting the attributes that is required as well as filtering the roles return to only those in the users BU. If this condition is not put in, all the roles that are associated with all the BUs will be returned. In D365, a role is duplicated for each BU you create, and you need to associate the user with the right role for their BU.

        "<fetch top=""50"" >
                        <entity name=""role"" >
                            <attribute name=""name"" />
                            <attribute name=""roleid"" />
                            <filter type=""and"" >" & Concat(
        ) & "</filter>
                            <condition attribute=""businessunitid"" operator=""eq"" value='" & MyReports.Selected.'Business Unit'.businessunitid & "'/></filter>

This produces an XML that looks like the below.

<fetch top="50" >
                        <entity name="role" >
                            <attribute name="name" />
                            <attribute name="roleid" />
                            <filter type="and" ><condition attribute="roleid" operator="neq" value='0699ab9b-984c-4896-9c8a-38352fdc3c93' /><condition attribute="roleid" operator="neq" value='d77e2e20-4eac-4fbf-b1a8-5bec6f853ebf' /></filter>
                            <condition attribute="businessunitid" operator="eq" value='ec0e9d51-0e91-e911-a822-000d3a34e879'/></filter>

On selecting the plus button at the top of the grid for Roles, another collection is populated using the custom connector, passing the XML to the Roles definition.


Add Role screen

The final screen is a simple gallery, with the name of the role displayed.

All the work is done on the + icon. This calls another custom connector, adding the role to the user. Postman is your friend here. My Postman to insert a role for a user is shown below

Basically, this is posting a body containing a systemuserroles_asscociation to the the webapi, which creates a new one. The user reference is in the URL and the roleId is in the body.

Now, I confess I am not the most offay on the best way to use the Web API. This is one of the reasons I am writing a blog, learning. See that little highlighted $ref? I spent hours trying to get this working and only found that this is required at the end. If you omit the $ref, it returns a positive result. Same if you try to add a role that belongs to a BU that the user doesn’t. Someone tell me where this is logged so next time I can do some troubleshooting rather than trial & error?

The D365 document does mention it, but it is simply missed and it is part of the OData standard for addressing references between entities. As I say, learn everyday.

To convert this to a custom connector definition the GUID of the user account in the URL needs to be replaced with a parameter, note the curly brackets and looks like the below

In PowerApps, after refreshing the connector, there is a new method on our connector. On click of the + in the Add role screen, this is called.

    "/api/data/v9.0/roles(" & roleid & ")"

Even though the second parameter has been hidden and given a default value in the connector, it still prompts to be entered. Not sure why.

The Teams addition is the same logic, but a different connecting entity. The postman for this is below

Deleting a Role

To complete the circle, a manager should be able to delete a role or team that the user belongs to. This is again done via a custom connector, the postman is below

As with the create definition, the 2 guids in the url need to be swapped out so the definition is created like this

Once refreshed in PowerApps, this is connected to the remove button on the Roles grid.


After this call, the grids are refreshed to show the action has taken effect.

Teams works in the same way, using the url like this

And that is it! This app works well to bridge the gap for onboarding users in a busy enterprise, especially as a PowerApp, it is available to users on the go.

Custom Connectors really bring the availability of all the standard Customer Engagement functionality to the “citizen developer”. As enterprise level organisations get on board with PowerApps, small apps that make the employee experience easier will become necessary. This is only an example of how this could be done. Reach out if you would like me to share the app with you.

User Admin PowerApp (Part 2)

So, after notifying the user that there is a new employee in their team, the manager needs to be able to update the data.


  • The Scenario (Part 1)
  • Notifying the manager of a new Employee (Part 1)
  • PowerApp to display and update User Data (This Part)
  • Update Roles and Teams

My Reports

I am not going to go through how to create a PowerApp, there are numerous blogs and pages that step you through this, may I recommend the Microsoft page with it all on?

My PowerApp is pretty straight forward at the start. The first screen is to retrieve all the users that the current logged in user manages, connected to a User data source in the CDS.

Start with a new List form, connect it to your CDS User data set and select a few fields. With the List form, there are several parts that you need to configure, to make the buttons at the top of the screen to work correctly.

Firstly, the Items property needs to be configured to correctly display and filter the data according to the search the user has selected and the sort.

            Manager.internalemailaddress = User().Email

The core to this is the Filter, where the Users (the data source) is filtered to only show those where the email address of the manager of the user is the current users email address.

Filter(Users,  Manager.internalemailaddress = User().Email)

Wrapped around this is functionality to sort and search this grid.

BTW, the picture is the ‘Entity Image’ field in the data set, the picture that a user can upload into D365 against their user account.

User Details

The next screen is a standard edit form, navigated to via the little arrow with this logic

    'Employee Edit',

The form again connects to the User data set, with the item in the edit portion being the significant bit

I have just selected some random fields to display here, where I think it would be appropriate to manage. The Business Unit is a key field for a user, and is a selection and needs a little help to display the right data and update the record appropriately.

Start by adding the field. If you select the datacardvalue control (the drop down itself in the form), notice that a couple of things are wrong.

Business Unit is a parent of User, a relationship Lookup, and PowerApps has decided (not sure why, the default for lookups surely should be the name field, which is how D365 works) to select the Address1_City field to display to the user when they are selecting. Change this to “name”. Change the search as well to match.

Now, the form will display the BU of the user as well as allowing an update of this field.

Displaying Security Roles

At the bottom of the edit form are 2 grids to display the users security roles and their teams. Both operate in the same way.

To get at the roles for a user, you have to use a bit of FetchXML. There is a hidden link table, or a collection value navigation, systemuserroles_association, which links a role with a user.

If you are using any sort of FetchXML, FetchXML Builder (FXB) by Jonas Rapp is essential. It allows a developer to define the exact fields, the connections and filters to form a query for data by looking at the data model. Start with a blank query, select the systemuser entity.

In our scenario, the query should return the roles for one user, the one that has been selected by the manager. Add a filter, then a condition, select the systemuserroles entity, and the systemuserid attribute (field). The operator is equals and the value is the GUID of the user account.

To test the query, paste in the GUID of the user. Another great tool is the Chrome Addon, Level Up for Dynamics by Natraj Yegnaraman allows you to quickly get to the GUID of any record, as well as some other useful tools (please don’t tell an end user about God Mode). The query should retrieve all the fields on the User entity for the chosen user.

Next, select a link-entity, namely the many to many systemuserroles.systemuserid -> systemuserid.

Executing this query will result in a lot of fields been returned, most of no interest to our query, so add some attributes, which limits the fields returned. Only interested in the roleId and the name.

Hit execute this time and this will display the roles that are associated with the user selected.

This is all well and good, but how is this data getting into the PowerApp? A custom connector of course. I have used custom connectors a lot to get at the bits the standard CDS connector can’t in my other posts. I won’t discuss how to create the connector, my post on LUIS covers that. I expand on it fixing the bug on creating attachments in D365.

Creating the Connector Definition

Again, Postman is your friend here. Each of the main entities will accept FetchXML as a parameter, as in this is what is created in Postman

{{WebAPIUrl}}/systemusers?fetchXml=<fetch mapping="logical" count="50" version="1.0">

<entity name="systemuser">

<attribute name="fullname" /><filter type='and'>   
              <condition attribute='systemuserid' operator='eq' value='EBD3707B-6C88-E911-A83E-000D3A323D10' />   
<link-entity name="systemuserroles" from="systemuserid" to="systemuserid">

<link-entity name="role" from="roleid" to="roleid">

<attribute name="name"/><attribute name="roleid"/>





Paste this into a new connector action and PowerApps will convert the fetchXML as a new parameter for the action.

Test this action with a copy and paste of the XML that you generated in FetchXML Builder and the action will return a bit of XML itself

Using the Connector in the app

Add the custom connector as a Data source in the App.

To call the connector, on the OnVisible of the Employee Edit form, generate the XML first. Set a variable to the XML generated from FXB, substituing the current selected user id as appropriate

        "<fetch mapping=""logical"" count=""50"" version=""1.0"">
            <entity name=""systemuser"">
            <attribute name=""fullname"" />
            <filter type='and'>
                <condition attribute='systemuserid' operator='eq' value='" & MyReports.Selected.User & "' />
            <link-entity name=""systemuserroles"" from=""systemuserid"" to=""systemuserid"">
                <link-entity name=""role"" from=""roleid"" to=""roleid"">
                <attribute name=""name""/>
                <attribute name=""roleid""/>

MyReports is the list on the first screen, Selected is the user that the manager has selected. PowerApps converts the User to the GUID of the user (handy). Be careful with the quotes here, I found when pasting, curly versions of the straight quotes came in ( “ rather than “) which took me a while to resolve. Also, ensure all the double quotes are present. FXB provides single quotes and hence you need to double up for PowerApps to infer an actual quote rather than the end of the string.

Update: Thanks to Jonas Rapp for reaching out on Twitter to put me right. In FXB there is a setting to use single quotes rather than double in the rendered XML

This then allows a straight copy / paste into your PowerApp

        secString,"<fetch top='50' >
  <entity name='systemuser' >
    <filter type='and' >
      <condition attribute='systemuserid' operator='eq' value='" & MyReports.Selected.User & "' />
    <link-entity name='systemuserroles' from='systemuserid' to='systemuserid' intersect='true' >
      <link-entity name='role' from='roleid' to='roleid' >
        <attribute name='name' />
        <attribute name='roleid' />

After the string is created, pass this to the custom connector, inserting the return into a collection. This is the collection that is shown in the grid.

    // Get the groups for the selected user

Put a gallery on the form, and use this collection as its items list, and sorted by the role just for usability.

The Teams grid is populated very much the same. The connector is based around the teams entity rather than the system user (in hindsight, I should have based the roles one of roles, rather than user, but achieves the same result, and it is all about learning isn’t it?). The XML looks like this.

        "<fetch top=""50"" >
  <entity name=""team"" >
    <attribute name=""name"" />
      <condition attribute=""isdefault"" operator=""eq"" value=""0"" />
      <condition attribute=""systemmanaged"" operator=""eq"" value=""0"" />

    <link-entity name=""teammembership"" from=""teamid"" to=""teamid"" intersect=""true"" >
      <filter type=""and"" >
        <condition attribute=""systemuserid"" operator=""eq"" value=""" & MyReports.Selected.User & """ />

teammembership is the connecting table.

So, that is enough for now, the app displays users that report to me, I can see their teams and roles. Next post will show how the teams and roles are updated.

User Admin PowerApp (Part 1)

Sorry it has been a while since my last blog post, this scenario has taken a while to get it to the state where I was happy to show it off. Mainly due to my own lack of understanding of the intricacies of the D365 API, but also been busy external to the blog, you know real life.


  • The Scenario (This part)
  • Notifying the manager of a new Employee (This Part)
  • PowerApp to display and update User Data
  • Update Roles and Teams

The Scenario

Big Energy Co is going from strength to strength, presumably because of the innovative solutions using LUIS , Alexa and IFTTT.

The HR department is ramping up recruitment and new teams are being shaped to support all the growth.

One of the criticisms from the managers is that it takes a while for the IT / D365 administrators to get users in the correct teams and security roles so they can be effective in D365.

A clever chap in the management team suggested that they be given an app that would allow a manager to update the roles and teams (and other relevant parts of a user) without resorting to logging into D365 administration. Something they can use wherever they have WiFi or a data connection.

It would also be good to get a notification when they have a new employee, or someone is added to their reports.

The Flow

This flow is quite simple, trigger an email when a user has the Manager field (parentsystemuserid) field updated. O365 will create the user for us (assuming you are in the cloud) and an administrator will still have to update the users manager.

Here, the attribute I am interested on is parentsystemuserid.

Next, just check to see if the manager is actually populated. In a lot of businesses, removing their manager is part of the process on off-boarding an employee, to tidy up selection lists etc.

Then, get the manager user record from D365 so that the email can be sent to it.

Told you it was simple. I am sure that this can have more logic – Do we need an approval step before assigning this user? Do we have to wait for HR to do some work and only activate the user once all the checks are done?

Next, I’ll step through the PowerApps set up to retrieve data from my reports.