Gallery2 filtered by Gallery1 All items ID

[Traciwho]

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:

1. Get all of the findings for ALL of the audits in Gallery 1.   (shouldn't be delegation issue)
2. In gallery 2, show a distinct list of said findings with a sum of findings by Item number  

Noting that I know a bit about SQL but am really new to Powerapps, here's what I've tried thus far:

1. Tried to filter(Permit_Findings, Permit_findings.ID = Gallery1.allitems.ID) - nope
2. Tried to filter(Permit_Findings, Permit_findings.ID = theseaudits.ID) - nope(really same as above)
3. Creating a collection with only the Permit_Audits.ID where I could ask if the Permit_Findings.permit_audit_id was in the collection; didn't get that right, kept saying I had a table value instead.

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! 

[RandyHayes]

@Traciwho 

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.

[Traciwho]

@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},

[RandyHayes · Accepted solution]

@Traciwho 

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.

[Traciwho]

@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! 

[RandyHayes]

@Traciwho 

Well you can sleep well now...until your next challenge.  Post back if you hit another.