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

Filter DataTable with 1 or many search parameters.

I have been asked by my boss as a learning exercise to design a app to record visitor details.  I have got 1 canvas where I can enter vistior details and they are saved in a sharepoint list.  That was the easy part.  I now want a "Report" canvas so I can search for Vistiors and can get single filters (text type) working, but want to be able to search for any of the visitor details.

 

I have a canvas with a DataTable created from a Sharepoint list, with 7 Columns, "Date (Date Type)", "First Name (Text)", "Second Name (Text)", "Company (Text)", "Car Reg (Text)", "Host (Name Lookup)" & "Visiting Site (Dropdown)".

 

I also have (or would like to keep) 8 search input boxes, "Date to (Date Picker)", "Date From (Date Picker)", "First Name (Text Input)", "Second Name (Text Input)", "Company (Text Input)", "Car Reg (Text Input)", "Host (DropDown)" & "Visiting Site (DropDown)".   (See attached)

 

I would like it to filter if any of the parameters (single or many) are entered, such as if i search for "Company" it will show all records that match the company text, then the i could refine the results, by then entering "Host" and it would then show results that match "Company" & "Host". I guess I need an "AND OR" operator, but cant figure it out. Please help.

 

Also, I want the "Host" and "Visiting Site" dropdown search parameters to show the unique values stored in their sharepoint columns, so I set "Items" for the dropdown boxes to the sharepoint list, and it shows the list colums, but not the "Host" or "Visiting Sites" columns?

 

Hopefully someone can help with this lengthy question.

7 REPLIES 7
Dual Super User
Dual Super User

Re: Filter DataTable with 1 or many search parameters.

Hi @StuartSmith 

 

Search function does not work on Complex data types (choices, lookups, person etc,)

 

Better to use a gallery and make it look like a data table

 

for filtering and not running into delegation warnings

http://rezadorrani.com/index.php/2019/09/18/powerapps-delegation-with-sharepoint-as-a-data-source-wi...

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

 

StuartSmith
Level: Powered On

Re: Filter DataTable with 1 or many search parameters.

Sorry, bad choice of words, am aware that "Search" isnt the best way to deal with data and meant to put "Filter".  That said, still no closer to a solution.  PowerApps, seems to be more App, less Power and missing so many standard features like "Print", "Export".  

 

Is it even possible to have multiple (1 or many) "Filter" parameters?

Community Support Team
Community Support Team

Re: Filter DataTable with 1 or many search parameters.

Hi @StuartSmith ,

Do you want to filter your list by 8 controls?

Firstly, I suggest you use gallery to display your list , not data table.

Date table can not display directly complex data type ,for example: look up column.

Secondly, could you tell me the data type of Visiting Site column? Dropdown is not a kind of data type.

I assume that it is a lookup column too.

I've made a test for your reference:

1)insert 2 data pickers, 4 textinputs, 2 drop downs

 

2)set data picker1's OnChange:

Set(vardate1,true)

set data picker2's OnChange:

Set(vardate2,true)

set drop down1's OnChange:

Set(vardrop1,true)

set drop down2's OnChange:

Set(vardrop2,true) 

 //datapicker and drop down will have value by default, by setting its OnChange property to justify whether you make selction 

 

3)I suggest you add a button named "search" to collect the data in the controls , since your control number is a little more.

Set the button's OnSelect:

ClearCollect(data,{name:datepicker1,status:vardate1},
{name:datepicker2,status:vardate2},
{name:dropdown1,status:vardrop1},
{name:dropdown2,status:vardrop1},
{name:textinput1,status:!IsBlank(textinput1.Text)},
{name:textinput2,status:!IsBlank(textinput2.Text)},
{name:textinput3,status:!IsBlank(textinput3.Text)},
{name:textinput4,status:!IsBlank(textinput4.Text)});
ClearCollect(datastatus,Filter(data,status=true))

4)Set the gallery's Items:

Filter(listname,If(!IsEmpty(Filter(datastatus,"datepicker1" in name)),Date>= datepicker1.SelectedDate,true),
If(!IsEmpty(Filter(datastatus,"datepicker2" in name)),Date<= datepicker2.SelectedDate,true)
If(!IsEmpty(Filter(datastatus,"dropdown1" in name)),Host .Id=drop down1.Selected.Id,true)
If(!IsEmpty(Filter(datastatus,"dropdown2" in name)),Visiting Site.Id=drop down2.Selected.Id,true)
If(!IsEmpty(Filter(datastatus,"textinput1" in name)),Textinput1.Text in First Name ,true)
If(!IsEmpty(Filter(datastatus,"textinput2" in name)),Textinput2.Text in Second Name ,true)
If(!IsEmpty(Filter(datastatus,"textinput3" in name)),Textinput3.Text in Company ,true)
If(!IsEmpty(Filter(datastatus,"textinput4" in name)),Textinput4.Text in Car Reg  ,true)
)

4)About your drop downs, I suggest you set the drop down1's Items:

Choices(listname.Host)

set the drop down2's Items:

Choices(listname.Visiting Site)

You could change its Value to ID, then it will display unique ID value.

 

Then, after you fill in words in the conrols , click the search button , the gallery will display the filtered items.

1094.PNG

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
StuartSmith
Level: Powered On

Re: Filter DataTable with 1 or many search parameters.

Thanks for a thorough response and have just got a gallary looking like a table, so first challange done.  To answer your question, the "Visiting Site" is, as you assume as "Lookup" Column.    

 

I will now work through the rest of your answer and let you get on, but in the mean time, thanks.

StuartSmith
Level: Powered On

Re: Filter DataTable with 1 or many search parameters.

Phoebe Liu,  Stupid question time.  What does "name:" represent?  Do I need to change that to something?  Also, the "Choices('Visitor Details'.'Host')" doesnt show the correct details, instead of showing the name values from the "Name Lookup" column, its showing the same results as the Choices('Visitor Details'.'Visiting Site') dropdwon box, very odd.

 

Thanks in advance

 

 

Community Support Team
Community Support Team

Re: Filter DataTable with 1 or many search parameters.

Hi @StuartSmith ,

Q1:The "name" is a field name of the collection that I create.

The step3 is the collection that I create. You could change it based on your demands.

My collection in step3:

collection1:   collectionname is "data", fieldname: "name","status"

collection2:   collectionname is "datastatus", fieldname: "name","status"

Step4 filter data based on the collection that I created.

 

Q2:You need to change the drop down's Value property, in the right pane.

You could change its Value to ID, then it will display unique ID value.

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
StuartSmith
Level: Powered On

Re: Filter DataTable with 1 or many search parameters.

Hi Phoebe, still having problems, and have even tried reducing the search from down to a single text input box, but still upon searching it displays no results.  If you or anyone gets time, I have uploaded test data (.xlsx) with the same column names as the sharepoint list, (athough the "Host" column on the sharepoint list is a name lookup list and the "Visiting Site" column is a lookup column) and a text canvas.  The canvas has a gallery in table layout, collection displayed as the table header, 1 text input and 1 search button.  Please help.

 

Also, is it possible to have the table contents displays upon load and then update upon search.? if not, no worries.

 

Thanks in advance

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 (6,558)