Get (Many-to-Many) Associated Entity Records from User Record

[gymcode]

Is it possible to get a list of associated entity records in Users profile?

- (Many-to-Many) Relationship between User and Area entity.

Objective:
- Get list of Areas associated to current logged in user, or
- (not preferable) Get list of Users associated to an Area

----------

Something along the line of 

Filter(Areas, User().FullName in Areas.'AuthorisedUsersSubgrid'.FullName)

where 'AuthorisedUsersSubgrid' is the unique name of the subgrid displaying associated users record in an Area record.

[ivan_apps · Accepted solution]

Yes it's possible to get N:N relationship data, however it is not available directly from the Power Apps Canvas UI.

What you would have to do is create an instant Power Automate flow (with Power Apps v2 trigger) and query the intersect table on a dataverse List Rows action. The intersect table may not be directly visible, but you can always enter the table name directly.

Example table name in the N:N relationship (teamroles) for Teams to Security Roles -

[gymcode]

Hi @ivan_apps , thank you for the suggestion.

1) I have created a relationship between Users -> Areas

2) After which, I tried to create an intersect table because there is no fields during table creation that allows me to specify the Many-to-Many relationship.

Even after creating the new table, I couldn't specify the Many-to-Many relationship anywhere.

3) I then created an Instant Cloud Flow with PowerApps (V2), and a List Row action querying the relationship table name, but an error was encountered.

Request to XRM API failed with error: 'Message: Flow client error returned with status code "BadRequest" and details "{"error":{"code":"InvalidOpenApiFlow","message":"Flow save failed with code 'DynamicOperationRequestClientFailure' and message 'The dynamic operation request to API 'commondataserviceforapps' operation 'GetMetadataForGetEntity' failed with status code 'NotFound'. This may indicate invalid input parameters. Error response: {\"error\":{\"code\":\"EntityNotFound\",\"message\":\"Could not find table with name 'hsl_systemuser_hsl_area'.\"}}'."}}". Code: 0x80060467 InnerError: '.

Failed to retrieve dynamic outputs. As a result, this operation's outputs might not be visible in subsequent actions. Error details: Could not find table with name 'hsl_systemuser_hsl_area'.

I am lost at this current stage and uncertain if the above steps are correctly done.

I seek your advice. Thank you.

[gymcode]

@ivan_apps I have updated my attempt details and screenshot above, for your advice at your availability. Thank you.

[ivan_apps]

When you create a Many-to-many relationship an intersect table is automatically created. In your screenshot #1 you can see the intersect table name is hsl_systemuser_hsl_area. No need to manually create the intersect table after creating a N:N relationship.

Now your flow error does look like an issue - have you published all customizations? Power Automate won’t find the relationship unless it’s published.  There are other things to check but first ensure that you can see the many-to-many relationship on the list of relationships for systemuser or Areas

[gymcode]

Hi @ivan_apps , thank you for your response.

I have Published All Customizations again, but the table not found error is still shown.

In both User and Area entity, I can see the Many-to-Many relationship and both Relationship table names are the same.

1) User

2) Area

Let me check other potential areas for this issue....

[ivan_apps]

Ok I dug into it further - your tables might be different but the method should be the same. 

XrmToolBox has a tool called FetchXML builder, I used that tool to generate the appropriate Power Automate filters as described here: https://dynamicsbackpage.wordpress.com/2019/12/27/using-expand-query-in-power-automate-with-cds/

You can build your query with the tool and connect it directly to your tenant. End result is you get the query exactly as you need to enter it in Power Automate.

Basically instead of querying the intersect table (it was possible in code and SQL in the past) - you will need to use the $expand property in Power Automate.

this is what it would look like, if you are retrieving the Users (systemuser) table for a specific person and returning all associated teams (teammembership table): 

teammembership_association($select=name)

In the case above, I'm just interested in the team name property, so that's the $select statement.

The flow output will then have all your teams, and you can iterate through them as needed.

Hope it helps!

[gymcode]

Hi @ivan_apps , thank you for the assistance. I have attempted to FetchXML Builder via XRMToolBox.

I configured the relationship and linked-entities, 'systemuser' and 'hsl_area', and Result View does show the 2 associated records.

On the step of retrieving Power Automate parameters, it displays the following error:

Cannot find metadata for relation systemuser.systemuserid => hsl_systemuser_hsl_area.systemuserid

Am I missing a configuration or step anywhere while creating the relationship? 😞

[ivan_apps]

Yes you have to add an alias because there are multiple systemuserid columns from the relationships. Add any text on the 'Alias' field and it will prefix all attributes from that joined table with that alias to uniquely identify it. 

Don't forget to do another join to retrieve the properties from hsl_area table, and give that an Alias as well.

In my example below - the alias for the teammembership table was "tm" and the next join to the team table had an alias of "t" - so then retrieving the team name has a column name of "t.name"

[gymcode]

Hi @ivan_apps , thank you for your examples. I followed the steps but encountered an error during Flow Run.
_________________________________________________________________________________________________________________________
Objective
Get all'Areas'.'hsl_name' associated with a systemuser (Many-to-Many relationship)
_________________________________________________________________________________________________________________________
Steps Taken
1) Verified 3 records of 'Areas' associated to 'SystemUser' are shown in Result View and CRM user record.

Table : Areas
Columns : hsl_name
Filter : (hsl_systemuser_hsl_area/any(o1:(o1/systemuserid ne null)))
Expand : hsl_systemuser_hsl_area($select=systemuserid)
Fetch Xml :

<fetch top="50">
  <entity name="hsl_area">
    <attribute name="hsl_name" />
    <link-entity name="hsl_systemuser_hsl_area" from="hsl_areaid" to="hsl_areaid" link-type="inner" alias="Area" intersect="true">
      <filter>
        <condition attribute="systemuserid" operator="eq" value="015fde02-eaad-ee11-a569-000d3ac71097" uitype="hsl_systemuser_hsl_area" />
      </filter>
      <link-entity name="systemuser" from="systemuserid" to="systemuserid" link-type="inner" alias="SystemUser" intersect="true">
        <attribute name="systemuserid" />
      </link-entity>
    </link-entity>
  </entity>
</fetch>

2) Input the values into 'List Rows' action in Power Automate (except FetchXML)

Columns: hsl_name
Filter Rows: systemuserid eq '015fde02-eaad-ee11-a569-000d3ac71097'
Expand Query: hsl_systemuser_hsl_area($select=systemuserid)
_______________________________________________________________________________________________________________________
Results
However, the Test Flow failed with the following error:

Could not find a property named 'systemuserid' on type 'Microsoft.Dynamics.CRM.hsl_area'.

- Raw Inputs:

{
    "host": {
        "connectionReferenceName": "shared_commondataserviceforapps",
        "operationId": "ListRecords"
    },
    "parameters": {
        "entityName": "hsl_areas",
        "$select": "hsl_name",
        "$filter": "systemuserid eq '015fde02-eaad-ee11-a569-000d3ac71097'",
        "$expand": "hsl_systemuser_hsl_area($select=systemuserid)"
    }
}

- Output:

{"statusCode":400,"headers":{"Cache-Control":"no-cache","x-ms-service-request-id":"ff0e81f8-94d0-4525-b06a-e74f505f2aa3,79f98285-d811-48d4-925a-6532056d82d0","Set-Cookie":"ARRAffinity=f69b0981f5a81304924bfd10aa1623f09102b22407fefd8f03be2ac4731febd1eb29bb4b19df1459d2ea781b605f33064b9fea1f144b525b9d7676625c4ffb5408DC1CFC52A7AA48447460282; path=/; secure; HttpOnly,ReqClientId=dd759e0f-207a-4f35-919e-23b0875381cf; expires=Wed, 24-Jan-2074 16:18:42 GMT; path=/; secure; HttpOnly,ARRAffinity=f69b0981f5a81304924bfd10aa1623f09102b22407fefd8f03be2ac4731febd1eb29bb4b19df1459d2ea781b605f33064b9fea1f144b525b9d7676625c4ffb5408DC1CFC52A7AA48447460282; path=/; secure; HttpOnly","Strict-Transport-Security":"max-age=31536000; includeSubDomains","REQ_ID":"79f98285-d811-48d4-925a-6532056d82d0,79f98285-d811-48d4-925a-6532056d82d0","CRM.ServiceId":"framework","AuthActivityId":"dd772241-5fe3-4e45-b734-0950e80f4e89","x-ms-dop-hint":"48","x-ms-ratelimit-time-remaining-xrm-requests":"1,200.00","x-ms-ratelimit-burst-remaining-xrm-requests":"5999","OData-Version":"4.0","X-Source":"1291914420521215112089418157832371953740931791032081821117114173209416768421936,19523217291124581639816413317031501961424723461146359207143321612512039235316448","Public":"OPTIONS,GET,HEAD,POST","Date":"Wed, 24 Jan 2024 16:18:41 GMT","Allow":"OPTIONS,GET,HEAD,POST","Content-Type":"application/json; odata.metadata=full","Expires":"-1","Content-Length":"126"},"body":{"error":{"code":"0x0","message":"Could not find a property named 'systemuserid' on type 'Microsoft.Dynamics.CRM.hsl_area'."}}}

_________________________________________________________________________________________________________________________

 I request your assistance to see which part of the steps above did I made a mistake in.

Thank you very much, I appreciate your patience on handling my lengthy question and responses.