A Double Expand Query and Some Missing Dynamic Content in Power Automate

Just earlier this week I had to do a double expand (getting data from a table that are two relationships away) and realized that when I did that, I didn’t get any dynamic content. Bleh. Well. Not too difficult to solve with an expression, continue to read to find out how.

An example scenario

So my example scenario here is that I have 3 tables, Buildings, Rooms and Contacts. Each Room have a Building that has a Building Contact. I want to list all rooms and send e-mails to the building contact. That requires me not only to expand between Room and Building, but between Building and Contact too.

A Simple Expand

I’m not really good with odata expressions and have no real intent on learning them either, so I use FetchXmlBuilder in XrmToolBoox to build my FetchXml and get the expression I need for my expand. I’ve written a blogpost about that earlier so if you are still not familiar with either XTB or FXB you should check this out.

A simple expand would be to directly use data on the building table, like the name row. By just expanding the query one level I magically get the wonderful dynamic content which first display the Table and then display the Row name. Awesome!

When I use that dynamic content I can take a sneak peak on how the expression behind it looks, just by hoovering over it. And it looked like this:

The Double Expand

So trying to reach the e-mailaddress of the Contact on the Building table while listing the Room table looks something like this:

I’m doing an expand within the expanded table. NOTE That you need to include the lookup attribute (in this case it’s sara_buildingcontact_value) for the double expand to work, if you leave that out you will not be able to get any data for the second table.

Now no dynamic content except for the Room table in itself is available, so I lost dynamic content for the first level expand by adding a second .

The Expression You Need

But since I did a little hover on my simple expand value, I could experiment and come to a conclusion on how my expression on my double expand would need to look. So I replaced the row schemaname on the first level entity with a lookup schemaname, added / and added the schemaname of the row on the second table.

Here is the expression if you’d like to copy paste:

items('Apply_to_each')?['sara_building/sara_BuildingContact/emailaddress1']

It wasn’t more complex than that. Happy double expanding y’all!

Original Post https://saralagerquist.com/2021/12/03/a-double-expand-query-and-some-missing-dynamic-content-in-power-automate/