How to get N:1 relationship data from Dynamics CRM?

[CharlotteWest]

I have an N:1 relationship in our CRM (between the Venues and Programmes custom entities) where each Venue has a subgrid containing related Programme records. All I want is a list containing all the Venues and their related Programmes. I believe Power Automate is the way to do this, but I'm not sure how to get it to work. 

I've got as far as finding the List Records function, but Programmes doesn't show in the list of related entities in the Expand Query box. 

I'm relatively new to both Dynamics and Power Automate so any help would be very much appreciated! Thanks!

[v-alzhan-msft · Accepted solution]

Hi @CharlotteWest ,

The items in the Venues entity should have the corresponding items in the Programmes custom entity, so there should have a column in the Venues entity would be same with the column in the Programmes custom entity.

You need to create a flow as screenshot below:

The flow would get all list from the two entities, and would apply to each item in the  Venues entity, to find the correcponding items in the Programmes custom entity.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CharlotteWest]

Hi - that's great, thank you. I've managed to make the flow and run it, but I'm confused as to where the results end up? Do I have to add another step to get it to create a spreadsheet or something? Sorry, I'm really new to this!

[v-alzhan-msft]

Hi @CharlotteWest ,

You could create html table with the array and send an email with the html table:

You would receive multiple email, each with a table of Venues and their related Programmes.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ManishJain]

Hi @CharlotteWest ,

If your intention is to get the child data with parent why not use List Records option from Common data service (current environment) and use the fetchxml of your choice with required columns. 

Once you get the results use Create CSV Table action and select advanced options and there select custom for Columns . Do mapping from the result . In next step use Create File action from Sharepoint and save it in desired location. Do remember the file format should csv.

Thanks

[CharlotteWest]

Hi - I don't understand what you mean. When I said I was new to this - I really meant it! I've never used fetchxml before so I'm not sure how what you've described is much different to just creating an advanced find. Does adding the fetchxml element mean it could take the information from a different place?