Filter 2 Sharepoint lists in power app with data in different galleries with drop down selection

[EmmaWatt]

Hello Power App Experts,

I am stuck and would like your expertise to help me resolve it.

I have a leave request app which is largely based on the template, which has a Sharepoint list that details the employee's entitled holiday per year - 'Leave Entitlement' and the year is in the list's Title column and just has 2022, 2023 etc per employee's annual leave entitlement.  Then the second Sharepoint list is the 'Leave Requests' which contains each employee's holiday request with date columns for from and to.

In the app the leave entitlement details are in 1 gallery and the individual leave request in is a second gallery, which all works but what I need to do now as we are now moving into a new year I need to add a drop-down to select either a previous year's data or a future year for advance holiday booking and on opening the app the year defaults to current year's data, where the drop-down manages/matches both the 'Leave Entitlement' and the 'Leave Requests' year at the same time.

I hope there is someone who would be able to assist me here.

Many thanks in advance,

[BCBuizer]

Hi @EmmaWatt ,

You can achieve this by adding a condition to the filter function in the Items property for both galleries:

Filter(
   DataSource,
   <other conditions>,
   YearColumn = YearDropDown.Selected.Value
)

[EmmaWatt]

Thanks for this, I already have in each gallery a filter:-

Gallery1=

If(
"Pending" in _requestTypeFilter,
Filter(
'Leave Request',
'Created By'.Email = User().Email && (Status.Value = "Pending Manager Approval" || Status.Value = "Pending HR Approval")
),
Filter(
'Leave Request',
'Created By'.Email = User().Email && (Status.Value = _requestTypeFilter || _requestTypeFilter = "All")
)
)

and Gallery 2=

Filter('Leave Entitlement',Employee.Email = User().Email)

My coding is beginner and not sure how you add this extra filter level in and nor do I follow what <other condition> is (sorry just learning and trying to understand)

And when adding the drop-down for the employee to select a previous year to see past holiday request and entitlement what is the needed formula for this?

Thanks

[Tripondisco]

What does your data structure look like? You should be able to apply multiple conditions with the same filter assuming it's all in the same list, for example: Filter(Table,condition1,condition2...)

[Rajkumar_404]

Hi 

For your first gallery:

If(
"Pending" in _requestTypeFilter,
Filter(
'Leave Request',
'Created By'.Email = User().Email && 
(Status.Value = "Pending Manager Approval" || Status.Value = "Pending HR Approval") &&
Year(YearColumn ) = DropdownYear.Selected.Value
),
Filter(
'Leave Request',
'Created By'.Email = User().Email && 
(Status.Value = _requestTypeFilter || _requestTypeFilter = "All") &&
Year(YearColumn) = DropdownYear.Selected.Value
)
)

For the second gallery:

Filter('Leave Entitlement', Employee.Email = User().Email && Year(YearColumn) = DropdownYear.Selected.Value)

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

[EmmaWatt]

Amazing thank you, so that is now filtering the 2 galleries with the 1 drop down selection, but now seeing delegation warnings on these galleries. Also what is the default code on the drop-down to set the year as this current year?

[Rajkumar_404]

Default code on the drop-down to set the year as this current year.

Drop Down item property : 

[Year(Today())]

Avoid Delegation warning :

Refer this link : Power Apps Delegation – SharePoint – Practical Power Apps

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

[EmmaWatt]

The link on Delegation is good but doesnt help me on how I need to change my formula on the 2 galleries that use the Year for the filter. I am not that experienced in the coding

[Rajkumar_404]

What specific data type is used for the Year Column within your SharePoint List?

Share your code! 

[EmmaWatt]

its a date column, if this makes it more complicated It can be changed to just a single text line where just a year is entered as text eg 2023