Applying 2 filters to a gallery using a dropdown

(0) Share

Report

Posted on by caslasgee

223

Hi All, I have a gallery and 2 drop down list. One is filter by a value while the other is by year as shown below.

Filter 1.PNG

I have been able to apply the first filter by value using

Filter(Unit_JRP_Funding_Activity,UnitID=galunit.Selected.ID)

I will also like to be able to filter by using date as well from the year drop down where i used collect to populate the years using

Distinct(ColYear,Year(Urgent_Date))

How do i merge both filters together. Where my Year label name is drpunitfilterdate

Thanks.

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @caslasgee,

Thank you for the clear documentation. Below you can find a filter function that will only return records where the unitID matches the selected ID and Urgent_Date occurs within the selected year:

Filter( Unit_JRP_Funding_Activity, Urgent_Date >= Date(drpunitfilterdate.Selected.Value,1,1), Urgent_Date <= Date(drpunitfilterdate.Selected.Value,12,31), UnitID = galunit.Selected.ID ) //Note: If you allow empty selections you might want to use (example for one of the conditions): //IsBlank(drpunitfilterdate.Selected) || Urgent_Date >= Date(drpunitfilterdate.Selected.Value,1,1)

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

Was this reply helpful? Yes No
caslasgee 223 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @LaurensM . Thank you for your response. It actually did the Job by using the date of the Unit_JRP_Funding_Activity list instead of using the Unit_UrgentNeeds list. Apologies i forgot to mention that there is a relational column that feeds the gallery. Showing the both list below,

The above list only feeds the gallery with the 'Title' column just to display the activity that is currently being edited or viewed in the gallery.

While the above is the main list that shows the amounts and also the dates with i would want to use to achieve the year filter.

I hope i am clear enough. Thanks, In anticipation.

Was this reply helpful? Yes No
LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @caslasgee,

I'm afraid I don't fully understand what you would like to achieve. As I understand it: Each line in the gallery is a record from the Activity list. Why is the Urgent_Date filter needed? Would you like to display the total value of the related UrgentNeed items for that particular year in the other columns?

Was this reply helpful? Yes No
caslasgee 223 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @LaurensM . Each line is a record - (Which is true) and the records are drawn from 2 related list. Just to be clear enough, Below is a screen shot of one of the labels added from one of the lists called Unit_UrgentNeed and the gallery it self filters the Activities of the Unit based on the drop down above.

From the above image, 1 and 2 Fetches information from the urgentNeeds list while leveraging the Title Unit_Activity_list. When you select view, It takes you to another screen where you see a complete view of the subset of the activity list and the urgentNeeds information as shown below.

Every information on this screen shows UrgendNeed information which includes Amounts and Date.
So my request is if possible, I would like filter the gallery based on the Urgent_Date of the UrgentNeed list. From the help you provided initially, If i am to rewrite in a very funny/Incorrect way, I write it this way
Filter(Unit_JRP_Funding_Activity, ActID=ThisItem.ID, JRP_Year >= Date(drpunitfilterdate.Selected.Value,1,1), JRP_Year <= Date(drpunitfilterdate.Selected.Value,12,31),UnitID = galunit.Selected.ID)
which obviously is not making sense. I really hope i am a bit clear this time.
Thank you.

Was this reply helpful? Yes No
LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@caslasgee,

Ok thank you, I now understand the relationship between the tables and both galleries. However it is not fully clear to me what gallery you would like to filter:

Do you want to extend the filter of the first gallery by selecting a year and only showing records where the JRP_Year is included (including an additional filter on the Sum in the labels)?

Is something wrong with the filter of the second gallery?

Do you want to create a new gallery when selecting one of the Operational Cost lines on the second screen?

Was this reply helpful? Yes No
caslasgee 223 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @LaurensM . Thanks for the follow up questions.
I just would like to show the Sum of all 'operational cost' in the first screen in all the JRP_Unit_Activity in 2023.
ie Using the snapshot previously sent (Screen 1), All the Title values displayed on the gallery all have Operational cost Per year ,eg 'Child Protection Awareness Raising', 'Child protection-Community based child protection' etc. I just want to show the total of all operational costs for all values within the gallery which already have a filter applied based on Another list which i never mentioned called "Unit_Funding" where each JRP_Unit_Activity list is tied to using a relational column (ID).

Permit me to share all lists (Dummy version) for better understanding.

For the same scenario (An overview).
1. I have a gallery (galunit) that has a list - Funding_Unit (Title). When you select the Next Arrow icon, It will Navigate to a Screen (Main) that has a gallery called galmain.
2. The galmain has a filter
Filter(JRP_Funding_Activity,UnitID=galunit.Selected.ID)
Also, in the Onvisible of the main screen, i have this
ClearCollect(ColYear,Unit_UrgentNeed)
And finally, in the main screen, I have a drop down(drpyear) that contains the following which list out the years based on the Clearcollect -
Distinct(ColYear,Year(Urgent_Date))
So my question goes, using 'Protection' (in Funding_Unit List) for example, How do I calculate the operational Cost for 2023 while leveraging the Drop Down.
Apologies for the lengthy text. I hope this clears the air.
Thanks.

Was this reply helpful? Yes No
Verified answer

LaurensM 12,516 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@caslasgee,

Thanks for the explanation.

I see that galMain has a few calculations in it already - can we leverage these to calculate the total cost of operations instead of creating another filter? This would allow us to avoid any code duplication or additional, complex filters.

Note: If you want to display the total cost on another screen (not Main), you might want to use a Filter instead of refering to controls from another screen.

To achieve the calculations via labels you will have to adjust the galMain slightly:

(1) Add an invisible label with the Sum calculation code (don't use the Text() function to style it as a currency, we need it to remain a number)

This is only needed for the Sum labels that we need to use to calculate the total operations cost.

(2) Use the visible label to refer to the invisible label and style it as a currency

Text(Value(InvisLabel1.Text), "$ ##,###.00")

(3) Place a label outside of galMain to display the total operational cost

//Refer to the invisible label Sum(galmain.AllItems, Value(InvisLabel1.Text)) //Should the operational cost be a sum of multiple labels for each activity use the following structure: Sum(galmain.AllItems, Value(InvisLabel1.Text) + Value(InvisLabel2.Text))

I hope this helps!

Was this reply helpful? Yes No
caslasgee 223 on at

Like
a
(0)

Report
Copy link

Link copied!

@LaurensM, Thanks for your help. It worked as expected.

Was this reply helpful? Yes No