Patching all items in a list with a lookup query from another list

[d3ell]

Hi All,

I have a large data set from an app we created a couple of years back and now in order to improve the way i can access the data i'm needing to insert another column to the list and i'm hoping that i can create a formula so that i can fill this field for all current records with the click of a buttom.

Unfortunately since making this app i havent had hardly any time using powerapps and this one is beyond me to work out.  

in order to make this easier to explain to you guys i have created a couple of lists as examples and i will explain what i am wanting to do.

i have a list called "Test_List1" - This is the list where i am wanting to update records with the "LocationCode"

I am wanting to update each record dependant on a lookup to another list

I have a list called "LocationsList" - This is where i want the Lookup function to look for each record and return the "Code" and then update the "Test_List1" Location Code with the Code returned from the lookup.

I have inserted pictures of the lists so you can see the format.

I have tried for hours all different amalgamations of a Forall / Lookup / Patch formula however i just cant get it to work, pone example below. (which doesn't work 😞 )

ForAll(Test_List1,Patch(Test_List1,LocationCode,LookUp(LocationsList,SiteLocation=Location,Code)),LocationCode))

Any thoughts, i'm probably miles off track with what is above so any help would be apprecaited.

If my dataset was smaller i would just do it all manually but really dont want to have to do that!

thanks to anyone in advance who takes a look. 

[SebS · Accepted solution]

@d3ell 

I don't know if this is best way to do this in terms of performance but for one time should do the job

Collect(
    col_List,
    Test_List1
);
ForAll(
    col_List As T,
    Patch(
        Test_List1,
        LookUp(
            Test_List1,
            ID = T.ID
        ),
        {
            Title: T.Title,
            LocationCode: LookUp(
                LocationList,
                SiteLocation = T.Location,
                Code
            )
        }
    )
)

[d3ell]

Hi, Thanks for your input, however powerApps doesn't like the code above.

getting a red squiggly line all the way from the Lookup(LocationList onwards.

Also, might i ask why you have put in the code to lookup the ID's of both lists? These ID's to my knowledge don't have no reference between the lists.

Thanks again.

[SebS]

@d3ell 

Can you make screenshot of the error and the formula, the ID refer to ID from collection I am making relationship with collection and original List to make sure Patch know what record to Update the collection is your List so the have same ID sequence and same records attached to ID to Update Record ID is required other than that it will create a new record and we don't want that.

I test the code means I must did mistake with name of a column, or Your column have different name some ware and I never notice when You provide screen I will be able to fix it.

[d3ell]

@SebS 

After my message, i noticed that there was just an 's' missing as the sheet is called locationsList.

Looks like the code works! - thank you

Would you mind trying to explain (or refer me to somewhere i can read up) why you Lookedup the ID columns first and what impact that had?

thanks again

[SebS]

@d3ell 

I crated collection col_List and collected whole Your SharePoint List to this collection so basically it was a copy of it

then I use ForAll to loop thru the collection (This is not recommended as it create performance issue). When system looping thru collection Patch applying Lookup Data to columns, I requested based on Your match from another List. Patch requires ID if You want to Update but You can Match ID = ID as this confuse Lookup and You get an Error So I create an Aliase for col_List to easy refer to it and called it "T" Than I match ID = T.ID what's same as Test_List1.ID = col_List.ID but Lookup is able to handle it so Patch was matching same records and updating based on match The required "Code" to LocationCode Column 

The collection was created cause patch and ForAll will throw an error if you try to Loop thru List and Patch to the same List 

hope it make sense My English is not the best

[d3ell]

Thankyou.  Your explation seems great, i'm just trying to understand it. I'll get there!

Thanks you verymuch.

One last question... With my actual list that i am applying your fix too the list that we are collecting has just short of 1400 records, Will this work OK?

[SebS]

Hi @d3ell 

Make sure You increase Delegations in settings in Your app to 2000 from 500 and there will be no issues if the list is not bigger than 2000 records so 1400 will be ok. The ID in SharePoint is Partial Delegable so collection without some specific approach will not collect more than 2000 records there are solutions what include mirror ID as Text what can increase this limit to 4000 but in Your scenario, this is not required.

[d3ell]

Hi @SebS 

Sorry to be a pain, i have just checked the file (see image attached) and my count of 1400 was way off, not sure why i thought it was that.

If i run the code above would it run on the first 2000 records or the last 2000 records?  If it is the last 2000 then i dont have an issue with that, however if its the first 2000 i would somehow need to update the others.

Is this something you can advise on? 

[SebS]

@d3ell 

To don't complicate things after You finish Updating first 2000 records change code in collection to:

Collect(
    col_List,
    Filter(Test_List1, Modified < Today()
);

That will pull all records what You did not modified today to the collection reducing the collection under 2000 records and will not include already updated 🙂

Couse this is one time thing that will be ok if this Solution would be something what You need to do regular, I will not recommend this.

Hope this Helps