cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Capt_Ron
Level: Powered On

Looping through a collection to patch a data source

I'm trying to patch a data source based on values in a collection and cannot figure out how to do it.

Here's my situation.  I have a web site that has 10 pages on it.  Each page has a message box where I want to post messages to.  The web site is database driven and I have a table for the page information.

The table (dbo.Pages) has ID (primary key), Title, and AlertHtml columns.

My PowerApp has dbo.Pages as a data source.

 

I  have a text box for a message, a list box for pages, and I have a gallery for styles (which gets prepended to the text box value)

 

I want to be able to post the message to any pages I select in the list box.

My initial thought was to put the Pages selected from the list box into a collection (colChosenPages) then loop through them and patch to dbo.Pages.  But there is NO LOOP function!  I looked at ForAll and Patch combinations but I don't understand how I use the matching criteria in Patch.

Here's what I was trying that does not work.

 

ForAll(colChosenPages,Patch('[dbo].[Pages]',Id=colChosenPages.Id,{AlertHtml:varMessageText})))

 

How can I add the message to the pages table for each of the pages chosen in the list box?

Thank you

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Looping through a collection to patch a data source

Hi @Capt_Ron ,

Do you want to modify several records at the same time?

I've made a similar test for your reference:

Set the listbox's OnChange:

ClearCollect(selecteditems,Filter('[dbo].[Pages]',ID in ListBox1.SelectedItems.ID))

Set the button's OnSelect:

ForAll(selecteditems,Patch('[dbo].[Pages]',LookUp('[dbo].[Pages]', ID = selecteditems[@ID]),{AlertHtml:varMessageText}))

Here's a doc about update multiple records for your reference:

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

 

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User
Super User

Re: Looping through a collection to patch a data source

Hey!

 

Please refer to the screenshot below: 

 

MicrosoftTeams-image (12).png

 

In this scenario we are patching new records into the db table, based on selected value in listbox and search text; 
and the gallery is displaying the results.
 
In my case, it will create new records, but if you want to update an existing one, then we need to replace the default(datasource) with the matching record, so that it gets updated. 
 
Also, What is the gallery doing in your case? Can you please share an example of what kind of data it is prepending to the text box? It would help in better understanding the whole scenario. 
 
Hope this Helped!
 
Thanks, 
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Capt_Ron
Level: Powered On

Re: Looping through a collection to patch a data source

@yashag2255 ,

Thank you.

So, I'm trying to do the same this you have in your response except I need to update the records.

In my case the gallery is actually irrelevant.  I just grab an attribute from the selected item and concatenate it in front of the text box Text value.  UpdateContext({varMessageText:Concatenate(Gallery1.Selected.Title,Textbox1.Text})

 

For simplicity, I want to: 

ForAll(Listbox1.SelectedItems,Patch('[dbo].[Pages]',***This is where I don't know what to do***,{AlertHtml:varMessageText})))

Hope this helps

Ron

Super User
Super User

Re: Looping through a collection to patch a data source

Hi, 

 

If you want to update an existing record in the table, then the below formula will work:
ForAll(Listbox1.SelectedItems,Patch('[dbo].[Pages]',Lookup('[dbo].[Pages]', <<condition>>),{AlertHtml:varMessageText})))
Lookup fetchs the first record that satisfies the condition so make sure the condition is applied in a way that only the required record is retrieved. 
 
Hope this helps!
 
Thanks, 
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Community Support Team
Community Support Team

Re: Looping through a collection to patch a data source

Hi @Capt_Ron ,

Do you want to modify several records at the same time?

I've made a similar test for your reference:

Set the listbox's OnChange:

ClearCollect(selecteditems,Filter('[dbo].[Pages]',ID in ListBox1.SelectedItems.ID))

Set the button's OnSelect:

ForAll(selecteditems,Patch('[dbo].[Pages]',LookUp('[dbo].[Pages]', ID = selecteditems[@ID]),{AlertHtml:varMessageText}))

Here's a doc about update multiple records for your reference:

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

 

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Capt_Ron
Level: Powered On

Re: Looping through a collection to patch a data source

For closure, I wanted to post my final code (test code) that works.

 

This is on the Save Button:

 

ClearCollect(SelectedPages,ListBox1.SelectedItems);

ForAll(SelectedPages,Patch('[dbo].[SSOPagesTest]',LookUp('[dbo].[SSOPagesTest]',pk_ID=SelectedPages[@pk_ID]),{HTMLAlert:Concatenate("Testing<|>",TextInput1.Text)}));

Refresh('[dbo].[SSOPagesTest]')

The key is the SelectedPages[@pk_ID] piece.

 

@v-yutliu-msft Thank you very much.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

Follow PowerApps on Twitter

Stay Up-to-Date by following PowerApps on Twitter

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 45 members 4,020 guests
Please welcome our newest community members: