Face the fear of $expand and get the lookup values you deserve in Power Automate
I love power automate. I appreciate lookup fields, but I' do NOT love power automate and lookup fields together. The issue starts to arise when you are trying to send notifications which include information about records which includes lookup fields.
For example - sending an email about a contact who works at a company called ‘ Vegemite’ comes back as ‘eca9ea6f-c96b-ea11-a811-000d3a58d840‘. I’m not sure about you, but me and most my customers don’t speak '“GUID”.
I covered one way of handling this in a previous blog [1] using the ‘Get Records’ action, however I know full well there is a better way to do this. Its called $expand, there’s lots of blogs and documentation about it [2]. I have been avoiding the $expand query for two reasons:
“if it aint broken don’t fix it“ - I don’t need this formula and I can work around it using ‘Get Record’, I know its not so friendly of the API calls nor so efficient but it works and speed/API call budget isn’t an issue for any of my current scenarios
the name $expand scares me - it seems too ‘codey’
It’s like making changes in the default solution or direct in production - you know its frowned upon but it does the job and you just don’t tell anyone. Fortunately it annoyed George Doubinski enough that he offered to teach me how to do it, so long as I promised to help him work out how to teach others like me (team ‘anything but code’) about it too. So here we go!
The Goal
Send a daily summary of the subscriptions updated in the last 24 hours - I need the members name, the club they belong to and also a link to the members record
We also want to do this in a way which gets rid of the unnecessary ‘Get Records’ actions by creating more powerful ‘List Records’ queries.
The Tool
We are going to use Expand Query in the list records action to replace subsequent ‘Get Records’ actions. What the heck is Expand? It’s a way of reaching into your lookup fields and instead of the GUID you can grab out of there any value you want.
Example
A Subscription (rdu_subscription) belongs to a Member (contact)
A Member (contact) belongs to a Club (account)
I run the ‘List Records’ on Subscriptions, using Expand I can reach into the Member (contact) record and grab the members Full Name and Email Address. Then I can reach down into the Club (account) record and get the Club Name plus anything else I fancy along the way! Rather than running ‘Get Records’ afterwards for Member (contact) and Club (account).
The other great thing is that you can combine the queries you use here so whatever other filtering methods you are using, don’t need to be changed
The end result looks like this - a nested expand query using complex fields, but lets break it down as to how on earth that happened because that’s a pretty scary looking lump of code!
rdu_member($select=firstname,lastname,parentcustomerid_account;$expand=parentcustomerid_account($select=name))
The Secret Weapon
As you can see we need to get down into the underlying names of the entities and fields we are working with, which isn’t always so easy. So first we will use Google Chrome to install your secret weapon - Level Up for Dynamics 365 & Power Apps. This magical tool has a whole wealth of handy functionality but the ones we are going to use here are ‘Logical Names’, ‘All Fields’ and maybe ‘Open Record in Web API’
Part One: Get the Members (contact) first and last name
So how do you eat an elephant? One bite at a time. Let’s start with getting the Member first name and last name and for this we simply need to use the ‘Show logical names’ button from Level Up!
rdu_member($select=firstname,lastname)
Ok so nothing exploded - now what? If you go down to the section where you would have previously used ‘Get Records’ - you will see some new Dynamics Values in your list coming from the List Records action. The first name and last name - wow!
If one level of lookups all you need you can stop right here, delete your ‘Get Records’ action, switch in the new dynamic values and be on your way to a much more efficient flow. But if like me you also want to ‘expand’ a little deeper - read on to part two.
Part Two: Get the name of the Club (account)who the Members (contact) belongs to
Quick recap
A Subscription (rdu_subscription) belongs to a Member (contact) - we got this bit.
A Member (contact) belongs to a Club (account) - we need this bit
We take the query from above, remove the last bracket and ask for another field. According to ‘Show field values’ its called parentcustomerid but if you try to run the expand query with this he will seriously disagree. This pesky customer field is a bit of a special case, it can be used for linking contacts or accounts so for this we need to use the ‘All Fields’ control on Level Up! you can see that the ‘associatednavigation property’ changes based on if the value is a contact or an account.
Now the formulae knows where we want to look. Next, we add another expand query - this time for the Club (account) entity using the ‘associatednavigation property’ field name rather than the actual underlying name. All we want from the Club (account) is the ‘name’. Because this query is nested we need to use the $expand annotation this time.
rdu_member($select=firstname,lastname,parentcustomerid_account;$expand=parentcustomerid_account($select=name))
Where are my dynamic values?!
Logically now you want to go down to the section where you would have previously used ‘Get Records’ - and expect to see some new Dynamics Values in your list coming from the List Records action, in addition to the first name and last name from part one, but instead the last name has maybe disappeared and the Club (account) name is nowhere in sight - whaaaaat!
What’s happened here? Your too smart for Power Automate! Once you start nesting expand queries some values stop coming back in the ‘Dynamic values’ options. This is normally where I give up and decide its broken and stop trying. DONT GIVE UP - YOU ARE SO CLOSE!
Using the formula below you can get the value from the expand query. Where ‘For_each_member’ is the name of my apply to each action. When you first drop it in it will look a bit odd blue and broken, bit if you save and refresh it will render itself to a more CDS field looking item and this also confirms that the code you have written is understood by the flow.
items('For_each_member')?['rdu_member/lastname'] items('For_each_member')?['rdu_member/parentcustomerid_account/name']
And that my friends is a whirlwind tour of the expand and nested expand query. I really hope the pictures and explanations of where the bits of code come from help you to build your own expand queries more easily - even if you don’t code!
A massive shout out to the amazingly patient George Doubinski for taking the time to teach me this terrifying thing, so I could first understand it, then help to explain it in a less ‘developer-esque’ way. Any comments or questions please hit the comments below - I would love to help more people on their flow journey to expand their horizons (terrible pun intended).
[1] My first documented war with lookup GUIDs in HTML tables https://www.ameyholden.com/articles/html-tables-with-lookup-values-in-power-automate
[2] $expand query documentation https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/retrieve-related-entities-query
The Video Version
If you are more of a watch than read kind of person, head over to Citizen Can for audio visual simulation on expand
An introduction to what on earth is expand, why you should use it, and a simple (yet powerful and API call saving) example of how.
Can we expand the expand? If yes then how far can we go? Amey "Anything But Code" Holden talks to George "The Enabler" about nested expands in Power Automate and how they make it easier to get to that piece of data you really want regardless how deep it's buried.