cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdamH
Post Patron
Post Patron

Need help with a Filter(ForAll( formula

Hi All,

 

I am trying to use a Filter formula but I would like to have it filter from 2 galleries. 

 

I have two galleries, BrowseGaller1 and Gallery2

The formula I am using is:

Filter(ForAll('NC MST Tracker',If(BrowseGallery1.Selected.'Employee Name' in ThisRecord.'Mentor / Preceptor Name'.Value,ThisRecord,Blank())),!IsBlank('Employee Name'))

 

Is there a way to get this to work from both galleries? 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

@AdamH 

Let me explain

Firstly I didn't want you to remove any code you had. So please leave everything as it was.

You had wanted to do a Filter but the criteria for the Filter can come from 2 different sources. To handle this I had asked you to add a variable in the OnSelect of the GREEN OUTLINE in BrowseGallery1,

Set(gblSelectedEmployee, BrowseGallery1.Selected.'Employee Name')

 

add a variable in the OnSelect of the GREEN OUTLINE in Gallery2,

Set(gblSelectedEmployee, Gallery2.Selected.'Employee Name')

 

change the filter formula to,

Filter(ForAll('NC MST Tracker',If(gblSelectedEmployee in ThisRecord.'Mentor / Preceptor Name'.Value,ThisRecord,Blank())),!IsBlank('Employee Name'))

 

 Hope it is clear!

View solution in original post

15 REPLIES 15
CNT
Super User
Super User

@AdamH From the code you have shared, it looks like you are trying to filter all the records from 'NC MST Tracker' where the 'Mentor / Preceptor Name' is the same as the selected 'Employee Name' in Browse Gallery1.

For that you can use this simple formula,

Filter('NC MST Tracker', BrowseGallery1.Selected.'Employee Name' in ThisRecord.'Mentor / Preceptor Name')

 

You have also said you want to filter on Gallery2. So what value in Gallery2 do you want to use as the base value and which field are you trying to filter on and in which table/list?

 

@CNT 

BrowseGallery1 is the original gallery that was setup with the app from the SP List (wizzard)

 

I since added a 'filtering' gallery that sorts all the records into groups depending on staff role ect.  I can select a group of staff and display those records in (gallery2). 

 

The problem I have is when navigating through the original BrowseGallery1, that formula works but when navigating through Gallery2, it does not show the correct records. 

 

Gallery2 was set up differently to the original BrowseGallery1. Gallery2 uses

Items: glbCurrentRecord.Items 

OnSelect: Set(gblID,ThisItem.ID);Navigate(DetailScreen1)

 

Basically I want to filter the same field but from both galleries.

 

@AdamH Where is this Filter code going? Also could you share some screens please.

@CNT 

I will try to share what I can with you, I have to be careful with data protection because this is a live app. 

 

The image below is my original browsegallery1

BG1.png

Red Outline - ITEMS: 

With(
    {WMST: 'NC MST Tracker'}, Sort(
    Filter(
        WMST,
        Or(IsBlank(TextSearchBox1.Text),
        TextSearchBox1.Text in 'Employee Name',
        StartsWith(
            'Employee Name',
            TextSearchBox1.Text
        )) && Switch(
            Gallery1.Selected.Value,
            "Mentor",
            Mentor,
            "AET",
            'AET Instructor',
            "MST",
            'MST Assessor',
            true
        )
    ),'Employee Name',If(SortDescending1, Descending,Ascending))
)

Green Outline - ONSELECT: 

Select(Parent)

On select then navigates to DetailScreen1 (below)

DS1.png

Red Outline - Where the filter formula is entered

Filter(ForAll('NC MST Tracker',If(BrowseGallery1.Selected.'Employee Name' in ThisRecord.'Mentor / Preceptor Name'.Value,ThisRecord,Blank())),!IsBlank('Employee Name'))

 

 

Other Gallery I am using. 

This is gallery5_1. I am using this as a dashboard report which filters the staff into groups. 

The right circled arrow navigates to the another gallery (Gallery2) that only lists those staff which are in that group. 

Set(glbCurrentRecord,ThisItem);Navigate(DashboardReport1)

DB1.png

 

Gallery2, this is where I navigate BACK to the DetailScreen1 using:

DBR1.png

Red Outline - ITEMS:

glbCurrentRecord.Items

Green Outline - ONSELECT:

Set(gblID,ThisItem.ID);
Navigate(DetailScreen1)

 

So the problem I have is:

 

When I navigate from BrowseGallery1 to DetailScreen1 - The formula works

Filter(ForAll('NC MST Tracker',If(BrowseGallery1.Selected.'Employee Name' in ThisRecord.'Mentor / Preceptor Name'.Value,ThisRecord,Blank())),!IsBlank('Employee Name'))

 

When I navigate from dashboardreport1 screen (gallery2) to detailscreen1 the formula does not work - and I get the previous opened record results.

@AdamH Understood the issue quiet well.

You are seeing the same results because Mentor/Preceptor is always filtered based on the selection in BrowseGallery1.

 

To solve this, before you navigate to DetailScreen1, in the OnSelect of the GREEN OUTLINE in BrowseGallery1 put this code,

 

Set(gblSelectedEmployee, BrowseGallery1.Selected.'Employee Name')

 

 

in the OnSelect of the GREEN OUTLINE in Gallery2 put this code,

Set(gblSelectedEmployee, Gallery2.Selected.'Employee Name')

 

change the filter formula to,

Filter(ForAll('NC MST Tracker',If(gblSelectedEmployee in ThisRecord.'Mentor / Preceptor Name'.Value,ThisRecord,Blank())),!IsBlank('Employee Name'))

 

CNT
Super User
Super User

@AdamH Just checking if you got it to work.

@CNT 

I'm just working on it now as it happens. 

 

I'm having a problem with this:

Set(gblSelectedEmployee, BrowseGallery1.Selected.'Employee Name')

It is calling up the last opened record. 

@AdamH what do you mean by "It is calling up the last opened record. "?

@CNT 

The last record I opened in powerapps play before closing and going back to the design view. 

 

Before your suggestions:

In my BrowseGallery1

The first gallery item ONSELECT and the Eye icon is set to:

Set(gblID,ThisItem.ID);Navigate(DetailScreen1, ScreenTransition.None)

 

You suggest changing it to:

Set(gblSelectedEmployee, BrowseGallery1.Selected.'Employee Name')

But there is no navigation - So I added:

Set(gblSelectedEmployee, BrowseGallery1.Selected.'Employee Name');Navigate(DetailScreen1)

But this is not opening the record I want, It is opening he last record I had open.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,973)