cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShaoXingHuang
Frequent Visitor

Batch update lookup column in dataverse table

I have created two tables,

Table1:

devicenametypelookupcolumn
device1type1 
device2type2 
device3type3 

 

Table2:

devicenameIP address
device11.1.1.1
device22.2.2.2
device33.3.3.3

 

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

 

devicenametypelookupcolumn
device1type1device1
device2type2device2
device3type3device3

 

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

devicenametypeIP address
device1type11.1.1.1
device2type22.2.2.2
device3type33.3.3.3

 

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

 

 

 

9 REPLIES 9
dpoggemann
Super User
Super User

Hi @ShaoXingHuang ,

 

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)
Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

@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
Super User
Super User

Hi @ShaoXingHuang ,

 

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.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

@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
Super User
Super User

@ShaoXingHuang, 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.

@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?

@ShaoXingHuang

  • 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.

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
Super User
Super User

@ShaoXingHuang, 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.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Users online (4,080)