cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thunter
Level 8

Sort Data Table using dropdowns

I have a Data Table that pulls columns from my DataSouce. I want the user to be able to pull data by Quarter and Year using 2 dropdowns. For example, if the user wanted to see all the data for Quarter 3 of 2018, they would dropdown to Q3 and 2018. I am able to sort by using just one dropdown, is there a way to sort using 2 dropdowns? Thanks for any help!

27 REPLIES 27
Super User
Super User

Re: Sort Data Table using dropdowns

Hi @thunter 

Just add the additional columns to your formula with a sort order following the column name. They will be sorted in order. 

SortByColumns(Filter(datasource, etc.), "Column1", Ascending, "Column2",Descending, "Column3",Ascending)

from the documentation:

Sort( Sort( IceCream, OnOrder ), Quantity )

SortByColumns( IceCream, "OnOrder", Ascending, "Quantity", Ascending )
Sorts IceCream first by its OnOrdercolumn, and then by its Quantitycolumn. Note that "Pistachio" rose above "Vanilla" in the first sort based on OnOrder, and then together they moved to their appropriate place based on Quantity.

 

shailendra74
Level 8

Re: Sort Data Table using dropdowns

The filter criteria should include start and end dates based on the Quarter & Year dropdown selection. Try as follows:

 

UpdateContext(
    {
        STARTDATE: If(
            quarterdd.Selected.Value = "Q1",
            DateValue(
                Concatenate(
                    "1/1/",
                    Text(yeardd.Selected.Value)
                )
            ),
            If(
                quarterdd.Selected.Value = "Q2",
                DateValue(
                    Concatenate(
                        "4/1/",
                        Text(yeardd.Selected.Value)
                    )
                )
            )
        ),
        ENDDATE: If(
            quarterdd.Selected.Value = "Q1",
            DateValue(
                Concatenate(
                    "3/31/",
                    Text(yeardd.Selected.Value)
                )
            ),
            If(
                quarterdd.Selected.Value = "Q2",
                DateValue(
                    Concatenate(
                        "6/30/",
                        Text(yeardd.Selected.Value)
                    )
                )
            )
        )
    }
)

Use STARTDATE and ENDDATE in your filter criteria.  You can add the rest Q3 & Q4.  

 

OnVisible of the screen, set to default date for both START and END dates

 

Final code looks something like this:

SortByColumns(Filter('[dbo].[wynne_incidents]', Inc_date>=STARTDATE && Inc_date<=ENDDATE), "incident_num", Ascending)

thunter
Level 8

Re: Sort Data Table using dropdowns

@shailendra74  Thanks for all the help and info!

 

So does the Update Context formula go in Items on the Data Table?

shailendra74
Level 8

Re: Sort Data Table using dropdowns

It is to declare variables having scope with the screen.


Thanks.
thunter
Level 8

Re: Sort Data Table using dropdowns

I am just not sure where to put the formula, does it go on the Data Table? I thought the SortColumns formula went on the Items portion of the Data Table. Please clarify. Thank you.

shailendra74
Level 8

Re: Sort Data Table using dropdowns

Hi @thunter 

 

The formula I mentioned would be in onChange of both dropdown. So on change of any dropdown the dates will be updated as per quarter selected.

 

As mentioned earlier -

SortByColumns(Filter('[dbo].[wynne_incidents]', Inc_date>=STARTDATE && Inc_date<=ENDDATE), "incident_num", Ascending) . will be in item property of Datatable.

 

Thanks.

 

 

thunter
Level 8

Re: Sort Data Table using dropdowns

@shailendra74 

 

I put all the formulas in the correct areas and now get an error on the formula in the Items of Data Table

 

"The requested operation is invalid. Server Response: We cannot apply operator < to types of DateTimeZone and Date."

thunter
Level 8

Re: Sort Data Table using dropdowns

@shailendra74 

 

You also mentioned "OnVisible of the screen, set to default date for both START and END dates"

 

How do I set the default dates?

shailendra74
Level 8

Re: Sort Data Table using dropdowns

Hi @thunter 

 

UpdateContext({STARTDATE: Text(Now(), "mm/dd/yyyy"), ENDDATE: Text(Now(), "mm/dd/yyyy") })

 

Thanks.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 48 members 4,745 guests
Please welcome our newest community members: