Filter DataTable with 1 or many search parameters.

Like (0) Share

Report

Posted on 8 Oct 2019 09:29:46 by StuartSmith

207

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.

Categories:

General Questions

2019-10-08_08-43-...

All responses (7)

Answers (0)

StuartSmith 207 on 12 Oct 2019 at 19:01:24

Like (0)

Report

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

TestApp.msapp

TestData.xlsx
v-yutliu-msft on 10 Oct 2019 at 09:39:24

Like (0)

Report

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,
StuartSmith 207 on 10 Oct 2019 at 09:30:26

Like (0)

Report

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
StuartSmith 207 on 09 Oct 2019 at 13:38:02

Like (0)

Report

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.
v-yutliu-msft on 09 Oct 2019 at 10:09:24

Like (1)

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

Best regards,
StuartSmith 207 on 09 Oct 2019 at 08:29:58

Like (0)

Report

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?
RezaDorrani 12,141 on 08 Oct 2019 at 15:28:51

Like (0)

Report

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-with-respect-to-all-column-types/

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