Patching a collection to a Sharepoint List

[JimJim]

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?

[PG_WorXz10 · Accepted solution]

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.

[Nogueira1306]

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)!

[JimJim]

Hi @PG_WorXz10 ,

It works, thank you so much 😀

[JimJim]

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]

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')}
  )
)