cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GOBrien11
Frequent Visitor

Patching multiple records from a Galley with GroupBy

Hi all,

 

This community has solved so many problems I've had with Powerapps, thank you all.  I'm loving Powerapps!!

 

My problem is this:

 

I have a gallery which has a SQL data source that is grouped using GroupBy,

Looks like this: GroupBy(Filter('[dbo].[CTMaster]',Status=3,Railcar<>""),"CTMasterRecord","Railcar","Location","Status","RailCar#")

 

I have a Submit button in the gallery item that I'd like the OnSelect action to patch every record grouped into each grouping,

 

Example:

 

Gallery item 1 has Railcar 1111, Location A, Status 3.  When I hit the Submit button, I would like each record in my SQL database that has the railcar field populated with 1111 to update with the patch command to a value I specify in another box somewhere.  I'm thinking I have to use ForAll, but I can't figure out the syntax.

 

Maybe I have to add the items to a collection first, not sure.

 

Would love some help on this.

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

Hi @GOBrien11 ,

You need to specify the criteria in an UpdateIf() command, so something like this with the item selected in the gallery.

UpdateIf(
   '[dbo].[CTMaster]',
   Railcar=YourGalleryName.Selected.Railcar,
   {YourOtherFieldName:YourRequiredValue}
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

4 REPLIES 4
WarrenBelz
Super User III
Super User III

Hi @GOBrien11 ,

You need to specify the criteria in an UpdateIf() command, so something like this with the item selected in the gallery.

UpdateIf(
   '[dbo].[CTMaster]',
   Railcar=YourGalleryName.Selected.Railcar,
   {YourOtherFieldName:YourRequiredValue}
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

That was absolutely the answer.  How did I miss the UpdateIf command!!  Thank you so much

 

Follow up question - now I want to create a record for each of those rows that were updated into another table, that I'm using for history.

 

I normally pass the parameters through a flow to a stored procedure I've created.  How would I pass the values from each of those rows.

 

Before you answered the first question - I achieved what I wanted to by way of stored procedure, but I'd much rather do it all through power apps, and not have to process through flows if I don't have to.

 

I appreciate your help!!

Thanks @GOBrien11,

Without testing this, you could do a collection

ClearCollect(
   YourColName,
   DropColumns(
      Filter(
         YourGalleryName.AllItems,
         Railcar=YourGalleryName.Selected.Railcar,
      ),
      "RailCar#"
   )
)

This will get you a collection with all the fields you grouped by and (hopefully) dropping the table with the rest of the fields.

If all the fields line up in name and type with the target, you could try

Collect('[dbo].[CTMaster]',YourColName)

I am a SharePoint user, so if this does not work on SQL, a ForAll Patch should. You would also not need the DropColumns on this.

ForAll(
   RenameColumns(
      YourColName,
      "CTMasterRecord", "CTM",
      "Railcar", "Car",    
      "Location", "Loc",
      "Status", "Stat"
   ),     
   Patch(
      '[dbo].[CTMaster]',
      Defaults('[dbo].[CTMaster]'),
      {
         "CTMasterRecord":"CTM",
         "Railcar":"Car",      
         "Location":"Loc",
         "Status":"Stat"
      }
   )
)     

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

This is excellent.  Thank you so much for the help

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,952)