cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (34,242)