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

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
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
AndersIskov
Level: Powered On

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")
AndersIskov
Level: Powered On

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

Does anyone have an idea?

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?

 

AndersIskov
Level: Powered On

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

 

 

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

AndersIskov
Level: Powered On

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 🙂

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

AndersIskov
Level: Powered On

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

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
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,054)