cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TWolsten
Helper II
Helper II

Update or create new item from Collection to SharePoint List

Hi all,

 

I am trying to use a Collection (Proposed) that I submit into a Gallery (Gallery1) as a 'basket'.

 

Then I want to submit all items into a SharePoint List (bp_proposed) and where items are brand new (Gallery1.Selected.New_x0020_Item_x003f_ = "Yes") it creates a new line, otherwise, it just makes amendments to an existing item.

 

The formula I have right now is on a button: 

ForAll(Gallery1.AllItems, Patch(bp_proposed, If(Gallery1.Selected.New_x0020_Item_x003f_ = "Yes", Proposed, ID = ID, Proposed)))

 

It adds new items just fine but keeps saying the following on existing items:

"The list item could not be added or updated because duplicate values were found in the following field(s) in the list [BPID]"

 

I am confused as surely it should be picking up a duplicate value exists as that is what I am trying to overwrite with the new data???

 

Any help would be much appreciated. 

17 REPLIES 17

Hi @TWolsten ,

I use something similar with new record selecting crew from a gallery based on a collection and then writing back to a SharePoint List. There will be other views on this, but I tend to keep code simple rather than trying to wrap multiple functions inside each other (also MUCH easier to debug), so firstly the new records. The reference to the collection Proposed is actually not needed as the data required is contained in the gallery and the ForAll on a gallery may be ambiguous if referring to something else. So for the new records, try this (works for me when all columns in the gallery line up  [name, type] with a field in the List).

ClearCollect(
   colNew,
   DropColumns(
      Filter(
         ChooseGallery.AllItems,
         New_x0020_Item_x003f_ = "Yes",
      ),
      "ID"
   )
);
Collect(
   bp_proposed,
   colNew
);
Clear(colNew)

 Now the existing items

ForAll(
   RenameColumns(
      Filter(
         ChooseGallery.AllItems,
         New_x0020_Item_x003f_ = "No"
      ),
      "ID",
      "IDRef"
   ),
   UpdateIf(
      bp_proposed,
      ID = IDRef,
      {
         Field1Name:Field1Name,
         Field2Name:Field2Name,
         Field3Name:Field3Name   //and so on for the rest
      }
   )
)

Please give all this a go and see how it works for you.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

View solution in original post

Hi @WarrenBelz , thanks so much - however the journey continues!

 

For new line items, ForAll(If(Gallery1.Selected.New_x0020_Item_x003f_ = "Yes", Proposed)) seems to do the trick so I am happy to keep it simple but thank you for the suggestion on putting it in a new Collection.

 

Your suggestion for editing an existing item works like a treat!

 

However, there is one final challenge.

 

My SharePoint List is over 50+ columns so to save time/keep the formula clean I would ideally like to write:

   UpdateIf(
      bp_proposed,
      ID = IDRef,
      {
         Proposed (i.e all columns in the SharePoint List)
      }
   )

 Similar to how the new item works?

 

Any ideas on this or am I going to be writing it out 50 times?

Hi @TWolsten ,

You only need to Patch the columns that have the potential to change - is this the whole 50 or just a few?

@WarrenBelz - it would be the entire 50 🙁

Hi @TWolsten ,

If you want to do a ForAll UpdateIf or Patch, then sorry you will have to specify them all if they could possibly contain changes.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi @TWolsten ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi @WarrenBelz 

 

Yes, your solution did work, just the manual entry of every column which is a pain.

 

So unless you have a wizzy way of including all columns (doesn't have to be a ForAll) 🙂

@TWolsten 

Love to help but unfortunately Patch is designed to be a manual process on update. Probably why I use SubmitForm whenever I can.

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 (2,102)