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
Users Online
Currently online: 220 members 6,372 guests
Please welcome our newest community members: