Unanswered

Filter DataTable with 1 or many search parameters.

(0) Share

Report

Posted on by StuartSmith

225

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:

Building Power Apps

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

I have the same question (0)

All responses (7)

Answers (0)

RezaDorrani 12,143 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
StuartSmith 225 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
v-yutliu-msft on at

Like (1)

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

Was this reply helpful? Yes No
StuartSmith 225 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
StuartSmith 225 on at

Like (0)

Report

@ 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

Was this reply helpful? Yes No
v-yutliu-msft on at

Like (0)

Report

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,

Was this reply helpful? Yes No
StuartSmith 225 on at

Like (0)

Report

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

Was this reply helpful? Yes No