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:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (2)

Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

Hi @rbhurjee

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

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
jhall 636 on at

Like (1)

Report
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.

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

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

Was this reply helpful? Yes No
rbhurjee on at

Like (0)

Report

@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?

Was this reply helpful? Yes No
Verified answer

rbhurjee on at

Like (0)

Report
@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!

Was this reply helpful? Yes No