Answered

Patch to update selected records only updates one record

(0) Share

Report

Posted on by rbhurjee

Hello Community, hoping you guys can help I've been losing sleep over this!

In my canvas app I am trying to update a connected on-premise SQL table by selecting items in a BrowseGallery1 where CheckBox1.Value = true. This is a screenshot of my initial screen where i select the checkboxes:

BrowseScreen1

My BrowseScreen1 gallery is connected to my SQL db like this:

Filter(SortByColumns(Search('[dbo].[Packing_List_Header]',TextSearchBox1.Text,"User_Text3","Customer_Code","Customer_Name"),"Packing_List_Number",If(SortDescending1,Ascending,Descending)),Company_Code="bhurjeeindsq")

My next button goes to the next screen if something is selected:

If(Checkbox1.Value = true,Navigate(SignatureScreen1))

SignatureScreen1

On SignatureScreen1 we have a datatable to show what records will be updated and a button to submit and call the ForAll function:

ForAll(Filter(BrowseGallery1.AllItems,Checkbox1.Value= true ),Patch('[dbo].[Packing_List_Header]',LookUp('[dbo].[Packing_List_Header]',Packing_List_Header_ID=BrowseGallery1.Selected.Packing_List_Header_ID),{Picked_Up:Toggle1.Value,Pick_Up_Date:Now(),User_Text3:TextInputWaybill.Text}))

As you can see I am trying to update 3 columns in '[dbo].[Packing_List_Header]':

{
Picked_Up: Toggle1.Value,
Pick_Up_Date: Now(),
User_Text3: TextInputWaybill.Text
}

But of all the things I've tried, at best, I can only update 1 record. I've even tried using First(Filter(... but again only 1 row gets updated.

What, pray tell, am i doing wrong?

Categories:

Connecting to Data

All responses (6)

Answers (2)

Verified answer

rbhurjee on at

Like (0)

Report
Re: Patch to update selected records only updates one record

@Drrickryp you know I looked at this post before but never noticed the {ID:ID} in the example!

So I changed my Patch by removing Packing_List_Header_ID=BrowseGallery1.Selected.Packing_List_Header_ID)
and adding
{
Packing_List_Header_ID: Packing_List_Header_ID,
Picked_Up: Toggle1.Value,
Pick_Up_Date: Now(),
User_Text3: TextInputWaybill.Text
}
So the final Patch looks like below and IT WORKS!
ForAll( Filter( BrowseGallery1.AllItems, Checkbox1.Value = true ), Patch( '[dbo].[Packing_List_Header]', { Packing_List_Header_ID: Packing_List_Header_ID, Picked_Up: Toggle1.Value, Pick_Up_Date: Now(), User_Text3: TextInputWaybill.Text } ) );
Thank you all for for your help!
rbhurjee on at

Like (0)

Report

Re: Patch to update selected records only updates one record

@Pstork1 I thought that my ForAll function required me to only return a single record for each iteration...perhaps I am using it incorrectly.

Would you suggest using the First(Filter(... instead of a LookUp in this case?
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

Re: Patch to update selected records only updates one record

Hi All:

@Meneghino has a thread about patching multiple records that seems to be on point. https://powerusers.microsoft.com/t5/General-Discussion/Hot-to-Patch-to-update-multiple-records/td-p/51714
jhall 636 on at

Like (1)

Report
Re: Patch to update selected records only updates one record

I'm guessing the issue is here:
BrowseGallery1.Selected.Packing_List_Header_ID
You're checking multiple boxes, but only one of them is in the state of being "Selected". At least that's my guess.
Pstork1 66,004 Most Valuable Professional on at

Like (0)

Report

Re: Patch to update selected records only updates one record

I would have to study your formula more to be sure. But you are aware that Lookup only returns the first record it finds even if it finds multiples. ON the surface it looks like that is why you are only patching one record because you are actually only pointing

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.to one.
Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

Re: Patch to update selected records only updates one record

Hi @rbhurjee

Have you tried the UpdateIf() function? https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-update-updateif