Dataverse - Copy value from text column to lookup column

[xsomej33]

Hi all,

I have the following scenario:

• Dataverse table called ETAM_Inventory
• Dataverse table called ETAM_Locations
• Single line of text column in the Inventory table called Location_text
• Lookup column in the Inventory table called Location
  
• I already have the relationship created (due to the creation of the lookup column)
  
• I'd like to copy the value from the column Location_text to the column Location in each row:
  
• I've build PowerAutomate flow (if there is some other way, please let me know) to handle this, but it's failing:
  
  
  

Any suggestions on how to fix this?

[dpoggemann]

Hi @xsomej33 ,

The Location is a Lookup to another table record (i.e. Location table), behind the scenes this is a GUID and you will not be able to replace a text value in this field as this is not the "Location" record GUID.  

In your Power Automate Flow you should do a Search on the Dataverse Locations table where the Name (assuming that is the text value you have) matches and then when you find it you can update the Inventory table with the unique id from that table.  

https://docs.microsoft.com/en-us/power-automate/dataverse/search 

[xsomej33]

Hi @dpoggemann,

Thanks for the tip - I've tried to create a flow that would get me the Location GUID, but it's running for 3 days now and seems like it's not set correctly. Could you have a look below please what am I doing wrong?

[gowrihalan04]

@xsomej33 You havent added filter condition. In the filter rows you should write something like below.

name(this is primary field on the location entity) eq '<your text value>(location text)' and you should do this query on location entity. your flow should look like below.

1. trigger on update of inventory entity

2. list record action on location entity (filter condition (name eq 'location_text from step 1').

3. from list record get location_id value.

4. update action on inventory entity(pass result.location_id to location lookup field)

Note: make sure you select filter attribute condition in step 1.(your flow should not trigger on update of this location lookup field) 

[xsomej33]

Hi @gowrihalan04,

Thanks for the tip, I finally got somewhere!

The flow will be used for one time only, so the trigger is manual. But the rest is working fine - I'm now able to get the location_id based on the location_text value. Still, I'm not able to update the row in the main Inventory table.

Here is the flow:

Failing due to this error:

[gowrihalan04]

@xsomej33 Ah I got it. Its simply. your step should be like below.

1. Manual trigger

2. get Inventory records. Then for each inventory you will have location text. Within apply to each step add list record step where you will location id. Now in the filter condition instead of hardcoding the text value, pass text value from inventory record. you will get the id

3. Finally add the update action where you can update the inventory you retrieved in the step 1.

4. this will iterate through all the records. 

[xsomej33 · Accepted solution]

It was still failing, but I've used a different method to submit values to the Dataverse upon data import, described in this article:
How to map a Lookup Column in a Power Platform Dataflow (apprising.co.nz)

But thanks @gowrihalan04 for your help!