I need assistance with creating a gallery.
I have a table that consists of the fields:
crew_id, dayofweek2,hours
I want the gallery to group by crew_id and sum the hours based on the day the week.
So, every row should contain the fields: crew_id, mondaysum, Tuesdaysum, wendnesdaysum .... totalsum
I This is my code so far but it's not grouping the sums:
any help would be greatly appreciated.
Solved! Go to Solution.
Wanting to drill down doesn't matter because you have a field to refer to that you can do a LookUp if you want to do that. This will still work for what you need.
There is a second option of where you do a Group By that does not include the sum and then get the sum in the gallery with a LookUp. I can't type that in without testing it to get the order correct. I'll post it in a few when I can test it and make sure I have in proper order.
Try this (HJ_FormanTotals is a SharePoint List name?)
Screens OnVisible=
ClearCollect(CollTotals,
AddColumns(GroupBy(HJ_FormanTotals, "dayofWeek2", "GrpByDay"),
"SumofHrs", Sum(GrpByDay, hours)))
Gallery Items = CollTotals (or on your chart or wherever you are showing the values)
HJ_FormanTotals is a collection that is collected upon entering the screen. I cant create sums in a different table because this gallery will be used as a drill-down and I will need to see the numbers change dynamically as hours are altered. This code would be very simple in SQL using Sum(Case When ... but is quite tricky so far in powerapps.
Wanting to drill down doesn't matter because you have a field to refer to that you can do a LookUp if you want to do that. This will still work for what you need.
There is a second option of where you do a Group By that does not include the sum and then get the sum in the gallery with a LookUp. I can't type that in without testing it to get the order correct. I'll post it in a few when I can test it and make sure I have in proper order.
That did it.
i just added this to the columns
I found one I already had doing it quicker than expected. You can also do this:
Awesome!
It just depends on how much data you have. If you end up with a lot of data and you notice it going slow you can swap it over to a collection instead and that will help. I have a few that have a lot of data (esp ones that I have looking to SQL instead of SharePoint) so what I do is on the screens visible I put a screen that shows a loading Icon and message I use, then gets the data in the collection and then hides the loading icon...just so it is obvious to the user to wait a second.
In regards to how current the data is...as long as you create the collection on the screens OnVisible it will be just as accurate as it is to look directly to the data source but will load faster.
Hi,
It looks like you got a lot of help here and you were able to get your question answered. I just wanted to add in a bit more information for learning. Feel free to take a look.
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby
Regards,
Alex
-------
Community Support Team _ Alex Rezac
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
User | Count |
---|---|
203 | |
183 | |
70 | |
43 | |
34 |
User | Count |
---|---|
339 | |
266 | |
116 | |
66 | |
66 |