cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gautham001
Helper IV
Helper IV

Forall , Patch issue

Hi all ,

 

I have a form connected to a sharepoint list , and a collection , whose items should be patched to the form , after the form is submitted.

 

Example : 

 

Form Has  A,B,C fields and collection has  E,F Fields.

 

A              B               C                                             E              F

Apple    Banana    Orange                                        1              2

                                                                                  3             4

                                                                                  5             6

 

Output Should be  :     (in the sharepoint list)                                      

 

Apple Banana Orange 1 2

Apple Banana Orange 3 4

Apple Banana Orange 5 6

 

My issue is , i only get one column  Apple Banana Orange 5 6 and not the other rows in my collection. Is This possible?

 

Please check the images to get a better understanding of my issue.

 

final2.PNGfinal4.PNG

 

There are more than just 2 columns in both the list and collection , but i think the output mentioned can be achieved. 

Please guide me further , thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Gautham001 ,

Two points:
1)you only need to use [@] when the two column names are the same in the collection and in the list.

tablename[@fielaname] is used to avoid ambiguity.

In your issue, you do not use this in some fields, like: ItemNameE.

"ItemNameChosen:ItemNameE" is enough.

2)yes, person type is the key point.

You need to use special formula to update person field.

Please notice the structure of person field:

{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        Claims:"i:0#.f|membership|" & userEmail,
    Department:"",
   DisplayName:UserFullName,
         Email:UserEmail,
      JobTitle:"",
       Picture:""
   }

So you should use formula like this:

 

ForAll(collection2,Patch(MerchandiseList,Defaults(MerchandiseList),
                          {Plant:{Value:collection2[@Plant]},
                           Title:collection2[@Title],
                           TotalExpense:Value(collection2[@TotalExpense]),
                           ItemNameChosen:ItemNameE,..., //some other fields
                           Name:
                   {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                     Claims:"i:0#.f|membership|" & collection2[@Email],
                     Department:"",
                     DisplayName:collection2[@Name],
                     Email:collection2[@Email],
                     JobTitle:"",
                     Picture:""
                    }
                           }
                          )
)

 


 Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Drrickryp
Super User
Super User

@Gautham001 

I may be misunderstanding but I think you have made it more complicated than it needs to be. If your collection has the exact same column names as your datasource, you can just Collect(datasource, Collection) to merge the datasource and the collection.  If the collection does not have the same exact names as the columns in your datasource, you can reshape the collection with the RenameColumns() function.  SharePoint can be tricky sometimes and the column names are not what you expect them to be.  To make sure what they are, add a datatable to a screen and set its items property to the SharePoint list.  It will populate with the names that you need to use.  It is also useful to make sure your collection was added properly.

Hi @Drrickryp  ,

 

I do have more columns in the SP list than the collection , i tried out the Renamecolumns , but i'm not sure i'm using it right to achieve the required result i want.

 

There seems to be no issues with the collection , because the last record in the collection is always being added and the beginning ones are not. Can you explain further on how to solve this? Can i get the required output i had mentioned using RenameColumns?

Hi @Gautham001 ,

Do you want to merge a collection and a form to update data?

Could you show me the structure of the collection, the form and the list?

Since I do not know about the relationship between these three, I could not give you very detailed formula.

Now, I use your example to exaplain.

Form Has  A,B,C fields and collection has  E,F Fields.

 

A              B               C                                             E              F

Apple    Banana    Orange                                        1              2

                                                                                  3             4

                                                                                  5             6

 

Output Should be  :     (in the sharepoint list)                                      

 

Apple Banana Orange 1 2

Apple Banana Orange 3 4

Apple Banana Orange 5 6

 

In this situation, you just need to firstly add ABC columns in your collection with the data of form. Then use this updated collection to update list.

ClearCollect(collection2,AddColumns(collection1,"A",textinput1.Text,"B",textinput2.Text,"C",textinput3.Text))
//textinput1,textinput2,textinput3 are the controls in the form for field A,B,C

If in lists, you have fields named A,B,C,E,F and their data type are the same with those in collection, you could directly use this to update:

Collect(list,collection2)

