Forall , Patch issue

[Gautham001]

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.

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!

[Drrickryp]

@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.

[Gautham001]

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?

[v-yutliu-msft]

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,

[Gautham001]

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!

[v-yutliu-msft]

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, 

[Gautham001]

@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.

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

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

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

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

Thanks for the continued support!

[v-yutliu-msft]

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,

[Gautham001]

@v-yutliu-msft  ,

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

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.

[v-yutliu-msft · Accepted solution]

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,