Hi all, long time looker, first time poster. You've already helped me so much in the past, I sure hope you can help with this one; I'm stumped.
I have 3 SharePoint lists, Permit_Audits, Permit_Findings and Permit_Items. The premise is that an auditor fills out the form (Permit_Audits). This person then goes down through the list and adds findings from the list of Permit Items; these are captured in (you guessed it) Permit _Findings. (Example and table names below)
This screen works great; it's on another screen where I'm trying to extract the information from multiple audits that I get stuck. I have created a screen that will show information YTD, and the following collection, which goes into Gallery1:
ClearCollect(theseaudits,Filter(Permit_Audits, Year(Audit_Date) = (Year(Now()))))
With this information, I need Gallery2 to show a Sum of each ALLof the findings for ALL of the items listed in the above collection. I have tried several methods,
I need to do the following:
Noting that I know a bit about SQL but am really new to Powerapps, here's what I've tried thus far:
I guess I could go back and add the dates to the Permit_Findings where I could filter it the same way I do Permit_Audits, but that just seems stupid to match all of the audit fields in findings just because I can't find a way to query this by AuditID alone. Plus, I have more filters I'd like to apply... Please, any help would be so appreciated!
Note: I'm really weak on Forall/Patch statements, so please be really specific if that's your solution. Thanks!
Solved! Go to Solution.
Too funny...I was sitting here banging my head on why I was not seeing Findings...then I realized you had added that in your response
So, you just need to change the Formula on the Gallery Items to this:
AddColumns( GroupBy( Filter( AddColumns(PermitFindings, "FindingItemTitle", LookUp(PermitItems, PermitItems[@ID]=PermitFindings[@PermitItemID]).Title, "AuditDate", LookUp(PermitAudits, ID=PermitAuditID).AuditDate ), Year(AuditDate)=2019), "FindingItemTitle", "items"), "NumberOfFindings", CountRows(items), "SumOfFindings", Sum(items,Findings) )
A couple of things that are different:
1) The FindingItem is now FindingItemTitle - no impact on anything in this formula, just a change I had made for clarity.
2) There are now two columns added - NumberOfFindings and SumOfFindings. They can be referenced in your Gallery.
Hope that is clear and helpful.
EDIT: I attached the updated example.
The trick here is to shape your data for your Gallery then Group and count.
It can all be put into one simple formula:
GroupBy( Filter( AddColumns(PermitFindings, "FindingItem", LookUp(PermitItems, PermitItems[@ID]=PermitFindings[@PermitItemID]).Title, "AuditDate", LookUp(PermitAudits, ID=PermitAuditID).AuditDate ), Year(AuditDate)=2019), "FindingItem", "items")
I took some liberties with the names of things, but it's pretty close to your data schema.
Also, since this might be a challenge to see here, I've attached a sample app that shows the three galleries you had in your photo, completed with the data you want. Look at the OnStart action for the sample data definition.
Then look at the Items properties on the Galleries. There are no collections or other issues to deal with.
Take a look and let me know if there are any questions.
I hope this is helpful for you.
@RandyHayes Randy, thanks for your help, and the app example you put together helped tremendously! My only caveat is that for each "item" that was grouped, there may be more than one finding. Is there a way to add the Sum in the 'Group by' statement? My attempts have to tweak what you gave me have not worked.
ClearCollect(PermitFindings,
{ID:1, PermitAuditID:1, PermitItemID:1, Findings:3}, Item1 Sum(4)
{ID:2, PermitAuditID:2, PermitItemID:2, Findings:2},
{ID:3, PermitAuditID:3, PermitItemID:1, Findings:1},
Too funny...I was sitting here banging my head on why I was not seeing Findings...then I realized you had added that in your response
So, you just need to change the Formula on the Gallery Items to this:
AddColumns( GroupBy( Filter( AddColumns(PermitFindings, "FindingItemTitle", LookUp(PermitItems, PermitItems[@ID]=PermitFindings[@PermitItemID]).Title, "AuditDate", LookUp(PermitAudits, ID=PermitAuditID).AuditDate ), Year(AuditDate)=2019), "FindingItemTitle", "items"), "NumberOfFindings", CountRows(items), "SumOfFindings", Sum(items,Findings) )
A couple of things that are different:
1) The FindingItem is now FindingItemTitle - no impact on anything in this formula, just a change I had made for clarity.
2) There are now two columns added - NumberOfFindings and SumOfFindings. They can be referenced in your Gallery.
Hope that is clear and helpful.
EDIT: I attached the updated example.
@RandyHayes Thank you, thank you! You have totally made my day...probably my week, because this issue has literally kept me up at night. So appreciated!
Well you can sleep well now...until your next challenge. Post back if you hit another.
Check out new user group experience and if you are a leader please create your group
Did you miss the call?? Check out the Power Apps Community Call here!
See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.
User | Count |
---|---|
273 | |
257 | |
87 | |
39 | |
34 |
User | Count |
---|---|
348 | |
249 | |
130 | |
68 | |
48 |