cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anonymia25
Helper IV
Helper IV

Patch and update existing record from Collection to SharePoint List

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 🙂 

1 ACCEPTED SOLUTION

Accepted Solutions

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  

View solution in original post

4 REPLIES 4
zmorek
Super User
Super User

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.

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:

anonymia25_2-1640139257258.png

 

 



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 🙂



Signature:


If you appreciated my comments/responses please be sure to Like/Kudo them it really does make me smile 🙂 !
Link to the Power Platform Professionals United Kingdom User Group:

https://powerusers.microsoft.com/t5/Power-Platform-Professionals/gh-p/PowerPlatformProfessionalsUnit...

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  

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,913)