cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jmerk
Level: Powered On

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
Community Support Team
Community Support Team

Re: Patch multiple records from two diffrent tables

Hi @Jmerk ,

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
Community Support Team
Community Support Team

Re: Patch multiple records from two diffrent tables

Hi @Jmerk ,

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

Jmerk
Level: Powered On

Re: Patch multiple records from two diffrent tables

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?

Community Support Team
Community Support Team

Re: Patch multiple records from two diffrent tables

Hi @Jmerk ,

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.
Community Support Team
Community Support Team

Re: Patch multiple records from two diffrent tables

Hi @Jmerk ,

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 170 members 5,617 guests
Please welcome our newest community members: