Bulk update of Entity field that using lookup Field

[Anonymous]

Hi Everyone,

I have question that I have not seen a post for. I have a project that the requirements need a new field added to the entity that was using a lookup to another entity

Entity:

Entity Name: Inventory Item

Entity Field: Inventory Group (Field I want to do bulk update on)>>> This field has lookup defined to 'Inventory Groups' (Entity) with a field called 'Inventory Group' with ten values … we will call one of the values "New Company"

Lookup:

Entity Name: Inventory Groups

Entity Field: Inventory Group (Key Field and used as lookup field)

I was thinking of just creating a button to fill in the blanks of all the new values in the dataset. I know power BI might be easier, but now just curious to find the error in my logic. I was looking at using a ForAll function, but definitely have something wrong with formatting.

My first thought was this:

ForAll('Inventory Items',{'Inventory Group':First(Filter('Inventory Groups','Inventory Group'="New Company"))})

This does not cause any errors but does not update anything on the table.

Then I was thinking I need to use a Patch function inside the ForAll statement, but this just creates errors that "you cannot use Patch on the same dataset" of the Forall Function.

Does anyone have an idea of how I would change all values of the field 'Inventory Group'  to say a text value "New Company". For simplicity we will just say we want to change all record of the entity 'Inventory Item' (I can work out the filters later).

Thanks,

[v-yutliu-msft · Accepted solution]

Hi @Anonymous ,

Do you want to update a lookup field of Inventory Item(all the record)?

Could you tell me the Key Field of Inventory Item entity?

I assume it as key field name, please replace with the real field name.

If so, please try this:

1)create a collection with the data of Inventory Item:

ClearCollect(a,Inventory Item)

 2)update:

ForAll(a,Patch(Inventory Item,LookUp(Inventory Item,key field name=a[@key field name]),{Inventory Group:LookUp(Inventory Groups,'Inventory Group'="New Company")}))

 //if you want to update all the records/multiple records, you need to firstly create a collection with the data of the records that you want to update.

Here's a doc about how to bulk update records for your reference:

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

Best regards,

[Anonymous]

Thanks v-yutliu-msft,

That worked. A little slow, but it worked. 

Here is the final equation:

ClearCollect(a,'Inventory Items');ForAll(a,Patch('Inventory Items',LookUp('Inventory Items','Item No'=a[@'Item No']),{'Inventory Group':LookUp('Inventory Groups','Inventory Group'="New Company")}))

Thanks Again,

Jason

[Anonymous]

Hi @v-yutliu-msft  Thanks for your solution. 

I was wondering if there is any way to do the same in model driven power apps. 
I have a field (company name) in contact table which this field is the lookup column from other table named company. I need to bulk update the name of company based on my scraping from LinkedIn.
Appreciate it if you can help me.
Cheers,
Zara