Patch to update selected records only updates one record

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

[Drrickryp]

Hi @rbhurjee 

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

[Pstork1]

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.

[jhall]

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.

[Drrickryp · Accepted solution]

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

[rbhurjee]

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

[rbhurjee · Accepted solution]

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