Data table - filters

[Elitezone]

Hello,

Is there a simple method to add filters for datatable with sharepoint list?

I have a sharepoint list with some stuff:

1. Name

2. Description

3. Category

4. Date when legal law was proposed

5. Person that made a proposition

I would like that add a section above data table with a set of filters that would let me dynamically sort stuff from list for example - list only propositions made by X, list only legal law propositions from this week, list only changes with category XXX.

How could I achieve that?

[v-qiaqi-msft]

Hi@Elitezone,

Based on the issue that you mentioned, do you want to filter the DataTable based on different columns?

Could you please share a bit more about the scenario?

You could add several buttons to click to filter the DataTable as you expect.

I have a test on my side, please take a try as below.

Set the OnVisible property of the current screen as below:

ClearCollect(Colwork,Test0916)

Set the Items property of the DataTable as below:

Colwork

Add a Button and set the OnSelect property to filter only propositions made by X as below:

ClearCollect(Colwork,Filter(Colwork,Person.DisplayName="XX"))

Add a Button and set the OnSelect property to filter only legal law propositions from this week as below:

ClearCollect(Colwork,Filter(Colwork,Date>=Today()-Weekday(Today())+2 &&Date<=Today()+(9-Weekday(Today()))))

Add a Button and a TextInput to filter only changes with category XXX. Set the OnSelect property of the Button as below:

ClearCollect(Colwork,Search(Colwork,TextInput1.Text,"Category"))

Note: As to these Text columns' filtering, you can enter in the TextInput1 and use Search() to filter the DataTable.

In order to filter the DataTable correctly every click, I add a Button and set the OnSelect property to recover the collection:

ClearCollect(Colwork,Test0916)

Note: After every click of the Button, you should click the recover Button to recover the collection.

Best Regards,

Qi

[Elitezone]

@v-qiaqi-msft 

This is a bit hard, so maybe I'll start with a little step.

My data table have such a formula in Items:

Sort(Search(IdeaBox_1;SearchBox.Text;"Name";"Description");Date;If(SortDescending2;Descending;Ascending))

IdeaBox_1 is my data source - sharepoint list

SearchBox is my text input field. 

In this input field I can filter all data table by name and description.

Date is a date of send from sharepoint list.

I also have a button with formula:

UpdateContext({SortDescending2:!SortDescending2})

This allows me to additionally sort everything by the date.

Those filters works together greatly, but I would like to have additional filters:

1. Button that would filter only requests made by me

2. Button that would filter only requests made in X days (could work the same as input field data)

Best scenario would be that this button works dynamically (first click - show only mine, second click undo).

The biggest problem for me is changing this data table Sort/Search formula. Each new search should be included in that, and I am still having some problems with syntax.

[v-qiaqi-msft · Accepted solution]

Hi@Elitezone,

Based on the needs you mentioned, you could try the following formula I provided before.

Do you want to click a button twice to trigger and cancel triggering? If you want to click a button to filter dynamically, you could not achieve this currently in PowerApps. You should need several Buttons as many as your filter types. For example, if you want to filter based on user and date, you should need 2 Buttons.

1. Button that would filter only requests made by me

Set the OnVisible property of the current screen as below:

ClearCollect(Colwork,IdeaBox_1)

Set the Items property of the DataTable as below:

Sort(Colwork;Date;If(SortDescending2;Descending;Ascending))

Add a Button and set the OnSelect property to filter only requests made by yourself as below:

ClearCollect(Colwork,Filter(IdeaBox_1,Person.DisplayName=User().FullName))

Note: The above formula helps you filter the requests made by the current user who is yourself. 

2. Button that would filter only requests made in X days (could work the same as input field data)

Add a Button and set the OnSelect property as below:

ClearCollect(Colwork,Filter(Colwork,Date>=Today()-Value(TextInput1.Text) &&Date<=Today()))

Note: The above formula helps you filter the date based on the input data within the TextInput. If you want to filter the records in 3 days, you could enter 3 within the TextInput.

3. As the 'Name' and 'Description' filtering based on the TextInput entry, I think adding a single button to filter could work.

Add a Button and set the OnSelect property as below:

ClearCollect(Colwork,Search(Colwork,TextInput1.Text,"Description","Name"))

4.In order to filter the DataTable correctly every click, I add a Button and set the OnSelect property to recover the collection:

ClearCollect(Colwork,Test0916)

Note: The above formula helps you to recover the DataTable after every click.

Best Regards,

Qi