Dynamics 365 Joined Entity fields

[Anonymous]

Hello,

I'm building a flow connected to Dynamics 365 in which I want to list (via LIST RECORDS) all the closed opportunities from the Opportunity entity, and then email said as an HTML table.

All is working except for selecting my fields I want in the HTML table. When I try using the account field from opportunity, it will only give me the GUID linked to the actual account entity.

So I joined the accoun entity via the EXPAND line, yet I'm still not able to select any account entity field, only opportunity.

Fields listed in the picture are only from the Opportunity entity, when I research for Account fields, nothing shows up.

Any idea how to fix this? Is there something I'm doing wrong?

Thank you for you help.

[abm · Accepted solution]

Hi @Anonymous ,

Please follow the below steps: 

Expression value:

items('Apply_to_each')?['parentaccountid']?['name']

Test Results:

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up.

Thanks

[yashag2255]

Hi @Anonymous 

You might want to look into this solved thread where I have explained how to get the account or any other related records information and create HTML tables from those with the required custom headers. 

https://powerusers.microsoft.com/t5/Building-Flows/Sales-Rep-from-Dynamics-to-HTML-Table/m-p/291418#M30608

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

Hello @abm ,

I tried your solution and while it works to get the account name, it only works for the first one, and then it populates every other record with that account's name.

So for Example Record #1 has Contoso as account name, all the other records will have that as well, instead of their actual account names.

Thank you for your help!

[abm]

Hi @Anonymous ,

That can't be right. I just tested with two different account names for two opportunities. It did worked as expected. Please check your mapping names? 

Thanks

[Anonymous]

@abm 

Here are some screenshots of my flow and the results:

And the results the first "Compte Name" being what they should have, vs what the flow is retrieving:

Thank you for your help!

EDIT: Just to be clear, when I look at the test results like you did in your first post, the inputs/outputs look fine, only when it goes into the HTML table that it seems it's not getting the right data.

[abm]

Hi @Anonymous ,

Yes I reproduced it and I can see it's setting the same value.

We need to use the array output here. So declare a variable array and append the results in the array. Set the array in the header. Please see below.

Next under the append array step use the expression 

items('Apply_to_each')?['parentaccountid']?['name']

Let me know the results.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up.

Thanks

[abm]

Hi @Anonymous 

Also the last flow step below "Create HTML Table" set the Columns Automatic.

Thanks

[Anonymous]

Hello @abm ,

The test flow fails since the accounts are string values. I don't even get to create the HTML table.

See the screenshot below:

Thank you for your help!

[abm]

Hi

See my screenshot where I defined as array. Yes the string will fail.

Thanks

[Anonymous]

@abm  Okay it works now, but I only get the opportunity's account name.

Is it possible to add other columns?

[abm]

Hi @Anonymous 

Dynamics List Records flow action Expand query do the following:

Here I am trying to get the account name and fax

parentaccountid($select=name,fax)

To get the value
items('Apply_to_each')?['parentaccountid']?['fax']

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up.

Thanks

[Anonymous]

Hello @abm 

How would I do if I wanted fields as headers from both Opportunity entity and Account entity? Would this be possible?

Thank you for your help!

[abm]

Hi @Anonymous 

Try push the new values into the array. See my earlier screenshot how we mapped the name in Append Array step.

Thanks

[abm]

Hi @Anonymous 

This is what you need to do

Extend the Append Array step

For the above Fax mapping expression is as follows:

items('Apply_to_each')?['parentaccountid']?['fax']

Below is my test results:

Thanks

[Anonymous]

Hello @abm ,

I can't seem to get this right unfortunately... I get this error message when I try to add the fax field (I added it in the expand query first)

Thank you for your help

[abm]

Hi @Anonymous 

Append array looks ok.  Check the flow step "Create HTML Table" set the Columns Automatic.

Thanks

[Anonymous]

Hello @abm ,

Here are the screenshots of the flow:

Test results:

We can see that there is some data getting out but somehow it's still not working.

Thank you again for your support in all of this, I appreciate it!

[abm]

Hi,

That's strange. Try to delete the Create HTML table action. Save the flow. Add the same action step again and try it.

Thanks

[Anonymous]

Hello @abm 

Still the same error unfortunately. Could this be because of the type of data?

Would it be possible to have headers from the opportunity entity and account entity at the same time?

Thank you for your help!