Answered

Sort and Filter functions (for Data table)

(0) Share

Report

Posted on by Andy-MyCXUk

Hi,

Long time reader but this is the first I've not found support through existing threads, hoping one of you experts can help me.

I've created a solution for projects within PowerApps, part of this has a home screen which presents 2 different Data Tables

Table1 - Datasource Workstack
Has a filter function that currently works fine in displaying all Active projects (unique column) but can't seem to have it sort by Start Date (unique column) - any thoughts on a solution?

Table2 - Datasource Tasks
Has a filter function displaying all incomplete tasks and again this works fine but I can't seem to have it sort by Completion Date(unique column) - is it also possible to have the Data Table row fill to red if the completion date is less than or equal to today()?

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

RusselThomas 4,014 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi Andy,

SortByColumns() should work, but it may be related to the data type of the date column if you're not getting the results you expect - is the data text or an actual date?

if it's an actual date;
SortByColumns(source, "StartDate", Ascending)
should work....

I'm not aware of any way to conditionally format rows or fields in a data table - they're quite limited in terms of customisation, you might be better of using a gallery formatted to look like a data grid and there you have plenty of options.

Hope this helps,

RT
v-zhta-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ Andy-MyCXUk

Could you please share a bit more about the error message within your PowerApps?

I did a test on my side and it worked fine.

I create two Excel table and set the data column to date format (then update two tables to One_Drive):

Then, create two data tables in powerapps:

Filter and sort data tables:

Workstack table:

Datasourcetask table:

Here are some check points you can try:
1: check the formula whether the symbol is missing or the column name is wrong;
2: determine column “completiondata” and “startdate” data type is date, otherwise the sort function won’t work correctly;

The introduction of “SortByColumns” :
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort

I don’t find the way to fill Data Table row to red if the completion date is less than or equal to today () but you can do that in a gallery using the following formula:

Fill = If(ThisItem.completetionDate<=Today(),RGBA(255, 0, 0, 1),RGBA(0,0,0,0))

Hope this helps.

Best regards,
Zhen
Andy-MyCXUk 6 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi guys,

Great feedback but I'd just like to give you more detail.

Table1 Datasource Workstack

Current formula

Filter(workstackdata, searchproject.text in TitleWorkStatus.value="Active") - what do I need to do to then have this sorted by a column called project start date.

Table2 Datasource tasklists

Current formula

Filter(tasklistdata, status.value ="Incomplete") - again would be great to have this sort by a column for completion date.

Also, looking to add a label at the top given the fill red isn't available in data tables, the individuals I'm working with don't want to utilize gallery function.

Currently have the below

"Pending Tasks: " Countif(tasklistdata, status.value ="Incomplete") - how can I then have this only count if based on the "completion date" column being less than or equal to today()?

Many thanks in advance, I appreciate your expertise given I'm quite new to this.
Verified answer

RusselThomas 4,014 on at

Like
a
(0)

Report
Copy link

Link copied!
Hey Andy,

Check out the SortByColumns() function, should apply as follows;

SortByColumns( Filter(workstackdata, searchproject.text in TitleWorkStatus.value="Active"), "ProjectStartDateColumn", Descending)
then to the next one;

SortByColumns( Filter(tasklistdata, status.value ="Incomplete"), "CompletionDateColumn", Descending)
Lastly, for the label try;

CountIf(tasklistdata, status.value ="Incomplete" && DateDiff(Today(), CompletionDateColumn)<=0)
Hope this helps 🙂

RT
Andy-MyCXUk 6 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you so much RT and Zhen!