Unanswered

How to sort on SharePoint people column (show first the items where this people column is empty)

(0) Share

Report

Posted on by dreamsat

I created a PowerApp from a SharePoint list. One of my columns is of the type "People or Group". I would like to sort the gallery with all the list items. I would like to show first the items where the people column is empty. I try this but it is not working:

Sort(
If(
/* filter on date */
IsEmpty(PersoonCombobox.SelectedItems),

Filter(
CoronaWerkplekRooster,
Datum <= Date(
Year(DatePicker1.SelectedDate),
Month(DatePicker1.SelectedDate),
Day(DatePicker1.SelectedDate)
),
Datum >= Date(
Year(DatePicker1.SelectedDate),
Month(DatePicker1.SelectedDate),
Day(DatePicker1.SelectedDate)
)
)

,
/* filter on user */
Filter(
CoronaWerkplekRooster,
Persoon.Email = PersoonCombobox.Selected.Email
)
),
/* sort on Persoon. Show the items without Persoon first */
Persoon.DisplayName,
Ascending
)

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@dreamsat

Consider the following formula:

SortByColumns( Filter( AddColumns( CoronaWerkplekRooster, "persoonEmail", Persoon.Email, "persoonName", Persoon.DisplayName ), If(IsBlank(PersoonCombobox.Selected.Email), /* filter on date */ Datum <= DatePicker1.SelectedDate && Datum >= DatePicker1.SelectedDate, /* else - filter on user */ persoonEmail = PersoonCombobox.Selected.Email ) ), "persoonName", Ascending )

This should give you what you need.

Couple of things...The SelectedDate of your DatePicker is already a date. There is no need to take the individual year, month and day to convert it back to a date.

Also, this formula will always sort by person name. If you wanted the sort to change based on if there is a name selected from person name to date, then you can consider this formula instead:

SortByColumns( Filter( AddColumns( CoronaWerkplekRooster, "persoonEmail", Persoon.Email, "persoonName", Persoon.DisplayName ), If(IsBlank(PersoonCombobox.Selected.Email), /* filter on date */ Datum <= DatePicker1.SelectedDate && Datum >= DatePicker1.SelectedDate, /* else - filter on user */ persoonEmail = PersoonCombobox.Selected.Email ) ), If(IsBlank(PersoonCombobox.Selected.Email), "Datum", "persoonName"), Ascending )

This will sort by date when no name is selected and by name when a name is selected.

I hope this is helpful for you.

Was this reply helpful? Yes No
dreamsat 96 on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes Great, the second script works. I just see something I didnt think about it before. Is it possible to sort on a second column?

I would like to see first the items where column "Persoon.DisplayName" and column "Externe Persoon" are both empty. After this I would like to see the items where column "Persoon.DisplayName" is empty and column "Externe Persoon" is filled. Something like this:

SharePoint column "Externe Persoon" is of type single line of text.

"Persoon.DisplayName" | Externe Persoon
"" | ""
"" | "test2"
"test3" | "test3"
"test4" | "test4"

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@dreamsat

Yes, you can add additional columns to the formula:

SortByColumns( Filter( AddColumns( CoronaWerkplekRooster, "persoonEmail", Persoon.Email, "persoonName", Persoon.DisplayName ), If(IsBlank(PersoonCombobox.Selected.Email), /* filter on date */ Datum <= DatePicker1.SelectedDate && Datum >= DatePicker1.SelectedDate, /* else - filter on user */ persoonEmail = PersoonCombobox.Selected.Email ) ), If(IsBlank(PersoonCombobox.Selected.Email), "Datum", "persoonName"), Ascending, "Externe Persoon", Ascending )

Was this reply helpful? Yes No
dreamsat 96 on at

Like (0)

Report
Copy link

Link copied!

I modified yours like this but it is still not sorting like I expect:

SortByColumns(
Filter(
AddColumns(
CoronaWerkplekRooster,
"persoonEmail", Persoon.Email,
"persoonName", Persoon.DisplayName,
"externePersoon", 'Externe persoon'
),
If(IsBlank(PersoonCombobox.Selected.Email),
/* filter on date */
Datum <= DatePicker1.SelectedDate &&
Datum >= DatePicker1.SelectedDate,
/* else - filter on user */
persoonEmail = PersoonCombobox.Selected.Email
)
),
If(IsBlank(PersoonCombobox.Selected.Email), "Datum", "persoonName"),
Ascending,
"externePersoon",
Ascending
)

I expect the green items as first.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@dreamsat

Actually, based on what we put together before - what you are seeing is correct. In that screen shot, you did not have a person selected, so your sort was by Date and then by the External name.

If you select a person, you should see your sort as you want.

We based the formula before on if a person was selected because that was how you had your original formula.

If you always want it sorted by blank name and then blank external name as you mention, then get rid of the If statement that selects the Datum column if no person is selected in the SortBy and just make it persoonName.

Was this reply helpful? Yes No
dreamsat 96 on at

Like (0)

Report
Copy link

Link copied!

Hi @RandyHayes lets forget the if statement about the date picker and people picker. How to sort like I would like to have without the people picker? I removed the if statements:

SortByColumns(
Filter(
AddColumns(
CoronaWerkplekRooster,
"persoonEmail", Persoon.Email,
"persoonName", Persoon.DisplayName,
"externePersoon", 'Externe persoon'
),
/* filter on date */
Datum <= DatePicker1.SelectedDate &&
Datum >= DatePicker1.SelectedDate
),
"persoonName",
Ascending,
"externePersoon",
Ascending
)

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@dreamsat

Sorry I didn't get the question in that...is that working or not producing what you want?

Was this reply helpful? Yes No
dreamsat 96 on at

Like (0)

Report
Copy link

Link copied!

I would like to sort as follow. I am able to remove the if statement if it will be easier to implement this expected sorting.

"Persoon.DisplayName" | Externe Persoon
"" | ""
"" | "test2"
"test3" | "test3"
"test4" | "test4"

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@dreamsat

I guess my question is that you posted a formula last and I didn't see a question. My question then is, what are you seeing now with that formula? How is it sorting and how is it not sorting as you want. I know where you're trying to get, but that formula should do it...where is it going wrong?

Was this reply helpful? Yes No