cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gustavo_C
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})

21 REPLIES 21

Thanks Eelman. Now that I see the formula, I understand the logical order of the statement and it makes a lot of sense. 

a) ForAll defines the scope of the formula (will apply to every record). So it has to go first.

b) RenameColumns: presents the formula with a modified, more usable version of the underlying data without actually changing it. It has to go next because this will be used by the rest of the formula. 

c) IF and LookUp separate the records that will undergo one or other action based on a condition

d) Patch with second LookUp completes an action on specific records that are defined by this second LookUp

e) Path with Defaults, just creates a new record when the IF condition is not satisfied. 

 

Thanks again

 

 

 

 

Anonymous
Not applicable

@Gustavo_C 

Yep, that's it.

 

Just on your Point b), I try to use the construct ForAll( RenameColumns (Dbase, "ID", "ID1") ... whenever there are matching column names in the two datasets being compared. It just helps avoid writing disambiguation code, which can be a little tricky, at least for my old brain, haha

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,030)