Filtering a record based on a choice value

[AdamH]

Hi All,

I have been looking into creating a filter based on a lookup value within a SP List record. 

I have a staff database app. Any member of staff can be a mentor to another member of staff.

For example:

Joe is a student

Sam is Joe's mentor. In Joe's record, Sam is listed as his mentor. 

Sam also mentors Fred, and in Fred's record, Sam is listed as his mentor.

I want to be able to go into Sam's record and see a list student names who he mentors. But at the same time, I want to be able to go into any staff record and see if they are mentoring any students. 

The data fields are as follows:

'Employee Name' - Single Line of Text

'Mentor / Preceptor' - Lookup field (allowing multiple values) pointing to the 'Employee Name' Column. 

Thanks 

Adam

[v-xiaochen-msft · Accepted solution]

Hi @AdamH ,

I did a test for you.

1\ This is my list 'LIST127'.

2\ Add a gallery control 'Gallery1' and set its Items property to:

LIST127

3\ Add a combo box control and set its Items property to:

LookUp(LIST127,'Employee Name'=Gallery1.Selected.'Employee Name').'Mentor / Preceptor'

4\ Add a combo box control and set its Items property to:

Filter(ForAll(LIST127,If(Gallery1.Selected.'Employee Name' in ThisRecord.'Mentor / Preceptor'.Value,ThisRecord,Blank())),!IsBlank(Title))

5\ Now if I select the 2nd Item 'BBB' in my gallery ,the result is as follows:

// BBB's mentor

// BBB's mentee

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

[AdamH]

@v-xiaochen-msft 

Thanks for your reply. I see how you have put that together, But I want to be able to open the existing mentor record and see the filtered students from within the record. 

Example, 

BrowseScreen1 (open a record) > DetailScreen1 (within this screen I will see if this member of staff mentors anyone)

[AdamH]

@v-xiaochen-msft 

I took what you posted and change it a little:

Inserted a list box control

Items Property: 

Filter(ForAll(LIST127,If(Gallery1.Selected.'Employee Name' in ThisRecord.'Mentor / Preceptor'.Value,ThisRecord,Blank())),!IsBlank(Title))

Value: 'Employee Name'

Thanks for your help!

[AdamH]

@v-xiaochen-msft 

I found a little problem, If I have two galleries that navigate to the DetailScreen1 where I have inserted the ListBox control:

BrowseGallery1

Gallery2

How would I code this, at the moment, when I navigate from Gallery2 to DetailScreen1 It does not show the correct student records for that mentor