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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (3,355)