Patch update using collection

[Gelos]

Hi guys I need your help.

I want to update cds table using collection. my collection will be

Clearcollect(collectionname,{id=1,name=geek,Age=20},{id=2,name=geek,Age=20},{id=3,name=geek,Age=20}))

I want to update that rows based on id I am using formula

ForAll(collectionname,

patch(tablename(First(

filter(tablename,Id in collectionname.id

)

),

{

Name=collectionname.name,

Age=collectionname.Age

}

)

)

But I will update only Id=1 row in table not Id=2 as wll as Id=3 

What is the correct formula please help me.

[WarrenBelz · Accepted solution]

Hi @Gelos ,

Assuming the Id fields line up

Clearcollect(
   collectionname,
   {
      id=1,
      name=geek,
      Age=20
      id=2,
      name=geek,
      Age=20
      id=3,
      name=geek,
      Age=20
   }
);
ForAll(
   collectionname As aPatch.
   Patch(
      tablename,
      {Id :aPatch.id},
      (
         name:aPatch.name,
         Age:aPatch.Age
      }
   )
)

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.

Visit my blog Practical Power Apps

[Gelos]

Thank you for your replay. what mean apacth is it function or else.

[WarrenBelz]

@Gelos ,

Please read this document - it serves as an identifier that address potential ambiguity.

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.

Visit my blog Practical Power Apps

[Gelos]

Ok thank you. I will look at that. 

[Gelos]

I tried it but it can not update. It will be inserted I want to be updated.

[Gelos]

Thanks Warren. I changed this formula for update.

[RandyHayes]

@Gelos 

Also, as a follow on - the ForAll is backward in your formula and not used properly.  ForAll returns a table of records, it is very inefficient as a ForLoop like in development (which PowerApps is not!).

If the columns of your collection have the primary key (Id) and the names match, then the only formula you need is:

Patch(tablename, collectionname)

What is nice about this as well, is that if a primary key is found, Patch will update it.  If a primary key is NOT found, then it will create a record.  So, you can use the above very efficiently to both update and create at the same time.

In the above, the entire table is given to patch all at once and Patch is instantiated once.  With a ForAll, the Patch is instantiated for each iteration of the ForAll table source, this will impact performance heavily.

If a more customized record is needed from a collection (i.e. the column names don't match), you can utilize the RenameColumns function to make them match and the DropColumn (or ShowColumns depending on the number of columns in your collection) to match the columns you want in your patch.

So, if your collection, for example, had a column called "PersonName" where the record in the datasource wanted a column called "name" and you also had a "Status" column in the collection, which does not exist in the datasource, then your formula would be:

Patch(tablename,
    DropColumns(
        RenameColumns(collectioname, "PersonName", "name"),
        "Status"
    )
)
             

Again, the above provides a table of records to the Patch function and it is only instantiated once.

If you need more customization, the ForAll is one of the most powerful functions in PowerApps (when used right)!  ForAll creates a table.  Then this can be used to provide to the Patch function.  Example, you want to change the name of all records and prepend the name with "Name:" 

Patch(tablename,
    ForAll(collectionname,
        {Id: Id
         name: "Name:" & name,
         Age: Age
        }
    )
)

This will provide a table to the patch function that will have that adjustment made.

To avoid all the typing of column names when modifying only one or a select few columns, this formula would do the exact same as the above formula:

Patch(tablename,
    ForAll(collectionname As _item,
        Patch(_item, {name: "Name:" & name})
    )
)

Again, all of the above are more performant as the Patch function is only instantiated once and provided a table of records that are all ready to go.

When the ForAll is backward, PowerApps has to repeat the same actions and instantiate the Patch function for each iteration of the ForAll table.  While it might work, performance will suffer and, as you can see, you will type much more formula to have to maintain.

Once you view the ForAll as the function it was made to be, you will find it to be the most powerful function in PowerApps!  Looking at it as a ForLoop and you will curse at it for what it is not.

I hope this is informative and helpful for you.

[WarrenBelz]

Hi @RandyHayes ,

Nice to hear from you. I have seen a couple of posts from you on this subject before and understand the performance advantages of a single Patch with one write to the data source, however I have not managed to get it to work (if only in a couple of attempts), In the example, the code

ClearCollect(
   collectionname,
   {
      id: 1,
      name: "geek",
      Age: 20
   },
   {
      id: 2,
      name: "geek",
      Age: 20
   },
   {
      id: 3,
      name: "geek",
      Age: 20
   }
);
ClearCollect(
   tablename,
   {
      id: 1,
      Name: "geek",
      Age: 20
   },
   {
      id: 2,
      Name: "geek",
      Age: 20
   },
   {
      id: 3,
      Name: "geek",
      Age: 20
   }
);
ForAll(
   collectionname As aPatch,
   Patch(
      tablename,
      {id: aPatch.id},
      {
         Name: aPatch.name,
         Age: aPatch.Age
      }
   )
);

works as expected, however when I replace the bottom bit with your example

Patch(
   tablename,
   ForAll(
      collectionname As _item,
      Patch(
         _item, 
         {name: "name:" & name}
      )
   )
)

I get the error below

[RandyHayes]

@WarrenBelz 

Good to hear from you too!  Hope all is well and you are enjoying the New Year!!

Yes, what you are seeing is Patch "throwing a fit" because in that example you are trying to Patch a collection.  A collection is just a table, so it does not have things that a datasource does...like a Primary Key.  This is a disadvantage for collections (one of many) as Patch will only treat them one way.  So with a Patch function on a collection, you need to always specify the entire record to be Patched  - Patch(collection, <record>, <record changes>

When patch is used with a datasource, it is primarily (no pun intended) working with the Primary Key for everything.  So patch will determine what to do based on the primary key - if there, update.  If not, create.  And it will take a full table of all the records to update or create all at the same time.

I used to think the only way to do a remove on records (in a delegable way) was with the RemoveIf function.  And since it only took one criteria, I thought this was one place that I needed to reverse a ForAll into a ForLoop.

i.e.:   

ForAll(tableOfRecords As removes,
   RemoveIf(datasource, ID=removes.ID)
)

This was horrible for performance on large lists.

Then I looked closely at the docs and took note that there were TWO syntaxes for Remove:

This made me change the formula back to a normal table:

Remove(datasource, tableOfRecords)

In this formula, Remove (just like Patch) looks at the primary ID in the tableOfRecords table.  It will then (and this is Delegable) remove the records by their primary key.

So, there was no longer a need for a ForAll, it was just that simple.

Now, Remove is smart enough to work with collections as well, but that matching is ENTIRE record.  So if I did this on a datasource:

Remove(datasource, tableOfRecords.ID)

The above would only be providing a table with an ID column to the Remove function.  And it works fine.

But:

Remove(colletion, tableOfRecords.ID)

Would not work because it expects the entire record of the collection to match it (as there is no primary key)

One other thing about that error message you showed.  That error is confusing in one other place where it shows.  

If you use a Patch(dataSource, collection) statement, sometimes you will see that same message.  It is misleading because what it really means is that there is a column in your collection that is NOT in the datasource, or that a column is not named properly in the collection to match the datasource (and that match needs to be "real" column names - as in the case of renaming a SharePoint column...the collection needs to contain the original column name.)  When I first saw that scenario, I pulled some hair out for sure until I understood that it was a misleading message!