Copying one Dataverse table to another, Having issues where source column is a Lookup and I want destination to be Text

[RandomDept]

Hi All,

I'm still pretty new to the community or at least in terms of using Power Automate.

As in the title, I want to create a scheduled flow that will copy all data from a Dataverse 'Environment Capacity' table (It's one that comes with Centre of Excellence that contains information about usage etc per environment.

Problem is that the 'Environment' column is listed as a Lookup. (Which I haven't got my head around 🙂

My Flow is outlined like this:

Scheduled Trigger
List Rows - Dataverse

For Each

Create Row.

I've started with a List Rows, filtered so environment must be 'Active' and Log usage isn't 0.

I then have a Apply to each, create a row in the new table with the sources from the list rows step.

The flow works, but 'Environment' (text) in the new table is not populated.

After trying CoPilot for the answer, it suggested adding the Get Row by ID and Compose with 'outputs('Get_a_row_by_ID')?['body/_environment_value@OData.Community.Display.V1.FormattedValue']'

and to replace _environment_value with my actual schema name of the lookup field 'admin_Environment

'outputs('Get_a_row_by_ID')?['body/admin_Environment@OData.Community.Display.V1.FormattedValue']'

New flow:

Scheduled Trigger
List Rows - Dataverse

Get Row by ID - Dataverse

Compose

For Each

Create Row.

But same issue, Environment field is not being populated,

Any help is greatly appreciated.

Thank you

RD

[BizAppBee]

Hi @RandomDept, for the purpose of this post I created a custom Environment, Environment Capacity and a destination table. Then I created a flow to copy the Environment value from a lookup field to a text field. I have recorded the steps here -> https://www.youtube.com/watch?v=0J-4-kCgg0I

Hope it helps!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you!

Leoza

[RandomDept]

First of all, Thank you. This was above and beyond.

I've got the flow steps in the right order now. I am getting an error saying the admin_environment with ID xyz Does not exist. Additionally looks like Display name returns a guid instead of text as well.

I manually exported both the Environment and Environment Capacity tables to csv so I can take a look.

I copied the guid from the error and nothing was found (as expected by the error)

I took a known environment guid and searched the Environments table and found it under the admin_EnvironmentID field.

On inspection it appears the columns I need to compare are admin_environmentId from Environments and admin_recordguidasstring from Environment Capacities. (1 & 2)

Problem is that admin_recordguidasstring doesn't exist in the Dataverse table. IE must be hidden in the lookup somewhere.

I try adding the recordguidasstring to the initial List Environment Capacity (3), but when trying to use that colum in (4) it is not shown. I have tried a few different variations...ie admin_Environments.admin_recordguidassting, admin_recordguidasstring, admin_Recordguidasstring, admin_RecordGUIDasString etc and no luck.

I'm sure if I can get this field to show up it will all work how I wanted.

Thanks again

RD

[BizAppBee · Accepted solution]

Hi @RandomDept, sorry I don't fully understand I don't think. Could you do a List rows with all those fields in the 'Select column' and instead of admin_recordguidstring, just have admin_environnment and then use the output of the list rows to do a 'Get Row' with the Environment(Value) from the dynamic content to get the Display Name (admin_displayname)?

One other way you could achieve this is by adding admin_Environment($select=admin_displayname) to your expand query in your list rows and then using the Environment Display Name to create your new record. This way you don't have to do the 'Get Row.' (I am using our COE Environment tables for this test rather than my custom tables)

Here is the result. You can use the display name directly when you create your new record, or use the output of the compose or store it in a variable.

Hope it helps!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Thank you!

Leoza

[RandomDept]

@BizAppBee you are a legend.

I added the expand filter in , removed the get row by ID, skipped the compose and went direct to the add a row and it works like a charm.

Thank you again for your time and expertise!