Unanswered

Sorting column from dropdown between dates

(0) Share

Report

Posted on by TheAudioPolice

Following the great solution I got in

https://powerusers.microsoft.com/t5/General-Discussion/Text-seach-in-Gallery-no-longer-works-when-set-from-other-screen/m-p/210058 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.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (0)

Sort by

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
TheAudioPolice 29 on at

Like
a
(0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No