Written by While working on a requirement I came across a scenario where I wanted to fetch an attribute from a Customer field and run the logic accordingly.
Even after intensely looking for answers I couldn’t find any resolution, so I came up with my own solution (so not sure if this is the right way).
In this post, I will show you how to fetch the data from an entity where Customer lookup is used.
To begin with, let’s create a Power Automate with trigger event as “Manually trigger a flow”. You can have any trigger event you want; but I have used this trigger to simplify testing.
Now, let’s add a List Record query to get all the Contacts with some filters. In my case, I have added a filter to fetch active contacts that have parent customer id and job title populated. And I want to fetch account number from parent customer id (account); for that I have used expand query. To get my filter and expand query I have used “FetchXML Builder” tool from XrmToolbox.
Let’s execute and confirm the result. Once we are satisfied with the result, time to get the filter and expand query. This is shown below.
Filter and expand query needs to be added to List Record action in Power Automate.
This is where things got little tough for me. I thought if we use expand query then the results will be available for me to use but I didn’t find the attribute in dynamics expressions. So, I used “Parse JSON” action to parse the entire body content. When you use this action you need to provide schema. So the trick was to execute the flow with just one action – List Records. This gave me the complete output in JSON format.
With this JSON data, I added my next action “Parse JSON” and used “Generate from sample” button and entered my JSON data that I had captured earlier. This generated my schema for Parse JSON action.
Using the above mentioned action makes things easier to get properties from the JSON object. In my example I want to send an HTML table via email. So, I used “Create HTML table” action and passed my JSON output and added some custom columns to the table that will display the data I want from Customer Lookup attribute. As we had provided the schema for “Parse JSON” action Power Automate was able to provide us with all the properties from the JSON output which we can use it in our “Create HTML table” action. In my case I wanted to send an email with Contact Name and Account Number in a table format. So, I created my table with two columns ContactName, AccountNumber and passed fullname, accountnumber properties from my JSON object as the row value.
The output of the “Create HTML table” was then added to send email action – creating a nice table in the email.
Hope this helps.
For more content subscribe to my blogs and follow me on:
LinkedIn: https://www.linkedin.com/in/danishnaglekar/
Twitter: https://twitter.com/Danzmaverick
GitHub: https://github.com/Power-Maverick
YouTube: https://www.youtube.com/c/PowerMaverick
ProDev Newsletter: http://www.powerplatformdevelopersweekly.com/
Power Maverick 
1 comment