Batch update lookup column in dataverse table

[Anonymous]

I have created two tables,

Table1:

devicename	type	lookupcolumn
device1	type1
device2	type2
device3	type3

Table2:

devicename	IP address
device1	1.1.1.1
device2	2.2.2.2
device3	3.3.3.3

I need to fill in the lookupcolumn in table1 so that it can have the relationship with table2.

devicename	type	lookupcolumn
device1	type1	device1
device2	type2	device2
device3	type3	device3

Then I can get all my required relationship data as below:

devicename	type	IP address
device1	type1	1.1.1.1
device2	type2	2.2.2.2
device3	type3	3.3.3.3

Can anyone tell me how to update the lookup column in a batch for more then 3000 records?

[dpoggemann]

Hi @Anonymous ,

Couple things here...

1. You might want to reconsider your table relationships to be something like the following:
   1. Device Type Table - Contains listing of device types (Type1, Type2, Type3)
   2. Device Table - Contains devices with lookup column to Device Type.  Fields in this table would be (devicename, lookup to Device Type, IP Address)
2. From here you can utilize multiple options to upload your data and populate into Dataverse
   1. Data Flows - You can setup to pull from Excel or other data source
   2. Excel Import - This is what I usually do most of the time as it works well for me.  You can download the data import template for your entity (https://docs.microsoft.com/en-us/power-platform/admin/download-template-data-import) 
3. In the data import you can just set the value of the device type name and it will map correctly to that type (as long at the primary name field is unique on the Device Type table)

[Anonymous]

@dpoggemannThank you for your prompt update.

But my intention is to link up the two table of devicename/Device Type and and devicename/IP Address together.

Because I will collect them from different source.

That's why I want to use the devicename as the lookup to get the final result as devicename/Device Type/IP Address.

And I have many other data source with the similar logic to link together. So I want to use the polymorphic lookups.  My current question is how to hard write the lookup column into the table in a batch for all rows.

[dpoggemann]

Hi @Anonymous ,

Sorry, I am struggling with how you are going to generate your end result table with the fields combined?   What I would do is something like this to accomplish your needs.  Sorry if it doesn't help, hopefully someone else will have an idea...

Note - the process below could switch Table1 and Table2 if Table2 has records created first (basically add the Type to table2 and switch Table1 & Table2 below)

1. Table1 primary name field would be the Device Name and it would have two additional columns including the Type and the IP Address
2. Table1 records would need to exist before you import records for Table2
3. Table2 would contain a primary name field that could be anything but if you want it to be the name of the device that would be fine, this table would also have the lookup column for Device Name and the IP address.  The Device Name column (when importing) would be the set to the name of the device from Table1 and it will automatically match and create the relationship.
4. Create a realtime workflow on the creation of the record in Table2 that will do the following:
   1. If Device Name lookup field contains data
      1. Update Record - Table1 and set the IP address on that table

This above process will create that end result of the combined table setting the IP address on Table1 based on the import of records from Table2.  You may also want to setup the realtime workflow to update the parent record (Table1) if the IP address is updated on Table2.  

Note, this only works if the device has a single IP address of course.

Again, sorry if this doesn't help.

[Anonymous]

@dpoggemannI think I have got the solution now.

LookUp(table2, devicename = ThisItem.devicename).ipaddress.

I use the above formula to get my required result in the CavasApp now.

As writing back to the table, I can use patch function for every single record. But I am still checking how to use bulk update to update a lookup column.

[AhmedSalih]

@Anonymous, by updating more than 3000 lookup values in the lookup column, do you mean to load them into the Dataverse table from another data source or to patch them from a Canvas app?

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

[Anonymous]

@AhmedSalihI am seeking a way to update, it does not have to be via Canvase App. Do you know if there is a more efficient way?

[AhmedSalih]

@Anonymous, 

• Make sure the Parent table (The One side of the Ralationship) has the data loaded first. In your case, I think it is Table 2
• The devicename column in Table 2 is your primary Key Column, so you need to create an Alternate Key and select the devicename column. (This is a very important step to load the data into the lookup column)
• Make sure you have the relationship in place (Table1_Looukpcolumn_Table2)
• Create a Dataflow to load the data into Table 1.
• You will load the data into the existing Table1, make sure to select that option when you are configuring your Dataflow. 
• Map your column and publish your Dataflow. 

All Lookup column values that have a correspondence value in Table2 will load to Table1.

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

[Anonymous]

Thanks AhmedSalish, this way works when the data was first loaded into the table. if there is update the tables, I have run the dataflow agian. I am checking whether I can use power apps collection to get all the devices need to be updated with lookup column, then use Relate/Patch function to update, I can see if I need to do it for all record, I can use the function ForAll. I am still testing it. Thanks for your advise.

[AhmedSalih]

@Anonymous, I don't think ForAll is delegable for Dataverse. Another method is you can use the collections and the patch function to update up to 2000 a time. You can consider update your records in patches. @WarrenBelz has this great article to cover this method. 

https://www.practicalpowerapps.com/delegation/power-apps-delegation-sharepoint/#Collections

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.