Update Sharepoint list from other Sharepoint list

(0) Share

Report

Posted on by LennartWalldén

181

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

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (2)

Sort by

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
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
Title Single line of text
Contact person Single line of text
list2:Test
Column (click to edit) Type
Title Single line of text
Contact Person or Group(Do not allow multiple slelections)
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*/ } ) )

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

Was this reply helpful? Yes No
LennartWalldén 181 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi v-bofeng-msft,

Thanks for your reply.

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

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
LennartWalldén 181 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi again v-bofeng-msft,

Ok, thanks a lot! This will help me a lot.

One more question: I have some other fields that I need to update if they are changed, where the column names and types are the same, do I need to specify the fields one by one, or is there any way to do this automatically?

Best,
Lennart

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
LennartWalldén 181 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi again  v-bofeng-msft

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

Was this reply helpful? Yes No
LennartWalldén 181 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi again  v-bofeng-msft

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

Was this reply helpful? Yes No