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
Hi @Anonymous ,
Have you solved your problems?
If you have no other problems, please mark my answer as solution.
Thanks!
Best regards,
Hi @Anonymous ,
There's no difference.
I just use two collections as an example.
You could also use other data sources.
Best regards,
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 ,
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,