cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

UpdateIf funtion is not modifying set of records

Hello Everyone,

 

I am facing problem with the UpdateIf command not doing anything even it doesn't throw any error when running following command:

 

Button - On Select: UpdateIf('[dbo].[TimeSheet_Entries_Test]'; USERNAME = TextBox26.Text && MONTH= TextBox22.Text && YEAR=TextBox25.Text;{Status:"Confirmed"})

 

Once i click on the button, it should select all the entries which satisfies the condition in 2nd argument and then update the Status column to "Confirmed" of those filtered rows.

 

But seems like UpdateIf is not working, i have done CountRows for 2nd argument in the formula and it has given 27 rows.

Does UpdateIf funtion modifies multiple records by using the above formula?

 

Kind regards,

Naga

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Level 10

Re: UpdateIf funtion is not modifying set of records

Apologies, yes I forgot that probably UpdateIf is not delegated (strange that it should not be, but there you go)

Ok, then the way to do it is with a Patch, something like this:

 

 

ClearCollect(TempCollection, Filter('[dbo].[TimeSheet_Entries_Test]', DAY = Label5_3.Text && USERNAME =CurrentUser.FullName && MONTH= Label22.Text));
Patch( '[dbo].[TimeSheet_Entries_Test]', TempCollection, RenameColumns(ShowColumns(AddColumns(TempCollection, "NewStatus", "Confirmed"),"NewStatus"),"NewStatus", "Status") )

 

Please note that for your locale you will probably need to use ; instead of , and ;; instead of ;

7 REPLIES 7
Meneghino
Level 10

Re: UpdateIf funtion is not modifying set of records

Just did a quick test and UpdateIf works as expected with Azure SQL Database data source.

I used the same sort of syntax you do.

Just on thought, maybe an issue with case sensitivity of the comparisons?  January is not the same as january in Powerapps

Re: UpdateIf funtion is not modifying set of records

Hello @Meneghino

 

Now i am thinking the possible failure of the UpdateIf function is due to more than 500 records in my table and i am also trying to update the rows which are at ~900th rows.

 

So, is there any way to get rid of this 500 limitation in UpdateIf function?

Seems like Filter function inside the formula is not working as it only accepts boolean result:

 

UpdateIf('[dbo].[TimeSheet_Entries_Test]';Filter('[dbo].[TimeSheet_Entries_Test]'; DAY = Label5_3.Text && USERNAME =CurrentUser.FullName && MONTH= Label22.Text) ;{Status:"Confirmed"})

 

Kind regards,

Naga

 

Meneghino
Level 10

Re: UpdateIf funtion is not modifying set of records

Apologies, yes I forgot that probably UpdateIf is not delegated (strange that it should not be, but there you go)

Ok, then the way to do it is with a Patch, something like this:

 

 

ClearCollect(TempCollection, Filter('[dbo].[TimeSheet_Entries_Test]', DAY = Label5_3.Text && USERNAME =CurrentUser.FullName && MONTH= Label22.Text));
Patch( '[dbo].[TimeSheet_Entries_Test]', TempCollection, RenameColumns(ShowColumns(AddColumns(TempCollection, "NewStatus", "Confirmed"),"NewStatus"),"NewStatus", "Status") )

 

Please note that for your locale you will probably need to use ; instead of , and ;; instead of ;

Re: UpdateIf funtion is not modifying set of records

Hello @Meneghino

 

Your formula worked like a gem but it is throwing an error after updating 26 out of 27 'Pending' to 'Confirmed' values for Status column in my database:

 

An error occured on the server:

Server Response: Microsoft SQL Server: Resource ID: 1. The request limit for the database is 30 and has been reached.

 

Do you think we can lower the intensity of the Patch command on the Database like splitting the into 2-3 phases?

 

Kind regards,

Naga

Meneghino
Level 10

Re: UpdateIf funtion is not modifying set of records

Yes, you can try that.

This what I know from this blog:

 

Limits

When deploying your app to a large number of user, one has to keep in mind the connectors are throttled. PowerApps currently support 600 requests per minute per user and up to 30 concurrent calls per user. 

Re: UpdateIf funtion is not modifying set of records

Thanks @Meneghino for directing me to the blog,

 

I have understood database error is due to Gallery list is directly binded to Database table, so it is updating the values in database table and also the application is making calls to database for the Gallery list which is present in the screen. I have now used Collections for gallery to overcome the database error.

 

Kind regards,

Naga

Meneghino
Level 10

Re: UpdateIf funtion is not modifying set of records

You are welcome, please check out my blog for more Azure SQL Database related posts.