cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimJim
Helper V
Helper V

Patching a collection to a Sharepoint List

Hi Guys,

 

I have an edit form, after 'On Success' of the form I am trying to patch a collection to a Sharepoint List, I need to do 3 things:

 

1. Add records that don't exist in SP (This part is working correctly)
2. Update existing SP items where IsChanged in the collection = 1

3. Delete existing SP where IsDeleted in the collection = 1

 

I'm not sure how to complete for 2 and 3.

 

I am defining my collection like so:

 

 

 

ClearCollect(colProducts, Defaults('Requested Products'));

 

 

 

Here is my part completed patch function

 

 

 

ForAll(colProducts, 
// make sure we do not patch any rows with blank or default values
If('Product Family'<>"-" And 'Product Family'<>"",
// If item doesn't exist then patch new item to SP
If(IsBlank(ID),
Patch('Requested Products',
    {'Capex Id': frm_Fleet.LastSubmit.ID, 
    'Product Family': 'Product Family', 
    'Product Line': 'Product Line', 
    'Unit Cost': Value('Unit Cost'), 
    'Unit Qty': Value('Unit Qty'),
    'Line Cost': Value('Line Cost')}),
// If the item does exist and has been changed then update SP item   
   If(!IsBlank(ID) And IsChanged=1,
// ************ PUT CODE HERE TO UPDATE ITEM ************,
// If the item has been deleted from collection then deletefrom SP
    If(!IsBlank(ID) And IsDeleted=1,
// ************ PUT CODE HERE TO DELETE ITEM ************
)))))

 

 

 

Can someone please help me with the missing lines of code?

1 ACCEPTED SOLUTION

Accepted Solutions
PG_WorXz10
Community Champion
Community Champion

Hi @JimJim ,

 

May be something like below will work in your case ? 

 

ForAll(Filter(colProducts,IsBlank(ID)),Patch('Requested Products',Defaults('Requested Products'),
    {'Capex Id': frm_Fleet.LastSubmit.ID, 
    'Product Family': 'Product Family', 
    'Product Line': 'Product Line', 
    'Unit Cost': Value('Unit Cost'), 
    'Unit Qty': Value('Unit Qty'),
    'Line Cost': Value('Line Cost')}));
ForAll(Filter(colProducts,IsChanged=1),Patch('Requested Products',ThisRecord));
ForAll(Filter(colProducts,IsDeleted=1),Remove('Requested Products',ThisRecord));

 

 

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.

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

View solution in original post

5 REPLIES 5
PG_WorXz10
Community Champion
Community Champion

Hi @JimJim ,

 

May be something like below will work in your case ? 

 

ForAll(Filter(colProducts,IsBlank(ID)),Patch('Requested Products',Defaults('Requested Products'),
    {'Capex Id': frm_Fleet.LastSubmit.ID, 
    'Product Family': 'Product Family', 
    'Product Line': 'Product Line', 
    'Unit Cost': Value('Unit Cost'), 
    'Unit Qty': Value('Unit Qty'),
    'Line Cost': Value('Line Cost')}));
ForAll(Filter(colProducts,IsChanged=1),Patch('Requested Products',ThisRecord));
ForAll(Filter(colProducts,IsDeleted=1),Remove('Requested Products',ThisRecord));

 

 

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.

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

Nogueira1306
Super User
Super User

Okay... First of all: 

 

I would create the collection like:

 

 

ClearCollect(colProducts, DataSource);

 

 

Also, for the rest

 

To create new itens:

 

Patch('Requested Products',
      Defaults('Requested Products'),
    {'Capex Id': frm_Fleet.LastSubmit.ID, 
    'Product Family': 'Product Family', 
    'Product Line': 'Product Line', 
    'Unit Cost': Value('Unit Cost'), 
    'Unit Qty': Value('Unit Qty'),
    'Line Cost': Value('Line Cost')})

 

To edit:

 

Patch('Requested Products',
      IsChanged = 1,
    {'Capex Id': frm_Fleet.LastSubmit.ID, 
    'Product Family': 'Product Family', 
    'Product Line': 'Product Line', 
    'Unit Cost': Value('Unit Cost'), 
    'Unit Qty': Value('Unit Qty'),
    'Line Cost': Value('Line Cost')})

 

To delete:

 

Remove('Requested Products', ThisItem.ID)

 

All togheter:

 

ForAll(
If( isDelEted = 1,
Remove('Requested Products', ThisItem.ID),

If( isChanged = 1,
Patch('Requested Products',
      ThisItem,
    {'Capex Id': frm_Fleet.LastSubmit.ID, 
    'Product Family': 'Product Family', 
    'Product Line': 'Product Line', 
    'Unit Cost': Value('Unit Cost'), 
    'Unit Qty': Value('Unit Qty'),
    'Line Cost': Value('Line Cost')}),

Patch('Requested Products',
      Defaults('Requested Products'),
    {'Capex Id': frm_Fleet.LastSubmit.ID, 
    'Product Family': 'Product Family', 
    'Product Line': 'Product Line', 
    'Unit Cost': Value('Unit Cost'), 
    'Unit Qty': Value('Unit Qty'),
    'Line Cost': Value('Line Cost')})
)
)

 

I think that will work. The only thing that I don´t know is if "ThisItem" will work.. If not, use a lookup to find the item with that ID and it will work

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Hi @PG_WorXz10 ,

It works, thank you so much 😀

Hi @Nogueira1306 ,

This is very useful to know and I like this method, I do already have a solution but thank you for taking the time to reply

JR-BejeweledOne
Super User
Super User

To remove your items:  This is presuming that ID is your unique identifier that will match the item in the collection to the item in the list.

 

 

RemoveIf(
    'Requested Products',
       Filter(
            colProducts,
            Id = 'Requested Products'[@Id] && IsDeleted = 1
        )
 )

 

 

You should also be able to use UpdateIf  Or Patch in the same fashion for updating your items.

 

 

UpdateIf(
   'Requested Products',  
    Filter(
      colProducts, ID = 'Requested Products'[@ID] && IsChanged = 1,
   )
     {'Capex Id': frm_Fleet.LastSubmit.ID, 
    'Product Family': 'Product Family', 
    'Product Line': 'Product Line', 
    'Unit Cost': Value('Unit Cost'), 
    'Unit Qty': Value('Unit Qty'),
    'Line Cost': Value('Line Cost')}
  )

 

 

 Or

 

 

Patch(
  ForAll(
     Filter(colProducts, IsChanged = 1),
     {ID = ID,
    'Capex Id': frm_Fleet.LastSubmit.ID, 
    'Product Family': 'Product Family', 
    'Product Line': 'Product Line', 
    'Unit Cost': Value('Unit Cost'), 
    'Unit Qty': Value('Unit Qty'),
    'Line Cost': Value('Line Cost')}
  )
)

 

 



If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,907)