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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (66,802)