cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Patch multiple records from two diffrent tables

I am new to powerapps and just learning it. I know that I need to use the patch function but having trouble with the syntax to figure out how to do this.

 

I have two tables using a common id.  The second table has multiple records related to the first table, so I would like to count the number of records with the same id in the second table and then update a field in the first record with this count.  I plan to do this in the app.onstart and would like to calculate for every id in the first table.

First table          Second table

ID                       ID

Id count Field

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

Hi @Anonymous ,

Do you want to update the number of the same id value in table2 to table1?

I've made a similar test for your reference:

1)my table1:

ClearCollect(table1,{id:1,value:"a"},{id:2,value:"a"},{id:3,value:"a"},{id:4,value:"a"},{id:5,value:"a"})

my table2:

ClearCollect(table2,{id:1,value:"a"},{id:1,value:"a"},{id:2,value:"a"},{id:2,value:"a"},{id:2,value:"a"},{id:3,value:"a"},{id:3,value:"a"},{id:4,value:"a"})

2)Set the app.onstart:

ClearCollect(table3,AddColumns(GroupBy(table2,"id","same id"),"number",CountRows('same id')));
//count the number of same id
ForAll(table3,Patch(table1,LookUp(table1,id=table3[@id]),{value:number}))
//update table1 with the value of counting number

The mainly important thing is :

1)group table based on id

2)update table1 with multiple records of another table.

Here are two docs about these two functions for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yutliu-msft
Community Support
Community Support

Hi @Anonymous ,

Do you want to update the number of the same id value in table2 to table1?

I've made a similar test for your reference:

1)my table1:

ClearCollect(table1,{id:1,value:"a"},{id:2,value:"a"},{id:3,value:"a"},{id:4,value:"a"},{id:5,value:"a"})

my table2:

ClearCollect(table2,{id:1,value:"a"},{id:1,value:"a"},{id:2,value:"a"},{id:2,value:"a"},{id:2,value:"a"},{id:3,value:"a"},{id:3,value:"a"},{id:4,value:"a"})

2)Set the app.onstart:

ClearCollect(table3,AddColumns(GroupBy(table2,"id","same id"),"number",CountRows('same id')));
//count the number of same id
ForAll(table3,Patch(table1,LookUp(table1,id=table3[@id]),{value:number}))
//update table1 with the value of counting number

The mainly important thing is :

1)group table based on id

2)update table1 with multiple records of another table.

Here are two docs about these two functions for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

sorry I might not made my orginal question clear enough.  The solution i see above looks like it is just making collection tables.  my data exist in two different sharepoint lists.  Does this make a difference?

Hi @Anonymous ,

There's no difference.

I just use two collections as an example.

You could also use other data sources.

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Anonymous ,

Have you solved your problems?

If you have no other problems, please mark my answer as solution.

Thanks!

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,610)