ForAll and Patch affect only the first matched row

[shibathedog]

Hi,

I need help because I am having a hard time finding the issue, I tried all solutions that popped up to my noob mind but they didn't work.

I have more than 3K rows so I use the collection "MyCol" to add up all the rows from the SP list.

• The following code should update the collection "MyCol" column Sold - this works
• then Collect all of the Non blank values in Sold column for the selected Gallery item into a temporary collection "TempCol" - this also works
• and in the third step copy the entries from the temporary collection the the SP list - this only updates the first matched row in the SP list and nothing further.

UpdateIf(MyCol,
Unit = MyGallery.Selected.Title1.Text,
{Sold: "Yes" };

ClearCollect(TempCol,
Filter(MyCol, Unit = MyGallery.Selected.Title1.Text, !IsBlank(Sold))) ;

ForAll(TempCol.Unit, Patch(SalesTable, Unit = MyGallery.Selected.Title1.Text, {Sold: "Yes" }))

If anyone has an idea, I would be more than grateful!

Thanks!

[RandyHayes · Accepted solution]

@shibathedog 

Please consider changing your Formula to the following:

UpdateIf(MyCol, Unit = MyGallery.Selected.Title1.Text,
   {Sold: "Yes" }
);

Patch(SalesTable, 
    ForAll(Filter(MyCol, Unit = MyGallery.Selected.Title1.Text && !IsBlank(Sold)),
        {ID: ID,
         Sold: "Yes"
        }
    )
)

You might want to be more specific in the filter for Sold - instead of !IsBlank, perhaps Sold="Yes", as that appears to be what you are setting the column value to.

I hope this is helpful for you.

[shibathedog]

@RandyHayes As always, you are on spot! Thank you very much, you're such a lifesaver! 

I took your comment into consideration and swapped !IsBlank with =, you are right, it's more precise!

And a question? Why don't we need to use a "middle" collection? Patch doesn't have the same delegation issue as UpdateIf I presume so it can access/query the rows after 2999? (That's actually why I've been trying to use the temporary collection because my first version of this update was with an UpdateIf that didn't work after the 2999. row)

Thank you once again!

[RandyHayes]

@shibathedog 

Glad to help!

Everything is subject to record limits in PowerApps.  If your result sets or actions are outside of that record limit, then delegation is vital.  

UpdateIf and RemoveIf and other actions are subject to record limits also and must be delegable for outside of that limit.

Patch patches records (always) or tables (in the scenario with a datasource).  

The key thing here is using Patch to patch the datasource with a table of records.

In your original formula you were using the ForAll as a programmatic For/Loop.  It is not that, it is a function that iterates over a table and returns a table. 

So, back to patch. 

With Patch, you can

1) Do Patch(record, record) that returns a new record. 

2) Do Patch(dataSource, recordsource, record[, record, record...]) which will Patch a recordsource with a record and then, push that record to the datasource table. 

3) Do Patch(dataSource, table) which will perform #2 except it will iterate over all the records in the table. 

The KEY to this is this...any time you go to the datasource (#2 or #3) you MUST have an ID. It is the only thing really required for that to work.

So...in the formula, we use a ForAll to return a Table of ID and Sold columns.  The output of that ForAll is given as the second parameter of #3

NOW...all that said.  It is not that doing a Patch inside a ForAll will not work, it is just much less performant and is a waste of the table that ForAll creates.

As a bonus...in the scenario where you want to CREATE new records in your DataSource, you can use the Collect function for that.  Ex.

   Collect(yourDataSource, ForAll(Sequence(10), {Title: "This is Record : " & Text(Value}))

This will create 10 new records in the datasource and supplies the title (usually required).

I hope this is clear and helpful for you.

[shibathedog]

Wow @RandyHayes this is very detailed and knowledgeable! I learned more from your post than from whole day of searching for examples 😁 Thank you so much for explaining this! Now it seems so simple and I am sure many more people will stumble upon this and find it helpful! If you ever make a course or something, drop me message, I'd be glad to sign up for it! (I already follow your yt! 😊)

If you don't mind explaining me, just in short notes, would it be faster creating a temporary collection and then using that table instead of the Filter() in ForAll? Because the Filter also returns a table, correct? Just fyi, your solution is perfectly good and fast, I'm just wrapping my head around all the possibilites.

P.S. thank you for the code for creating new records, that actually might come handy later!

Thanks!

[RandyHayes]

@shibathedog 

So very glad to help out.  And thanks for subscribing to the yt!  I promise I will put up more content soon - been swamped!!

Collections are great for times when you NEED the ability to have an in-memory database. There are times for them, but I typically will use a variable instead if I need a snapshot of data.  Variables don't have the overhead of a collection (they don't use the mechanisms to add or remove rows).   Beyond that, they are extra overhead.

As for creating a temp collection.  It's just more performance overhead and more formulas that can "go wrong" for you.  

Example, there is really no need to create a table to put into a collection (which is just a table) so that you can use the collection (table) for a function.  Do it once and done!  

Like in the formula we worked out - there would be no real sense to use the ForAll that is in that formula to pump into a temp collection to then use in the Patch function.  It's just more wasted processing. 

Oops...you said "short"...

Yes, Filter returns a Table. 

Patch returns a record. 

LookUp returns a record.

UpdateIf/RemoveIf/Update/Remove return Tables.

AddColumns/ShowColumns/DropColumns/RenameColumns all return tables.

Collect/ClearCollect all return nothing.

These things can all be used in many ways once you know what they return.

Example...want to take a snapshot of the record you are patching into a variable?

    Set(result, Patch(yourList, LookUp(yourList, ID=1),  {Title: "updated"}))  //Patch returns a record

    can also be written as:

    Set(result, Patch(yourList, {ID=1},  {Title: "updated"}))

What if you want to use UpdateIf ?

    Set(result, LookUp(UpdateIf(yourList, ID=1, {Title, "updated"}), ID=1))

UpdateIf returned a table of the modified yourList.  LookUp looked up that record ID=1 in that table.

[shibathedog]

@RandyHayes I am looking forward to your new videos!

Thank you for the explanation! This wasn't short sure, but it was just perfect, stuff like that helps me grew my knowledge and putting things to use, I am just starting but I can see this being very valuable in the future!

I get it, making a temp table won't make it faster but opens a new area for errors (in layman terms). Just one last question: of the two patch examples you wrote, would you prefer the first one or second? Or does it depend on the cause you are using it for? The second one seems better because the formula doesn't have to evaluate the LookUp. Sorry if I got this wrong!

Thank you!

[poojathakur]

Iam updating the below "Feedback" list one column "Feed1Sent" ,its is updating but showing delegation warning in patch function.

Actual scenario is I have 5 check box and I want to update list column based on the checkbox selection based on the ID of the list like: Feed1Sent:"Yes",Feed2Sent:"Yes",Feed3Sent:"Yes",Feed4Sent:"Yes",Feed5Sent:"Yes" in the list if the

If( (chkSelect1.Value = true && lblfeed1Sent.Text="No"),Patch(Feedback, First(Filter(Feedback, ID in lblID.Text)), {Feed1Sent:"Yes"})

when Iam updating all the 5 records by checking all the check boxes its is updating one of the column ,this is happening due to delegation warning, as Iam using IN in the above formula, but unable to identify ,how can ID patch particular column in list.

Kindly help.