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

How to Bulk Update different textinput values passed inside gallery and patch it to the SQL/Oracle server ?

Hello, 
I am new to Power Apps, 
I am trying to create an Excel-like editable gallery in Power Apps (as shown in the Screenshot),
PAPP COMM 1.PNG

 









When I try to enter three different scores for individual names (using Textinput input inside a vertical Gallery) and patch them to SQL server table using SAVE button, only one record (Score) is getting updated at a time in the SQL server table, I am unable to Patch multiple records (Scores) edited in the Gallery at a time using a Single BUTTON. 
Is it possible to update multiple records at a time with different values using a single BUTTON?
I have seen several articles but could not find an appropriate solution. Please help me with this. 

2 ACCEPTED SOLUTIONS

Accepted Solutions

@VenkataSai 

Thank-you. I should've also asked for the Items property of your gallery but I'll assume it is either a 'live' copy of your database OR a collection whose schema matches your database? If so, something like this should work:

 

ForAll(RenameColumns('EXCEL TYPE EDIT GALLERY'.AllItems,"ID","ID1"), 
   // here I've assumed you have a column called ID, adjust this to suit your needs 

         Patch('dbo.STUDENT_TBL',
           LookUp('dbo.STUDENT_TBL', ID = ID1), // adjust ID & ID1 to your needs as per above
           {SCORE:Value('TextInput1'.Text)}
         )
)

 

Let me know how this goes.

View solution in original post

@VenkataSai 

Your first image shows a Dropdown that filters the gallery, are you still using this? My thoughts are that if the gallery is filter first then apply the Patch() the function won't take a long time to run. Can you try this?

 

If this filtering isn't an option you want for your users then we will have to work on other gallery filtering that suits your needs.

View solution in original post

9 REPLIES 9
Eelman
Super User
Super User

@VenkataSai 

Can you please share your code on the 'Save' button

Hello @Eelman
Here is the code behind my SAVE button,
OnSelect: Patch('dbo.STUDENT_TBL','EXCEL TYPE EDIT GALLERY'.Selected,{SCORE:Value('TextInput1'.Text)})


@VenkataSai 

Thank-you. I should've also asked for the Items property of your gallery but I'll assume it is either a 'live' copy of your database OR a collection whose schema matches your database? If so, something like this should work:

 

ForAll(RenameColumns('EXCEL TYPE EDIT GALLERY'.AllItems,"ID","ID1"), 
   // here I've assumed you have a column called ID, adjust this to suit your needs 

         Patch('dbo.STUDENT_TBL',
           LookUp('dbo.STUDENT_TBL', ID = ID1), // adjust ID & ID1 to your needs as per above
           {SCORE:Value('TextInput1'.Text)}
         )
)

 

Let me know how this goes.

View solution in original post

Hello @Eelman
Thank You, This works great !!!
Its a live connection of Database 🙂 

No worries, happy to help 🙂 

Hello @Eelman,

The Code that you mentioned is getting the job done but looks like code is looping around for every record which includes records that are not being edited. This is taking more time than expected for the Power App to replicate the changes to the data source. Can you please help me in enhancing the code (looping process) that you have mentioned by patching only the edited items? 

Thank you.

@VenkataSai 

Your first image shows a Dropdown that filters the gallery, are you still using this? My thoughts are that if the gallery is filter first then apply the Patch() the function won't take a long time to run. Can you try this?

 

If this filtering isn't an option you want for your users then we will have to work on other gallery filtering that suits your needs.

View solution in original post

@Eelman,
Yes, I am filtering the Gallery first and I have many scores to edit (Let's say 500 records) after filtering the gallery. The code is looping for each filtered record in the gallery. For Instance, I have edited any 10 records of the 500 Rows of the filtered gallery, the code is looking up all the 500 records and Patching all the 500 records instead of only considering the 10 edited records)

Is there a way that we can First filter the table (SQL Server table) that we are going to patch to get the IDs of that 10 edited records and then patch those 10 edited records back to the source(SQL Server table) instead of looping all the 500 records? This would help us in enhancing App performance. 

@VenkataSai 

Ah, ok.

 

I was able to get something working at my end by adding a Toggle to the Gallery. Try this:

 

1. Add a Toggle to the Gallery

2. Set the Toggle Default to

If(TextInput1.Text=ThisItem.Score,false,true)

Notes: 

- TextInput1 is the input field you are adjusting the score inside your gallery

- ThisItem.Score, change 'Score' to match your DB column heading

- you can hide this Toggle when we are done, set the Visible property to false

 

3. Adjust your Patch code to this

ForAll(RenameColumns(Filter('EXCEL TYPE EDIT GALLERY'.AllItems,Toggle1.Value),"ID","ID1"), 
   // here I've assumed you have a column called ID, adjust this to suit your needs 

         Patch('dbo.STUDENT_TBL',
           LookUp('dbo.STUDENT_TBL', ID = ID1), // adjust ID & ID1 to your needs as per above
           {SCORE:Value('TextInput1'.Text)}
         )
)

Note: Toggle1 is the name of your Toggle so adjust as needed.

 

Let me know if this improves the performance?

 

 

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

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

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (64,058)