cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Sorting column from dropdown between dates

Following the great solution I got in 

 

https://powerusers.microsoft.com/t5/General-Discussion/Text-seach-in-Gallery-no-longer-works-when-se... that allows me to sort based on variables, I wanted to advance a bit to sort in a certain timeframe. 

 

Situation: My SP List contains three date colums. What I would like to do is something similar as above; I want to pass a column name to filter on, and two dates. 

 

I start with declaring the three:

DatumStart (startdate)

DatumEind (enddate)

DatumFilter (column name to filter on)

 

OnVisible = ClearCollect(LedenColl,Ledentst);Set (Filter1, "NoValue");Set (Filter2, "NoValue"); Set (DatumStart,"NoValue"); Set (DatumEind,"NoValue"); Set (DatumFilter, "NoValue")

On that same screen: 

 

OnSelect: 

Set(DateFilter,"Opzegdatum"); Set(DatumStart,Date(Year(Today()), 1, 1)); Set(DatumEind,Date(Year(Today()), Month(Today()) + 1, 0))

On the screen containing the gallery, I sort the gallery as follows: 

 

SortByColumns(
   
	Filter(LedenColl,
	
	(Filter1 = "NoValue" || Type.Value = Filter1),  

	(DatumFilter = "NoValue" || DatumFilter >= DatumStart && DatumFilter >= DatumEind),
	Lijstfilter_1.Text in Lidno || Lijstfilter_1.Text in Achternaam
         ),"Achternaam",Ascending)

But all three variables do not seem to work. When I check using labels, I can see that only the DatumFilter is passed, not the dates. 

 

 

 

 

 

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Sorting column from dropdown between dates

Hi @TheAudioPolice,

Could you please share a bit more about your scenario?

Do you want to save your column name within a global variable, then filter your Gallery items based on this variable?

Based on the needs that you mentioned, I have made a test on my side, if you want to save your column name within a global variable, then filter your Gallery items based on this variable, I afraid that there is no way to achieve your needs in PowerApps currently.

Currently, within PowerApps, when you want to filter a data source based on a column, you must provide a specific column name rather than a variable.

As an alternative solution, I have made a test on my side, please take a try with the following workaround:

Modify the formula within the OnVisible proeprty of your screen to following:

ClearCollect(LedenColl, Ledentst); Set(Filter1, "NoValue"); Set(Filter2, "NoValue")

Modify the formula within the OnSelect property within the same screen to following:

Set(DatumStart, Date(Year(Today()), 1, 1));
Set(DatumEind, Date(Year(Today()), Month(Today()) + 1, 0))

Add a "Filter" Icon button within your app, name it as "Opzegdatum" (Date column name that you mentioned), set the OnSelect proeprty to following:

ClearCollect(
LedenColl,
Filter(
Ledentst,
(Filter1 = "NoValue" || Type.Value = Filter1), (Opzegdatum >= DatumStart && Opzegdatum >= DatumEind) /* <-- Opzegdatum column (Cancellation date) represents the Date column in your SP list */
)
)

Note: If you want to filter your Gallery items based on other Date columns, please take a try to add corresponding "Filter" Icon buttons as above, then try above solution I provided to filter the LedenColl Collection. E.G.

ClearCollect(
LedenColl,
Filter(
Ledentst,
(Filter1 = "NoValue" || Type.Value = Filter1), (DateColumn2 >= DatumStart && DateColumn2 >= DatumEind) /* <-- DateColumn2 column represents the Date column in your SP list */
)
)

Set the Items property of the Gallery to following:

SortByColumns(
	Filter(LedenColl, Lijstfilter_1.Text in Lidno || Lijstfilter_1.Text in Achternaam),
"Achternaam",
Ascending
)

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Sorting column from dropdown between dates

Hi & Thanks. 

 

What I want to achieve is filtering a list with one of three date colums between a certain date, triggered by the onSelect of an icon (see screenshot). 

 

If the column name cannot be set by global variable, that indeed poses a problem.

 

Add a "Filter" Icon button within your app, name it as "Opzegdatum" (Date column name that you mentioned), set the OnSelect proeprty to following:

From your workaround, I get stuck at renaming the icon; there already is a field within an edit screen with this name. I tried adding the 

 

Set(DatumStart, Date(Year(Today()), 1, 1));
Set(DatumEind, Date(Year(Today()), Month(Today()) + 1, 0))

bit at the beginning of the icon.onselect, so it looks like this: 

 

Set(
    DatumStart,
    Date(
        Year(Today()),
        1,
        1
    )
);
Set(
    DatumEind,
    Date(
        Year(Today()),
        Month(Today()) + 1,
        0
    )
);
ClearCollect(
    LedenColl,
    Filter(
        Ledentst,
        Filter1 = "NoValue" || Type.Value = Filter1,
        Opzegdatum >= DatumStart && Opzegdatum >= DatumEind/* <-- Opzegdatum column (Cancellation date) represents the Date column in your SP list */
    )
);
Set(
    CurrentVariable,
    "Dit jaar Opgezegd"
);
Navigate(
    Lijstscherm_nieuw,
    ScreenTransition.Cover
)

But the list shows up empty.

 

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,521)