cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheAudioPolice
Level: Powered On

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
Community Support Team
Community Support Team

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.
TheAudioPolice
Level: Powered On

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
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,419)