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

Filtering datatable columns using comboboxs and populate results in ascending/descending order

Currently having issues to populate a search by filter using datatable pulling data from a SP list. 

The datatable needs to be clickable to have the ability to also choose the project in order to see details and have the option to edit from there. 

However, the datatable and combobox are not working properly. I have admitted several options and this is the currently code I'm attmepting as of now. Could I please have a 2nd, 3rd, 4th, millions opinions to get this to work. In crunch mode

 

Code for datatable (under Items):

Sort(
Sort(
Filter(
Projects,
(!IsBlank(
LookUp(
searchItems,
Result in 'Project Title',
Result)) || !IsBlank(
LookUp(
searchItems,
Result in 'Project Phase'.Value,
Result)) || !IsBlank(
LookUp(
searchItems,
Result in 'Project Status'.Value,
Result)) || !IsBlank(
LookUp(
searchItems,
Result in 'Project Contact Name'.DisplayName,
Result)) || !IsBlank(
LookUp(
searchItems,
Result in 'Project Summary',
Result)) || !IsBlank(
LookUp(
searchItems,
Result in 'Primary Platform'.Value,
Result)) || IsEmpty(searchItems)) && If(
!IsEmpty(PhaseBox.SelectedItems),
'Project Phase'.Value in PhaseBox.SelectedItems,
true) && If(
!IsEmpty(StatusBox.SelectedItems),
'Project Status'.Value in StatusBox.SelectedItems,
true) && If(
!IsEmpty(PlatformBox.SelectedItems),
'Primary Platform'.Value in PlatformBox.SelectedItems,
true) && If(
!IsEmpty(NameBox.SelectedItems),
'Project Contact Name'.DisplayName in NameBox.SelectedItems,
true) && If(
!IsEmpty(FunctionBox.SelectedItems),
Functions.Value in FunctionBox.SelectedItems,
true) && DOCToggle_1.Value = DOC),
'Project Title',
If(
SortDescending1,
Descending,
Ascending)),
If(
SortBox_1.Selected.Value = "Project Title",
'Project Title',
If(
SortBox_1.Selected.Value = "Project Phase",
'Project Phase'.Value,
If(
SortBox_1.Selected.Value = "Project Status",
'Project Status'.Value,
If(
SortBox_1.Selected.Value = "Primary Platform",
'Primary Platform'.Value,
If(
SortBox_1.Selected.Value = "Business",
Functions.Value,
If(
SortBox_1.Selected.Value = "Functions",
Functions.Value,
If(
SortBox_1.Selected.Value = "Contact Name",
'Project Contact Name'.DisplayName,
'Project Title'))))))),
If(
SortDescending1,
Descending,
Ascending))

 

 

 

7 REPLIES 7
Super User
Super User

Re: Filtering datatable columns using comboboxs and populate results in ascending/descending order

@bsims 

Your formula has a lot of conditions in it.  Can you tell me more about searchItems in your Lookups?  Is that a collection, or variable or something?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
bsims
Level: Powered On

Re: Filtering datatable columns using comboboxs and populate results in ascending/descending order

Sure. The searchItems is the collection for all searches to pull from the SP list. Was under the impression, this collection would collect the search and reset for another user. It was just an attempt.

 

I have tried to add the Filter by Columns to generate the search and add comboboxes to pull cetain cumns I want to populate in the option choose

Super User
Super User

Re: Filtering datatable columns using comboboxs and populate results in ascending/descending order

@bsims 

So can you also provide what formula you are defining searchItems with?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
bsims
Level: Powered On

Re: Filtering datatable columns using comboboxs and populate results in ascending/descending order

Unfornatley, I do not have a formula for the serachItems. I believe that is an issue right there. It is only define in the orignial code I have shared

bsims
Level: Powered On

Re: Filtering datatable columns using comboboxs and populate results in ascending/descending order

Forgive me. I have the searchItems formula under the "Go" button assigned to the Search bar created. 

 

under OnSelect under the Go button:

If(SearchInput_1.Text = "", Clear(searchItems), ClearCollect(searchItems, Split(SearchInput_1.Text," ")))

Super User
Super User

Re: Filtering datatable columns using comboboxs and populate results in ascending/descending order

@bsims 

Can you also tell me what your DropDown boxes would contain?  Specifically, the Filter by: Columns and an example of Phase.

In otherwords, I am trying to understand if you are using the dropdowns to select which columns the search terms should be search against.  Or, if your dropdown controls contain distinct values of all the items in your datasource (ex. Phase dropdown contains a distinct list or choices of all the Phases in the list)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
bsims
Level: Powered On

Re: Filtering datatable columns using comboboxs and populate results in ascending/descending order

My Dropdown boxes contains multi-select choices and single choices. I would like for it to to have the ability to do ascending / descnding by column. 

 

I'm using the dropdowns to filter out the options the user would like to search and populate the selections from the SP list they would only like to see. the distant values are of all the items in the datasource (Current and new)

 

for example, I would like to search all projects listed under Completed Phase, Mobilty Platform, and the status is In Progress. The list should populate the items exact or close to the search

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (3,780)