“How can I get data from another Dataverse table connected by a lookup column, what’s the best approach in Power Automate?”
When building solution over Dataverse, you might end up with multiple tables to store different type of information. Yet the information is often connected and the way to create such connection are lookup columns. There’s already a post on updating the lookup values using Power Automate, but how do you use them afterwards? What’s the best way to get the additional information from the second table?
You don’t need an additional action
Unlike with SharePoint where you need another ‘Get items’ action, in Dataverse you don’t need second ‘List rows’ (as long as there’s a lookup column). It’s because the ‘List rows’ action already offers such option in the ‘Expand Query’ field.
But what do you put there? As you might expect you’ll have to type in the input manually.
Use the Expand Query option
The input has always (at least) two pieces – the lookup column schema name and the data column logical name.
<lookupColumnSchemaName>($select=<dataColumnLogicalName>)Firstly, you need the Schema name of the lookup column in the main table.
Secondly, you’ll need the Logical name of the column in the second table.
Once you have both the pieces, turn them into the ‘Expand Query’, e.g.
cr09b_LookupColumn($select=cr09b_anotherlookupcolumn)
If you need more columns from the same table, separate them by comma…
<lookupColumnSchemaName>($select=<dataColumnLogicalName>,<dataColumnLogicalName2>,<dataColumnLogicalName3>)…or use the same approach to select columns from multiple tables.
<lookupColumnSchemaName>($select=<dataColumnLogicalName>,<dataColumnLogicalName2>,<dataColumnLogicalName3>),<lookupColumnSchemaNameX>($select=<dataColumnLogicalNameY>,<dataColumnLogicalNameZ>)Summary
Power Automate allows you to get data from another Dataverse table connected by a lookup field without any extra actions. You don’t need to add another ‘List rows’ with a filter, but you must know what to enter in the ‘Expand Query’. Get the lookup column Schema name together with the data column Logical name, put it together, and collect related data across multiple Dataverse tables in a single action.
Can I hire you for 30 min to 1 hour? I need to filter a dataverse query using the label value (not the index) of a choice column.
I would like to query dataverse and list the index and values of a choice list, but I can’t figure it out.
Thanks…
Hello Ben,
yes, I do consulting sessions: https://tomriha.com/power-automate-consulting/
Thanks for the info. Do you perhaps know how to do the same for a 3rd level table i.e. Main table has a lookup to 2nd table which in turn has a lookup to 3rd table – I am wanting to retrieve a column from the 3rd table.