If the fieldnames in list and in collection are different, you need to use RenameColumns to rename fields in collection to the same names, then update.

 

 

Best regards,

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yutliu-msft  , 

 

Just to be clear , in your example 

In this situation, you just need to firstly add ABC columns in your collection with the data of form. Then use this updated collection to update list.

ClearCollect(collection2,AddColumns(collection1,"A",textinput1.Text,"B",textinput2.Text,"C",textinput3.Text))
//textinput1,textinput2,textinput3 are the controls in the form for field A,B,C

If in lists, you have fields named A,B,C,E,F and their data type are the same with those in collection, you could directly use this to update:

Collect(list,collection2)

If the fieldnames in list and in collection are different, you need to use RenameColumns to rename fields in collection to the same names, then update.

 

 

collection1 - data from form (new collection to created?)

collection2 - already existing collection created previously?

 

Combine these two lists ,

and then use Collect(list,collection2)?  The names are different but i will try to rename themto see if it works!

Hi @Gautham001 ,

collection1 is your original collection with fields( E,F)

collection2 is the new created collection by using data of the form and the original field:

ClearCollect(collection2,AddColumns(collection1,"A",textinput1.Text,"B",textinput2.Text,"C",textinput3.Text))
//textinput1,textinput2,textinput3 are the controls in the form for field A,B,C

This formula is used to create collection2 by using collection1. You need to replace collection1 with your original collection's name.

 

Yes, if names are different you need to rename.

Here's a doc about this rename function for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping

 

 

Best regards, 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yutliu-msft  , 

I went ahead and created the collections and tried to update the sharepoint list using Collect(listname,collection).

But i'm stuck with a few errors unable to solve or understand them.

 

31.PNG

Syntax used to create collection 1 (It's name ItemCollection in this app) :34.PNG

Syntax used to create collection 2 which stores form and Itemcollection data :33.PNG

 

The collection2 is working the way i want it to , i am having issues just updating the list.32.PNG , i will also add a image about the SP List structure.

 

35.PNG , can you help me out to point where i am going wrong!

 

Thanks for the continued support!

Hi @Gautham001 ,

The reason why you could not use collect function to update directly is because of the data type in your sharepoint list.

Special data type need special formula to update.

Try this formula to update:

ForAll(collection2,Patch(MerchandiseList,Defaults(MerchandiseList),
                          {Plant:{Value:collection2[@Plant]},
                           Title:collection2[@Title],
                           TotalExpense:Value(collection2[@TotalExpense])
                           }
                          )
)

Since I only found three columns with the same name, so I could only list them as an example.

If you not only want to update these three columns, please tell me which column in collection2 is used to update which column in list in details.

To sum up, you need to update based on data type in list.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yutliu-msft  ,

 

It's working like a charm!  Except for the Name Field.

 

37.PNG

 

Name is a Person/Group field in the sharepoint list , is there a different syntax for this?

Where can i find more about this?

 

Thank you.

Hi @Gautham001 ,

Two points:
1)you only need to use [@] when the two column names are the same in the collection and in the list.

tablename[@fielaname] is used to avoid ambiguity.

In your issue, you do not use this in some fields, like: ItemNameE.

"ItemNameChosen:ItemNameE" is enough.

2)yes, person type is the key point.

You need to use special formula to update person field.

Please notice the structure of person field:

{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        Claims:"i:0#.f|membership|" & userEmail,
    Department:"",
   DisplayName:UserFullName,
         Email:UserEmail,
      JobTitle:"",
       Picture:""
   }

So you should use formula like this:

 

ForAll(collection2,Patch(MerchandiseList,Defaults(MerchandiseList),
                          {Plant:{Value:collection2[@Plant]},
                           Title:collection2[@Title],
                           TotalExpense:Value(collection2[@TotalExpense]),
                           ItemNameChosen:ItemNameE,..., //some other fields
                           Name:
                   {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                     Claims:"i:0#.f|membership|" & collection2[@Email],
                     Department:"",
                     DisplayName:collection2[@Name],
                     Email:collection2[@Email],
                     JobTitle:"",
                     Picture:""
                    }
                           }
                          )
)

 


 Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Users online (2,263)