Answered

ForAll Patch Statements Skipping Records

(0) Share

Report

Posted on by mstrawn

Hello,

In my app I am working with large datasets, specifically where one record can be associated with 10+ records in another table. It is a many-to-many relationship in SQL, so I am using a Junction (or link) table to solve this. For example, if a record of ID=1 is associated with 10 other records of IDs 1-10, there would be ten entries in that table, like:
1 1

1 2

1 10

I am doing this by looping over the combobox where the 10 records are, and then using a ForAll and nested patch to insert the 10 rows into my table. Like this:

My issue is that sometimes, like 1 out of 5, the ForAll and patches will just not fully work. They will only insert 7 out of 10 records, or other ones won't work. I am really curious why. I have spent days looking in the Monitor tool, and I can't figure it out. It is like it just randomly skips over some data. Is there a lag/size limit?

Any thoughts are appreciated,

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@mstrawn

I would first start with using the ForAll properly in the formula. You are trying to use it like a For Loop...which it is not. It is a function that returns a table. It just so happens that Patch accepts a table of records to patch.

So your formula should only be the following:

Concurrent( Patch(DetectionToolToRule, ForAll(ercbDetectTool.SelectedItems, {DetectionTool_id: key_id, Rule_id: Value(ertxtRule_key_id.Text) } ) ), Patch(ResponseActionToRule, ForAll(ercbResponseAction.SelectedItems, {ResponseAction_id: key_id, Rule_id: Value(ertxtRule_key_id.Text) } ) ), Patch(ScopeToRule, ForAll(ercbScope.SelectedItems, {Scope_id: key_id, Rule_id: Value(ertxtRule_key_id.Text) } ) ) )

When you use a Patch in a ForAll like a for loop, Patch has to instantiate itself on each iteration of the for all table record. This is a huge overhead and leads to horrible performance. With the above, Patch is instantiated once and provided a table of records to patch. Since you are not supplying a primary key to the patch, it is smart enough to know to create the record.

I point this out because your scenario seems to indicate a lag in performance that potentially is causing the issue on the database side. So, put the above in place and at least eliminate the performance factor.

I hope this is helpful for you.

Was this reply helpful? Yes No
mstrawn 38 on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes
This does make sense, thank you for the explanation.
When implementing, I am getting this error:

It's almost like it is not trying to create new records, which it should be?

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@mstrawn

Sorry for delay in this and I believe we solved this in the other thread...it is because those columns are primary keys...they should not be or Patch will try to update them.

Was this reply helpful? Yes No