Debugging UpdateIf function

(0) Share

Report

Posted on by MJK

109

Hi,

I have an update if function on Button click-

UpdateIf('[dbo].[PayrollHours]',EmployeeId in ApprovalDataNew.EmployeeId && WeekOf in ApprovalDataNew.WeekOf, {Approved:true});

Where ApprovalDataNew is a collection.

However the UpdateIf does not update the desired records in the PayrollHours table. Is there a way to debug and find out what the error is?

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Sort by

mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@MJK
What do you mean "doesn't update desired records"? Did it update some records you weren't expecting? Give actual examples please.

Then tell me: what column type is each column.

It would be great if you gave an example of what the collection looks like before the button press. And then also what it SHOULD look like after the button press.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
MJK 109 on at

Like (0)

Report
Copy link

Link copied!

hi @mdevaney ,

I mean that it doesn't update anything in the table (right or wrong).

ApprovalDataNew collection before button click-

After button click the values in the first column should change to "True".

Thanks.

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@MJK

Please try this to start. It corrects some of the errors in locating the correct record to patch

ForAll(
  ApprovalDataNew,

Patch(

  '[dbo].[PayrollHours]',

LookUp(

  '[dbo].[PayrollHours]',
EmployeeId =ApprovalDataNew[@EmployeeId] && WeekOf =ApprovalDataNew[@WeekOf]

),
{Approved:true}

);

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
MJK 109 on at

Like (0)

Report
Copy link

Link copied!

@mdevaney ,

I tried the above code however it updates only single record for each EmployeeId and WeekOf combination.

For example -
EmployeeId=5 && WeekOf='7/5/2020' has 7 records in the table(PayrollHours) but only the first one is getting updated. Same with the other two.

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @MJK :
Could you tell me how many records are in '[dbo].[PayrollHours]'?
I think the most likely reason is that the number of records in your data source is already greater than 500 or even greater than 2000. But UpdateIf is not delegable.
Therefore, the key to this problem is to bypass the delegation error.
Maybe you could try this code:

ForALL( RenameColumns( Ungroup( ForAll( ApprovalDataNew, {Value:Filter('[dbo].[PayrollHours]',EmployeeId =ApprovalDataNew[@EmployeeId] && WeekOf =ApprovalDataNew[@WeekOf])}), "Value"), "PrimaryKey", /*PrimaryKey is the primary key in your data base*/ "TheKey"), /*To avoid ambiguity, I renamed the primarykey to TheKey*/ Patch( '[dbo].[PayrollHours]', LookUp('[dbo].[PayrollHours]',PrimaryKey=TheKey), {Approved:true}) );

Best Regards,
Bof

Was this reply helpful? Yes No
MJK 109 on at

Like (0)

Report
Copy link

Link copied!

@v-bofeng-msft ,

No, there are only 123 records in my [dbo].[PayrollHours] table.

Also, does it mean that Patch will not work on large set of data, eventually [dbo].[PayrollHours] table will have more than hundred thousand data.

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!
@MJK
Is UPDATEIF allowed inside FORALL or does it give an error?

ForAll( ApprovalDataNew, Patch( '[dbo].[PayrollHours]', UpdateIf( '[dbo].[PayrollHours]', EmployeeId =ApprovalDataNew[@EmployeeId] && WeekOf =ApprovalDataNew[@WeekOf] ), {Approved:true} );

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
MJK 109 on at

Like (0)

Report
Copy link

Link copied!

@mdevaney ,
it gives an error.

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@MJK
I thought so... ok.

Was this reply helpful? Yes No