Patch and update existing record from Collection to SharePoint List

[Anonymous]

Hello,

I am having trouble patching a collection into my SharePoint list. Apparently it creates a new record instead of updating the previous record.
The idea is I wanted to collect the related data into a collection, patch the information using a gallery/editable table.
So far I have created a filtered collection OnStart.

OnStart:
ClearCollect(
DeptUpdates,
Filter('Department KPI', HeaderID = KPIHeader.ID)
);

I made a gallery with the collection as data source with added toggles to detect changes in the record. (Not sure if it's the best for this since I am using editable table concept) or I should just use the OnChange properties?
Here is the Patch function to save into SP.   

ForAll(
Filter(
'Gallery1.1'.AllItems,
togUpdate.Value
) As ChangedRows,
Patch(
DeptUpdates,
Defaults(DeptUpdates),
{
ID: ChangedRows.ID,
HeaderID: CurrentUserID,
KRA: ChangedRows.KRA_TxtInput.Text,
KPI: ChangedRows.KPI_TxtInput_1.Text,
Low: ChangedRows.Low_TxtInput_2.Text,
Base: ChangedRows.Base_TxtInput_3.Text,
Stretch: ChangedRows.Stretch_TxtInput_4.Text,
Weight: Value(ChangedRows.Weight_TxtInput_5.Text)
}
)
);

Patch(
'Department KPI',
DeptUpdates
);

 
Would appreciate the help/tips for this! Thank you 🙂 

[zmorek]

I believe the reason you're getting new rows instead of updating existing ones is because of the "Defaults(DeptUpdates)" portion of your formula; plus I don't believe you need a collection, toggles, or OnChange to accomplish this - you're just creating more overhead. Perhaps you can try using 'Department KPI' as the data source directly on the gallery, then your formula will be something like:

Patch(
'Department KPI',
ForAll ('Gallery1.1'.AllItems,
{
ID: ID,
HeaderID: CurrentUserID,
KRA: KRA_TxtInput.Text,
KPI: KPI_TxtInput_1.Text,
Low: Low_TxtInput_2.Text,
Base: Base_TxtInput_3.Text,
Stretch: Stretch_TxtInput_4.Text,
Weight: Value(Weight_TxtInput_5.Text)
}
)
);

Please let me know if I misunderstood; and please check my syntax/your field names, as I free-typed this.

[Anonymous]

Yes I tried patching it directly to the list in SharePoint but since I am using gallery to edit the data (as an editable table), apparently when I delete or add new item it will remove the ones that are still in edit mode. So I am thinking to create a collection instead and add/delete/edit the collection in the app and finally patch the items when user click Save on Draft or Submit button.

Here a screenshot of my editable table:

[AJ_Z]

surely the id wouldn't change and would be unique right? so you would be okay to do this right?

ForAll(
Filter(
'Gallery1.1'.AllItems,
togUpdate.Value
) As ChangedRows,
If(IsBlank(LookUp('Department KPI',ID = ChangedRows.ID)),
Patch(
'Department KPI',
Defaults('Department KPI'),
{
ID: ChangedRows.ID,
HeaderID: CurrentUserID,
KRA: ChangedRows.KRA_TxtInput.Text,
KPI: ChangedRows.KPI_TxtInput_1.Text,
Low: ChangedRows.Low_TxtInput_2.Text,
Base: ChangedRows.Base_TxtInput_3.Text,
Stretch: ChangedRows.Stretch_TxtInput_4.Text,
Weight: Value(ChangedRows.Weight_TxtInput_5.Text)
}
),
Patch(
'Department KPI',
LookUp('Department KPI',ID = ChangedRows.ID),
{
ID: ChangedRows.ID,
HeaderID: CurrentUserID,
KRA: ChangedRows.KRA_TxtInput.Text,
KPI: ChangedRows.KPI_TxtInput_1.Text,
Low: ChangedRows.Low_TxtInput_2.Text,
Base: ChangedRows.Base_TxtInput_3.Text,
Stretch: ChangedRows.Stretch_TxtInput_4.Text,
Weight: Value(ChangedRows.Weight_TxtInput_5.Text)
}
)
));

let me know if i have misunderstood also the collection idea you suggested does sound awesome as well 🙂

[Anonymous · Accepted solution]

Hi @AJ_Z !
And yes, collection does solve my issue and help improve performance as well! So I went ahead and did those. 😄
I made three collection one for add new item, delete and the main one straight from the SP list. There is no need for toggle now, I only used onChange to detect the changes. 

Here is my final patch:

Patch(
    'Department Shared KPI',
    UpdateIf(DeptUpdates, Created = Blank(), {ID:Blank()}));

Remove('Department Shared KPI', colDeleteDept);
Clear(colDeleteDept);

Thank you for the help! :)) 
Also I found a video that help me tremendously  - https://www.youtube.com/watch?v=wI6SHGQ9ATg  

[Lynn626]

Hi There,

I have a similar issue where I'm trying to update existing record using col collect function, but getting new records created at the same time using the code below.  I added the lookup statement, but it's giving me an error, any idea what is wrong with the code?

If(
varGridEdit,
Patch(
PSRA_Events_Response_Form,
LookUp(PSRA_Events_Response_Form,ID=galID),
colGridData
));

Notify(
"Form Data Updated",
NotificationType.Success,
2000
);
Select(btnLoadData);
Set(
varGridEdit,
!varGridEdit
)

Thank you in advance for your help.