cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Filtering a LookUp column based on the position of the person

Hi all,

 

I am creating a MainTable application based on a SharePoint List with a column that looks up information from another SharePoint List (EmployeeList).

 

In my Maintable each item/row is a project and in this situation there will be a Partner/Principle assigned as well as a "Sales Responsible". The problem is the two columns LookUp both retrieve data from the EmployeeList, which means all the employees, whether they are Partner/Principle or Sales Responsible show up in the dropdown.

 

To show what I mean, here is a list of the EmployeeList:

 

EmployeeList1.JPG

 

And here is a picture of the MainTable List, where the two columns are shown (both based on the EmployeeList):

 

 

 

EmployeeList2.JPG

 

 

 

The two ComboBoxes can be seen below, both currently show ALL employees, whether Partner/Principle or Sales Responsible.

 

EmployeeList3.JPG

 

 

Is there a way to filter these preset Comboboxes so that it filter according the the Position the employee holds?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Filtering a LookUp column based on the position of the person

@AndersIskov 

Now I am wondering: what type of Column is Position?  Is it a Choices type column?  I assumed it was a Single-line text column.

 

See my changes in blue if Position is in fact a Choices column.

 

Filter(
    Choices('Sales Pipeline Management'.Pen_x0020_Holder),
    Value in Filter('Employee List SPM', Position.Value = "Partner / Principle").Pen_x0020_Holder
)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

11 REPLIES 11
Highlighted
Post Prodigy
Post Prodigy

Re: Filtering a LookUp column based on the position of the person

I have tried the following, without luck, it gives me the red scribbles underneath the equal sign:

 

Filter(Choices([@'Sales Pipeline Management'].Pen_x0020_Holder), 'Employee List SPM'.Position = "Partner / Principle")
Highlighted
Post Prodigy
Post Prodigy

Re: Filtering a LookUp column based on the position of the person

Does anyone have an idea?

Highlighted
Super User
Super User

Re: Filtering a LookUp column based on the position of the person

@AndersIskov 

Can you please post a screenshot of the error message that shows for your red underlined code?

 

Highlighted
Post Prodigy
Post Prodigy

Re: Filtering a LookUp column based on the position of the person

@mdevaney,

 

Yes of course. This is what it lets me know when I try to use the above filter:

 

EmployeeList4.JPG

 

 

Highlighted
Super User
Super User

Re: Filtering a LookUp column based on the position of the person

@AndersIskov 

Gotta love those super vague error messages LOL!

 

I think the problem is your FILTER function is using another list as a criteria  'Employee List SPM'.

 

What does Pen_x0020_Holder refer to in the context of this part of the formula?  Is it a LookUp Column to 'Employee List SPM'?

Choices([@'Sales Pipeline Management'].Pen_x0020_Holder)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Post Prodigy
Post Prodigy

Re: Filtering a LookUp column based on the position of the person

@mdevaney 

 

Yes! So frustrating 😞

 

Your last sentence hit it on the nail. It is a LookUp column that takes information from the "Employee List SPM" and stores in the "Sales Pipeline Management".

 

In that case, is there a work around?

 

I am having so many problems once I use comboboxes outside of the preset info that I try to avoid them by all cost, so I hope there is a solution to this problem 🙂

Highlighted
Super User
Super User

Re: Filtering a LookUp column based on the position of the person

@AndersIskov 

After thinking about how to re-arrange your formula throughout the day and this is what I eventually came up with.  I tested it on my side with some fake data and it appeared to work.     

 

Filter(
    Choices('Sales Pipeline Management'.Pen_x0020_Holder),
    Value in Filter('Employee List SPM', Position = "Partner / Principle").Pen_x0020_Holder
)

 

I feel uncertain on what the values in Pen_x0020_Holder actually look like.  I cannot visualize it at all.  Instead of Pen_x0020_Holder I used ID in my example and it seemed to work fine.  Hopefully Pen_x0020_Holder will be OK

 

Let me know your thoughts on my idea once you have time to try it.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Post Prodigy
Post Prodigy

Re: Filtering a LookUp column based on the position of the person

@mdevaney

 

Thank you for the response.

 

I am sorry, I think I have been confusing in explaining the situation:

 

We have three different columns for each item:

 

1. Sales Responsible (can be anyone from the list of employees)

2. Pen Holder also comes from the list of employees, but here I want to filter down to Position = "Partner / Principle"

3. Responsible Partner / Principle exactly same as pen holders

 

Unfortunately I still get red scribble from the solution you have provided, have I done something wrong?:

 

EmployeeList5.JPG

 

 

I also tried with:

 

Filter(
    Choices('Sales Pipeline Management'.'Responsible Partner / Principle'),
    Value in Filter('Dogma Alares Employee List SPM', 'Dogma Alares Employee List SPM'.Position = "Partner / Principle").ID
)

 

But this gives the same error..

Highlighted
Super User
Super User

Re: Filtering a LookUp column based on the position of the person

@AndersIskov 

Now I am wondering: what type of Column is Position?  Is it a Choices type column?  I assumed it was a Single-line text column.

 

See my changes in blue if Position is in fact a Choices column.

 

Filter(
    Choices('Sales Pipeline Management'.Pen_x0020_Holder),
    Value in Filter('Employee List SPM', Position.Value = "Partner / Principle").Pen_x0020_Holder
)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,722)