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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

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

@Gustavo_C 

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), 
                        {favorite:favorite}),
          // else
                  Patch('[dbo].[Tbl_DQ]', Defaults('[dbo].[Tbl_DQ]'),
                        {favorite:favorite, id:id, record:record1, nct_id:nct_id})
               )
)

 

 

 

View solution in original post

21 REPLIES 21
Highlighted
Community Champion
Community Champion

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

Hello @Gustavo_C ,

The problem seems to lie in the last Patch

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

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.

Highlighted
Community Champion
Community Champion

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

@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?

ForAll_EndsEarly.jpg

 

Highlighted
Frequent Visitor

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

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? 

Highlighted
Community Champion
Community Champion

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

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

ForAll(
   FavCol, 
      If(
         IsBlank(
            LookUp(
               '[dbo].[Tbl_DQ]', 
                record = FavCol.record  //matching fields in db and oollecton
            )
         )
      ),
      Patch(
         '[dbo].[Tbl_DQ]', 
          Defaults('[dbo].[Tbl_DQ]'), 
          {
             favorite:favorite, 
             id:id, 
             record:record, 
             nct_id:nct_id
          }
      ),
      Patch(
         '[dbo].[Tbl_DQ]',
         record = FavCol.record,  // ***** ADDED
         {favorite:favorite}
      )
   )
)

 

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.

Highlighted
Community Champion
Community Champion

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

@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:

 

ForAll(
       RenameColumns(
          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));
               If(IsBlank(Value),
                  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 🙂 

Highlighted
Advocate III
Advocate III

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

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

If(
//CHECK IF ITEM EXISTS
IsBlank(LookUp(DatasourceForFavorites, Condition)) ,
//IF ITEM DOESN'T EXIST, Add it
Patch(DatasourceForFavorites, Defaults, Value)//add item as Favorite,
//IF ITEM DOES EXIST, Remove it
Remove(DatasourceForFavorites,LookUp(DatasourceForFavorites, Condition))//remove item )

 

 

Highlighted
Community Champion
Community Champion

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

@jKell 

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.

Highlighted
Frequent Visitor

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

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. 

 

 

 

 
 
Highlighted
Frequent Visitor

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

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

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,924)