cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LennartWalldén
Advocate I
Advocate I

Update Sharepoint list from other Sharepoint list

Hi all,

 

We have an old Sharepoint list that we have been updating with Sharepoints built-in functions. Now I have created a new list with a PowerApp on top to make it easier to update the data.

 

I started off with a copy of the old list, ProjectList_1, and then I have added a bunch of fields to the new list, ProjectList_2.

 

Meanwhile I have been deveoloping, my colleagues have been updating the old ProjectList_1, and now I want to update the records in the new list.

 

I have tried to use Patch, but I can't get it to work. I want to be able to update this a couple of times because I must do some fixing and testing with the data before we use the new ProjectList_2 only.

 

Question 1 : How do I update ProjectList_2 with the data in ProjectList_1? Some of the columns have the same names, and it's only these I want to copy data from.

 

Question 2: In the old list I have a Contact person field which is a text field. How do I use this value to set a People Picker field?

 

Best,

Lennart

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @LennartWalldén :

You can add a judgment condition, update if the target record exists, and add a new record if the target record does not exist.

Pleast try this code:

ForAll(
    Test1,
    Patch(
        Test,
        If(IsBlank(LookUp(Test,Title=Test1[@Title])),Defaults(Test),LookUp(Test,Title=Test1[@Title])),
        {
            Title: Test1[@Title],
            Contact: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & 'Contact person',
                DisplayName: Office365Users.UserProfileV2('Contact person').displayName,
                Email: 'Contact person',
                JobTitle: "",
                Picture: ""
            }
        }
    )
)

Best Regards,

Bof

View solution in original post

Hi @LennartWalldén :

You still need to write them in the formula and pay attention to disambiguation.

ForAll(
    Test1,/*Traverse Test1*/
    Patch(
        Test,
        Defaults(Test),
        {
            Title: Test1[@Title],
            Contact: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & 'Contact person',
                DisplayName: Office365Users.UserProfileV2('Contact person').displayName,
                Email: 'Contact person',
                JobTitle: "",
                Picture: ""
            }/*Assignment of data of type person needs to use a specific format*/
            Column1: Test1[@Column1]
            Column2: Test1[@Column2]
            ……
        }
    )
)

Best Regards,

Bof

View solution in original post

7 REPLIES 7
v-bofeng-msft
Community Support
Community Support

Hi @LennartWalldén :

Do you want to transfer records from list1 to list2 as new records?

My method is to use Forall and Patch functions

I'v made a test for your reference:

my lists:

list1:Test1

Column (click to edit) Type

TitleSingle line of text
Contact personSingle line of text

1.JPG

list2:Test

Column (click to edit) Type

TitleSingle line of text
ContactPerson or Group(Do not allow multiple slelections)

2.JPG

1\Add a button

OnSelect:

 

ForAll(
    Test1,/*Traverse Test1*/
    Patch(
        Test,
        Defaults(Test),
        {
            Title: Test1[@Title],
            Contact: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & 'Contact person',
                DisplayName: Office365Users.UserProfileV2('Contact person').displayName,
                Email: 'Contact person',
                JobTitle: "",
                Picture: ""
            }/*Assignment of data of type person needs to use a specific format*/
        }
    )
)

 

3.JPG

I think this link will help you a lot:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Person-column-and-SubmitForm/td-p/549235 

Best Regards,

Bof

Hi  

 

 

First of all, I want to update changed records, but also create new ones if they don't exist.

How do I change your code to update records if their values have been changed?

 

Best,
Lennart

Hi @LennartWalldén :

You can add a judgment condition, update if the target record exists, and add a new record if the target record does not exist.

Pleast try this code:

ForAll(
    Test1,
    Patch(
        Test,
        If(IsBlank(LookUp(Test,Title=Test1[@Title])),Defaults(Test),LookUp(Test,Title=Test1[@Title])),
        {
            Title: Test1[@Title],
            Contact: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & 'Contact person',
                DisplayName: Office365Users.UserProfileV2('Contact person').displayName,
                Email: 'Contact person',
                JobTitle: "",
                Picture: ""
            }
        }
    )
)

Best Regards,

Bof

View solution in original post

Hi again  

 

Hi @LennartWalldén :

You still need to write them in the formula and pay attention to disambiguation.

ForAll(
    Test1,/*Traverse Test1*/
    Patch(
        Test,
        Defaults(Test),
        {
            Title: Test1[@Title],
            Contact: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & 'Contact person',
                DisplayName: Office365Users.UserProfileV2('Contact person').displayName,
                Email: 'Contact person',
                JobTitle: "",
                Picture: ""
            }/*Assignment of data of type person needs to use a specific format*/
            Column1: Test1[@Column1]
            Column2: Test1[@Column2]
            ……
        }
    )
)

Best Regards,

Bof

View solution in original post

Hi again  

 

I get some error with the code for the DisplayName when trying your code. It says something about "Calling to an unknown or unsupported function"....

ForAll(
    Test1,/*Traverse Test1*/
    Patch(
        Test,
        Defaults(Test),
        {
            Title: Test1[@Title],
            Contact: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & 'Contact person',
                DisplayName: Office365Users.UserProfileV2('Contact person').displayName,
                Email: 'Contact person',
                JobTitle: "",
                Picture: ""
            }/*Assignment of data of type person needs to use a specific format*/
            Column1: Test1[@Column1]
            Column2: Test1[@Column2]
            ……
        }
    )
)

 

Best,

Lennart

Hi again  

 

I haven't had much time for testing, but I still have two problems:

1. It does not create new items in the new list, but the updates works fine.

2. I got an error message on the code marked with red:
.......Contact: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & 'Contact person',
                DisplayName: Office365Users.UserProfileV2('Contact person').displayName,
                Email: 'Contact person',
                JobTitle: "",
                Picture: ""

 

Best,

Lennart

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,926)