cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter a SharePoint LookUp combobox or dropdown according to columns in table

Hi all,

 

I have a field in which I would like to be able to filter the information a user can select from. The situation is the following:

 

Each project/row has 12 different phases ranging from "1. Lead"->"7. Sold"->"11. Lost"->"12. Cancelled"

 

A user can choose the phase according to what stage a project is in. If a user chooses fx. "11. Lost", then the user will be asked to choose one of 9 different reasons. In the reason there is a choice called "Project combined with other project". Now if that is choosen a 3rd dropdown/combobox appears. Here the user is supposed to choose between projects from the Maintable.

 

In order to find the best solution I have made two different columns either a singe-text field or a LookUp column.

 

1. The single-text field I have put a dropdownbox in which I write the following:

 

(Concatenate('Sales Pipeline Management'.Title, " - ",'Sales Pipeline Management'.'Opportunity Name')

 

Title = Name of the company/customer

Opportunity Name = Short Description of Project

 

2. Choices([@'Sales Pipeline Management'].Combined_x0020_With)

 

The combobox is a LookUp column in which I have connected it to "Opportunity Name" (Short Description of Project)

 

Here I do not know how to also add "Title" in the combobox, is there a solution to that?

 

 

 

My problem is this:

 

How do I filter this dropdown/combobox so that it ONLY shows a project from the same customer, which are in either of the Phases: 1,2,3,4,5,6,7,8 ?

 

I have tried this filter in the dropdown, which returns all values and does not filter down:

 

Sort(Filter(Concatenate('Sales Pipeline Management'.Title, " - ",'Sales Pipeline Management'.'Opportunity Name'),Title = ThisItem.Title),Ascending)

 

If I try the follow filter it returns NO values:

 

Sort(Filter(Concatenate('Sales Pipeline Management'.Title, " - ",'Sales Pipeline Management'.'Opportunity Name'),Title = ThisItem.Title, Or(Phase.Value = "1. Lead",Phase.Value = "2. Opportunity",Phase.Value = "3. Proposal Dev",Phase.Value = "4. Proposal Sent",Phase.Value = "5. Negotiation",Phase.Value = "6. Contract Sent",Phase.Value = "7. Sold",Phase.Value = "8. Contract Sent")) ,Ascending)

 

 

I have tried the following filter for the Combobox, which does not return an error, but returns NO values:

 

Filter(
    Choices([@'Sales Pipeline Management'].Combined_x0020_With),
    Value in Filter('Sales Pipeline Management', Title = ThisItem.Title).Title)

 

I have also tried the following, which also does not return any values, but does NOT return an error:

 

Filter(
    Choices([@'Sales Pipeline Management'].Combined_x0020_With),
    Value in Filter('Sales Pipeline Management', Title = ThisItem.Title,Or(Phase.Value = "1. Lead",Phase.Value = "2. Opportunity",Phase.Value = "3. Proposal Dev",Phase.Value = "4. Proposal Sent",Phase.Value = "5. Negotiation",Phase.Value = "6. Contract Sent",Phase.Value = "7. Sold",Phase.Value = "8. Contract Sent")).Title)

 

 

 

I have tried different version of the same filter. But nothing seems to work for me.

 

I prefer the Combobox as it can be searched in, but if the dropdownbox can be filtered correctly I am also okay with that.

 

 

7 REPLIES 7
StefanR
Advocate IV
Advocate IV

Hi @Anonymous,

 

What you have tried is to Filter a string:

Filter(Concatenate('Sales Pipeline Management'.Title, " - ",'Sales Pipeline Management'.'Opportunity Name')

 

The Filter function needs a DataSource, which can be a SharePoint-List, an Excel-Table or maybe a PowerApps Collection.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

 

I recommend to create a Project List in SharePoint.

Then you create a new connection to that Project List.

Now you bind the connection to the DataSource property of the ComboBox.

Next build a filter expression a configure the Items Property of the ComboBox.

 

Depending on the selected items you can head on and filter other fields.

 

Do you get my point?

 

Best

Stefan

Anonymous
Not applicable

Hi @StefanR ,

 

I am not sure I follow.

 

Are we talking about the dropdown or the combobox?

 

Both of the above I have connected it to the Sales Pipeline Management, which is the project list from SharePoint. It finds the different "Project Descriptions" perfectly fine, however, as the list is long I would like to filter it down according the what I described in the original post.

 

Hi @Anonymous 

 

I have understood, that you want to have less entries in your combobox (dropdown works similar)

 

If you want to Filter the entries of the combobox, you have to Filter the datasource.

 

Here an example of plants that are filtered by a region. Plants is a SPList where all company plants are stored in. Now i want to see just the plants from region "DACH". 

FilterCombo.png

 

What you need (if i have understood it correctly) is a list where each phase is related to possible reasons.

 

Phase 1Reason1
Phase 1Reason2
Phase 1Reason3
Phase 1Reason4
Phase 2Reason1
Phase 2Reason2

 

Now you can Filter that list by the selected phase like that:

Filter(MyMappingTable,Phase=xyz)

The result you can bind to the items property of a combo or a dropdown.

 

I hope, i have understood your problem right.

If not... Sorry for that!

 

Best regards

Stefan

Anonymous
Not applicable

@StefanR 

 

In this case Phase is a Choice column, which is why I use the Phase.Value = "1. Lead" as an example.

 

So in that case I have done the following filter:

 

Sort(
    Filter(
        Concatenate(
            'Sales Pipeline Management'.Title,
            " - ",
            'Sales Pipeline Management'.'Opportunity Name'
        ),
        Title = ThisItem.Title,
        Or(
            Phase.Value = "1. Lead",
            Phase.Value = "2. Opportunity",
            Phase.Value = "3. Proposal Dev",
            Phase.Value = "4. Proposal Sent",
            Phase.Value = "5. Negotiation",
            Phase.Value = "6. Contract Sent",
            Phase.Value = "7. Sold",
            Phase.Value = "8. Contract Sent"
        )
    ),
    Ascending
)

 

 

So I want to make sure the dropdown filters the selection options according to Title = ThisItem.Title AND where the Phase.Value= one of those as listed above

Hi, I have to care for my Kids now, but will try to help on Wednesday or Thursday.
Anonymous
Not applicable

@StefanR,

 

Thank you, I appreciate it!

Hi @Anonymous,

 

i think i need more info about your setup, to make a smal POC on my side.

To recapitulate the needs:

 

You are working on one single list, right?

 

List:

Sales Pipeline Management

 

Fields:

1. Title = Name of the company/customer

2. Opportunity Name = Short Description of Project

3. Phase: Choice [1.Lead,...,12.Cancelled]

4. Reasons: Choice [1,..,9] and one of that is "combined with other project"

5. "Combined With" a Textfield or LookUp to the Sales Pipeline Management-List

 

Frontend:

A Dropdown to select the project

a Phase-Selector filled with choices 1-12

a Reason Selector that is visible in case of Phase-Selector=11

a Combined-Project Selector in case of Reason-Selector = "combined with" with all Projects from the same list with the conditions:

-> customer = customer of the current project AND Phases is in 1,2,3,4,5,6,7,8

 

The problem:

The main problem is the filter of the third Dropdown (in my scenario the combined-project selector).

 

I'm not sure to get it fixed over the weekend, but i will try.

 

Best regards

Stefan

 

 

 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (49,407)