cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TesDA
Continued Contributor
Continued Contributor

Gallery ForAll() bulk patch checkbox group to sql

I have a Gallery, with checkboxes on each row consisting of projects. Above the gallery is a standalone checkbox outside of the gallery so that people can select all of the checkboxes.

 

Once people have checked all of the boxes of the projects they would like to include in their data, I'd like to bulk send the data to sql. Each project they select should be a new line in the data. This is a different table in sql, not the one used as the datasource of the gallery. The UpdateIf() I created to do this is creating an error:

 

UpdateIf('[dbo].[App_PAFRes_Proj]', ProjSelect_CheckBox.Value = true, {EntryUser: "US"+Office365Users.MyProfile().MailNickname, EntryTimestamp: Today(), MainID: varRecord, 'Project ID':CloneProjectGallery.AllItems.Project})

 

The 'Project ID' column in the data source you're updating expects a 'Text' type and you're using a 'Table' type.

The function 'UpdateIf' has some invalid arguments.

 

Gallery:

CloneProjectGallery

Items: Filter('[dbo].[vps_App_PAFResources_Proj]', varProj = 'Level 3 Project')

 

Icon (to move to next screen)

OnSelect: UpdateIf('[dbo].[App_PAFRes_Proj]', ProjSelect_CheckBox.Value = true, {EntryUser: "US"+Office365Users.MyProfile().MailNickname, EntryTimestamp: Today(), MainID: varRecord, 'Project ID':CloneProjectGallery.AllItems.Project})

GalleryCheckBoxGroup.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
TesDA
Continued Contributor
Continued Contributor

I ended up going in a totally different direction after reading that the ForAll() may not work well for alot of data (max ~350 rows). I also dont think my end users will wait the few minutes it would take to patch that many rows to sql.

 

1) I used the video from Shane Young, Select multiple records in a gallery and Patch with the PowerApps ForAll function

 Basically from this video I was able to create a collection which moves alot faster then patching a forall() or updateif() back and forth from SQL - basically everything updates in the collection right in the app

2) Once the end user has finalized which projects they want to include (checked all of the boxes they want) the arrow to the next screen will set off a flow which will use JSON to send the data to SQL. I used a blog from Rajeev Pentyala to do this.

(#2 is easier then it sounds, Rajeev did a good job of making it relatively simple).

The flow kicks of very quickly and then operates behind the scenes, so the end user doesn't have to sit and wait for a patch or forall to process to SQL.

View solution in original post

2 REPLIES 2
RandyHayes
Super User
Super User

@TesDA 

Consider this for your Formula:

ForAll(
    Filter(CloneProjectGallery.AllItems., ProjSelect_CheckBox.Value),

    UpdateIf('[dbo].[App_PAFRes_Proj]', ID=ThisRecord.ID, 
        {EntryUser: "US"+Office365Users.MyProfile().MailNickname, 
         EntryTimestamp: Today(), 
         MainID: varRecord, 
        'Project ID': Project
        }
    )
)

 

The one thing to pay attention to (since I don't know your data), you need to substitute ID=ThisRecord.ID with something that will uniquely identify the record in your datasource that you want to update.

Also, not sure on your Project ID, but this is the general construct of what you want, please substitute your information as necessary.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
TesDA
Continued Contributor
Continued Contributor

I ended up going in a totally different direction after reading that the ForAll() may not work well for alot of data (max ~350 rows). I also dont think my end users will wait the few minutes it would take to patch that many rows to sql.

 

1) I used the video from Shane Young, Select multiple records in a gallery and Patch with the PowerApps ForAll function

 Basically from this video I was able to create a collection which moves alot faster then patching a forall() or updateif() back and forth from SQL - basically everything updates in the collection right in the app

2) Once the end user has finalized which projects they want to include (checked all of the boxes they want) the arrow to the next screen will set off a flow which will use JSON to send the data to SQL. I used a blog from Rajeev Pentyala to do this.

(#2 is easier then it sounds, Rajeev did a good job of making it relatively simple).

The flow kicks of very quickly and then operates behind the scenes, so the end user doesn't have to sit and wait for a patch or forall to process to SQL.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,323)