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

[Gustavo_C]

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})

[Gustavo_C]

Hello jKell. When I write directly to the server, without using a collection, I quickly get the error stating the number of server requests per unit of time has been exceeded. In this application a clinical investigator can review hundreds of studies in a matter of minutes. I am using Azure SQl database.

[WarrenBelz]

Hello @Gustavo_C ,

Please the code you have now as text

[Gustavo_C]

Hello Warren.

This is the one that gives me now errors, but it doesn't work either:

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}))))

[WarrenBelz]

Thanks @Gustavo_C ,

If you go back to my second post, there are two items you have not addressed noted below.

@Anonymous and I cannot see your data, so we have to rely on you a bit to put valid field and and control references in the code.

Once you a valid structure, then the code can be debugged.

The other thing you can try is breaking it down into components -

• Firstly do all the Patch options work by themselves? If not then they certainly will not work in your code.
• Instead of the Patch statements, put in Notify scripts Notify("Patch xxx would run in here") - that way you can see if all the options would run with your logic.

ForAll (
   FavCol, 
   LookUp (
      '[dbo].[Tbl_DQ]', 
       record = record         // this is ambiguous
   ); 
   If(
      IsBlank ('[dbo].[Tbl_DQ]'), 
      Patch(
         '[dbo].[Tbl_DQ]', 
         Defaults('[dbo].[Tbl_DQ]'), 
         {
            favorite:favorite, 
            id:id, 
            record:record, 
            nct_id:nct_id
          }, 
       Patch(
         xxxxx = yyyy,        //you need to specify the record you are patching here              
         '[dbo].[Tbl_DQ]',
         {
            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.

[Gustavo_C]

Warren, the issues you mention were addressed trying to use your code. Look at my post from one hour ago in reply to you message two days ago. Here its is again. I actually used the method suggested by you and @Anonymous . I attached the screenshot instead of text so you could see the errors I was getting.

[WarrenBelz]

@Gustavo_C Yes - but I was responding to the code you sent me in the last post.

Can we please confine this to one thread conversation. @Anonymous and I will work out who deals with it.

Also we are both trying to get you to understand the logic of the syntax so when you have to do another one, you will be able to.

[Gustavo_C]

Thanks Warren, no problem. I will keep it in one thread, and I appreciate the teaching approach. 

Regards.

[Anonymous · Accepted solution]

@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})
               )
)

[Gustavo_C]

Good morning @Anonymous and @WarrenBelz.

Eelman, the formula went smooth and without any errors since the beginning, it did what it was supposed to do. Thanks a lot to both for your help. I will study the code to see what was being done wrong on my side. 

Best regards.  

[Anonymous]

@Gustavo_C 

Great to hear we got there in the end. Let me know if you get stuck understanding how this code works and I will help explain it.

All the best, and thanks @WarrenBelz  for sticking strong throughout 🙂