What was the best thing that happened to you this week? For me, it was being able to use a Get a Row action in a flow, involving an Expand Query part without considering chucking my laptop out the window!

It might seem insignificant to some, but if you knew how many times I’d written the syntax wrong in the past, and how in some cases I’d abandoned it altogether and used Get a Row on the related table columns to get round it, then you’d appreciate how excited I am that I’ve now cracked it from memory!

I had a flow that needed data from 5 related tables from the triggering record… Initially I just went all guns blazing, adding 5 Get a Row by ID actions – before deciding it would be much better to just use 1 Get a Row by ID for the triggering record and using the Expand Query to pull it all in to my flow. Here is an example of how Expand Query is used, and something to note to avoid my past failures!

Above is a much simpler version than the 5 tables, put together in my Murder Mystery system, but it shows an example of what to put in Select columns and Expand Query. I’m bringing through a couple of columns from the TV Show and Actor associated with the Character.

WHat Syntax is needed

There are a few things to remember when using the Expand Query

• In Select columns use the _columnlogicalname_value format when referencing the lookup columns
• In the Expand Query use the Schema Name (case sensitive). This might be the same as the Logical Name if you edited it when creating the column.
• Use a comma to seperate the list if you are expanding the query to multiple tables
• You could pull through all columns in Select columns by not listing anything there, however you already have access to all columns from the trigger so it’s best to limit the data you a pulling in.
• You could pull through all columns in Expand Query by just doing lws_TVShow,lws_Actor and not using ($select=…) in the example above, however it would be best to limit to only the ones you intend to use in your flow.

Below is the Schema name and the Logical name, found in the column details.

Expanding the query to another level

As well as using those direct related tables through the use of Expand Query, you can also grab from a table associated with one of those related tables!

Above is an example, where I also get a column from the Lead Character listed in the TV Show.

THINGS TO REMEMBER

• You need to select the lookup column you are going to expand on in the format _columnlogicalname_value
• Use a ; (semi colon) after the last column you are selecting, before you do the Expand
• When you reference the lookup column, after $expand you need to use the Schema Name
• Watch your brackets when closing the nested select, and the initial select

CODE USED in Both Expand query examples

lws_TVShow($select=lws_name),lws_Actor($select=lws_name,lws_nationality)

lws_TVShow($select=lws_name,_lws_leadcharacter_value;$expand=lws_LeadCharacter($select=lws_name)),lws_Actor($select=lws_name,lws_nationality)