Answered

Patch and update existing record from Collection to SharePoint List

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

zmorek 3,272 on at

Like
a
(1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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:

Was this reply helpful? Yes No
AJ_Z 3,711 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
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 🙂

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Lynn626 20 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No