Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Help with: ForAll items in a collection Patch in SQL table If record doesn’t exist, else UPDATE



Hello. I tried the suggested articled in this post to solve my problem with no luck. So if someone here can give me a hand it will be greatly appreciated.

I have a gallery that lists basic information of a long list of clinical research papers. The idea is to allow the user to look at what is available and mark for future review those that catch their attention. This is achieved by clicking on a star icon that OnSelect takes the information of that paper and adds it to a collection called FavCol.  The collection has several columns two of which are “record”(the unique identifier) and “favorite” that will toggle between  “Yes”or “No” each time the user clicks the star icon.

Once the user has reviewed the gallery they press a button that will patch the collection into an SQL database (dbo.Table_DQ) only if those records don’t already exist, if they do exist, only the “record” column will be updated.

I have used several variations of this code with no success. Any help will be appreciated. Best regards.


ForAll (FavCol, (LookUp ('[dbo].[Tbl_DQ]', record = record))); If (IsBlank ('[dbo].[Tbl_DQ]'), Patch('[dbo].[Tbl_DQ]', Defaults('[dbo].[Tbl_DQ]'), {favorite:favorite, id:id, record:record, nct_id:nct_id}), Patch('[dbo].[Tbl_DQ]', {favorite:favorite})


Accepted Solutions


I think your formula structure still needs some work, try this:

ForAll( RenameColumns(FavCol, "record", "record1"),
          If(!IsBlank(LookUp('[dbo].[Tbl_DQ]', record = record1)),
                  Patch('[dbo].[Tbl_DQ]', LookUp('[dbo].[Tbl_DQ]', record = record1), 
          // else
                  Patch('[dbo].[Tbl_DQ]', Defaults('[dbo].[Tbl_DQ]'),
                        {favorite:favorite, id:id, record:record1, nct_id:nct_id})




View solution in original post

Super User III
Super User III

Hello @Gustavo_C ,

The problem seems to lie in the last Patch


You need to specify which record you are Patching in the place of Defaults(datasource) for a new record like 

id=the id you know


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.

Super User
Super User

@Gustavo_C , @WarrenBelz is definitely onto something with his comment.


The other thing I noticed is that your formula structure seems off? In the pic below your ForAll() ends at 1 and your If() at 2, this doesn't seem to be correct - or maybe it is?



Thanks @WarrenBelz  and @Eelman for your help so far. I made the corrections suggested by Eelman and the errors I had in the formula went away, so no red lines. The issue is that it doesn't patch or update records.


It now looks like this: 

ForAll (FavCol, (LookUp ('[dbo].[Tbl_DQ]', record = record)); If (IsBlank ('[dbo].[Tbl_DQ]'), Patch('[dbo].[Tbl_DQ]', Defaults('[dbo].[Tbl_DQ]'), {favorite:favorite, id:id, record:record, nct_id:nct_id}, Patch('[dbo].[Tbl_DQ]', {favorite:favorite}))))


Now, I am just learning here so forgive me if I say an atrocity. This is the way I understand the code.



ForAll (FavCol, (LookUp ('[dbo].[Tbl_DQ]', record = record)); // This part will evaluate every record in my collection against the SQL I am writing to ('[dbo].[Tbl_DQ]') and give a list of those items present in the collection and in the database.


If (IsBlank ('[dbo].[Tbl_DQ]'), Patch('[dbo].[Tbl_DQ]', Defaults('[dbo].[Tbl_DQ]' , {favorite:favorite, id:id, record:record, nct_id:nct_id}), // This part will evaluate the first condition of IF which is, if there is no record in '[dbo].[Tbl_DQ]' with a "record" field matching any of those in the collection Patch will write a new one


Patch('[dbo].[Tbl_DQ]', {favorite:favorite}))))// This would be the ELSE part of the IF which is the action that would be applied to any record that already exists in the source.


@WarrenBelz  What do you mean when you say that the record needs to be identified? 

Super User III
Super User III

Hi @Gustavo_C ,
Try this - I could not test so make sure all brackets and commas are there.

                record = FavCol.record  //matching fields in db and oollecton
         record = FavCol.record,  // ***** ADDED


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.

@Gustavo_C it's good to see you've got rid of those pesky red lines! Nothing worse, haha


First suggestion I'd make is that you may be getting an error from 'record = record'. The way I usually deal with these is to use RenameColumns, like this:


          FavCol,"record", "record1"),   // here I rename column 'record' to 'record1'
          (LookUp ('[dbo].[Tbl_DQ]', record = record1));...   // here I reference 'record1'


Next, I think (not sure) that:


(LookUp ('[dbo].[Tbl_DQ]', record = record1))


 creates a 'Value' you can reference further in your code, so that your IsBlank becomes:


If(IsBlank(Value), Patch(...


And as @WarrenBelz suggested, you need to reference a record for the last part of your Patch and you may be able to use 'Value' here, like this:


Patch('[dbo].[Tbl_DQ]', Value, {favorite:favorite})



Putting all the above together gives you this (note: I haven't tested this so you may need to tinker a bit?):


ForAll( RenameColumns(FavCol, "record", "record1"),
        (LookUp ('[dbo].[Tbl_DQ]', record = record1));
                  Patch('[dbo].[Tbl_DQ]', Defaults('[dbo].[Tbl_DQ]'),
                  {favorite:favorite, id:id, record:record1, nct_id:nct_id}),  // you had a ')' missing here
                 // else
                 Patch('[dbo].[Tbl_DQ]', Value, {favorite:favorite})



If this doesn't work and 'Value' isn't available to select, you could just remove the LookUp() at the start and replace the 2 places where 'Value' appears in my solution with the removed LookUp() formula ... maybe 🙂 


Like I said, I've not tested this but let me know how you go.



EDIT: Whoops @WarrenBelz replying at the same time 🙂 

Advocate III
Advocate III

I have used favorite functionality in the past. Is there a reason why you are not Patching on the Star button itself?


Your description sounds like user must:

  1. Select which item to favorite (and they can select multiple)
  2. Then you batch process all favorites to favorites list

Why not put the Patch action on the selector so that OnSelect, you can patch the value to the favorite column?

This way you can avoid the ForAll() and changes are made instantly as opposed to needing to process.


I am suggesting:

  1. Make the user interaction of selecting the Favorite run your Patch Statement

On the OnSelect of your Favorite icon you can have 1 if statement that Toggles the favorite

IsBlank(LookUp(DatasourceForFavorites, Condition)) ,
Patch(DatasourceForFavorites, Defaults, Value)//add item as Favorite,
Remove(DatasourceForFavorites,LookUp(DatasourceForFavorites, Condition))//remove item )




There are many solutions to any problem - @Eelman and myself try to stay within the structure of the code given and make it valid - it makes a smaller journey for the user to take an in particular understand for future usage.

@Gustavo_C , @Eelman and myself can both solve this for you - please tag which whichever path is best for you to follow.

Hello Warren, last night I tried to make this work following your suggestions and @Eelman. This is how it looks like with the code you suggested:



Screenshot 2020-03-04 20.44.34.png

I reviewed the parenthesis and comas and they seem to be fine. I made several changes back and forth and couldn't make it work.

Any other suggestion, no matter how different from my rookie code, will be appreciated. 





Hello Eelman. Thanks for your suggestion, same as with Warren's I tried to make it work, using Value or the LookUp statement, to no avail. Here is how it looked like when I used the Value. 


Screenshot 2020-03-04 21.11.07.png


I checked for parenthesis and other obvious errors but couldn't identify anything. Grrrr. 


Any suggestions please let me know. Thanks for your help so far with this.

Helpful resources

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.


Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (5,746)