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

Gallery2 filtered by Gallery1 All items ID

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)

 

Capture.PNG     

 

 

  Capture.PNG

 

 

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  

 

Capture.PNG

 

 

 

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! 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Gallery2 filtered by Gallery1 All items ID

@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 Smiley Surprised

 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
5 REPLIES 5
Super User
Super User

Re: Gallery2 filtered by Gallery1 All items ID

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Traciwho
Level: Powered On

Re: Gallery2 filtered by Gallery1 All items ID

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

 

Super User
Super User

Re: Gallery2 filtered by Gallery1 All items ID

@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 Smiley Surprised

 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Traciwho
Level: Powered On

Re: Gallery2 filtered by Gallery1 All items ID

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

Super User
Super User

Re: Gallery2 filtered by Gallery1 All items ID

@Traciwho 

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 194 members 4,397 guests
Please welcome our newest community members: