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

Sort by multiple columns

Hello,

 

I'm new to PowerApps so hopefully my question is easy to answer.

 

I have a gallery that's based off a sharepoint list with a few columns, one of which is a choice column called in/out with two possible values, In or Out.

 

I need to show the Out items at the top of the list, so I can do this to the Items property of the gallery to achieve that:

Sort('list name','in/out'.Value,Descending)

But now I want the coulmns sorted by the last modified date, so the 'Outs' still show above the 'Ins' but within those two sections it's sorted by the modofied date. I tried this:

Sort(Sort('list name','in/out'.Value,Descending),Modified) 

However, this shows the most recently modified at the bottom of the gallery.

 

No matter how I structure the function I can't get the gallery to show Out above In at the same time as sort descending the Modified column.

 

For example this causes the modified to show the right way up, but puts the Ins at the top of the gallery and Outs at the bottom:

Sort(Sort('list name','in/out'.Value,Descending),Modified,Descending) 

How can I solve this?

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Sort by multiple columns

You can create an expression that would assign a numeric value to each of the entries in the list, in a way that this value is larger for what you want to display on top (easier to do, since you want to sort by the modified date/time in descending order), and smaller otherwise. If you use an expression such as the following:

Sort(
    'list name',
    If('in/out'.Value = "Out", 100000000, 0) +
        Value(Text(Modified, "[$-en-US]yyyymmdd.hhmmss")),
    Descending)

Then you should get what you need.

The idea here is to "promote" the "Out" values higher than any possible "In" values. Since the maximum computed value for modified would be 99991231.235959 (assuming December 31st, 9999, 23:59:59), then we add 100000000 to the rows that have their in/out column set to "Out".

5 REPLIES 5
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Sort by multiple columns

You can create an expression that would assign a numeric value to each of the entries in the list, in a way that this value is larger for what you want to display on top (easier to do, since you want to sort by the modified date/time in descending order), and smaller otherwise. If you use an expression such as the following:

Sort(
    'list name',
    If('in/out'.Value = "Out", 100000000, 0) +
        Value(Text(Modified, "[$-en-US]yyyymmdd.hhmmss")),
    Descending)

Then you should get what you need.

The idea here is to "promote" the "Out" values higher than any possible "In" values. Since the maximum computed value for modified would be 99991231.235959 (assuming December 31st, 9999, 23:59:59), then we add 100000000 to the rows that have their in/out column set to "Out".

WillPage
Level 8

Re: Sort by multiple columns

Perfect, thanks!

 

Could you please expand a bit on how this works? I can sort of understand how the 100000000 is getting added to the vale of Modified, but what's the significance of the If statement, and the , 0 after the 100000000.

 

Juts curious at this stage, now I have it working I can experiment.

Super User
Super User

Re: Sort by multiple columns

Hi @WillPage

The 0 after the 100000000 is the 'else' part of the If statement. So if the value of 'in/out' is 'out', it adds 100000000 to the value of 'Modified'. Otherwise, it adds 0.

WillPage
Level 8

Re: Sort by multiple columns

@timl

Thanks. How would this work if I wanted to sort by an alphanumeric column instead of modified date? Would I do something like this?

If('in/out'.Value = "Out", zzzzzzzz, 0)

 

Super User
Super User

Re: Sort by multiple columns

Hi @WillPage

If you want to sort by an alphanumeric column (for example, Title), you can use a formula that looks like this:

Sort(
    'list name',
    If('in/out'.Value = "Out", "___", "") & Title,
    Ascending
)

When the value of 'in/out' is 'out', this formula prefixes an underscore character to the start of the title to promote the sort sequence of the 'out' items to the top of the list.

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

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

Top Kudoed Authors
Users Online
Currently online: 288 members 3,908 guests
Please welcome our newest community members: