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

How to sort "All" request type

Hi Guys,
Does anyone know how to execute this process? 
I wanted to display All the request(Pending,Approved,Declined,Processed) type under "All" column
all.PNG
This is my formula

If(_search && !_requestTypeFilter="All",Sort(Filter('[dbo].[Request]', Status = 1 && search.Text in SequenceNo && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && If(_search,ApprovalStatus = _requestTypeFilter) && Text(RequestDates.SelectedDate) in TransactionDate),SequenceNo,Descending)
,
_search && _requestTypeFilter="All" ,Sort(Filter('[dbo].[Request]',search.Text in SequenceNo && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && Status =1),SequenceNo,Descending),

Sort(
If( _requestTypeFilter="Pending",
Filter('[dbo].[Request]',Status =1 && ApprovalStatus = "Pending" && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName)),
Filter('[dbo].[Request]',Status =1 && ApprovalStatus=_requestTypeFilter && Text(RequestDates.SelectedDate) in TransactionDate && If(_managerView, Approver = _myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName)) ), SequenceNo, Descending))


Thank you!



 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to sort "All" request type

Hi @Big_S ,

Please consider modify your formula as below:

If(
_search && !_requestTypeFilter = "All",
Sort(Filter('[dbo].[Request]', Status = 1 && search.Text in SequenceNo && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && If(_search, ApprovalStatus = _requestTypeFilter) && Text(RequestDates.SelectedDate) in TransactionDate),SequenceNo,Descending), _search && _requestTypeFilter = "All" ,
Sort(Filter('[dbo].[Request]',search.Text in SequenceNo && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && Status =1),SequenceNo,Descending), Sort( If(
_requestTypeFilter="All", Filter('[dbo].[Request]',Status =1 && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && Value(Text(TransactionDate, "yyyymmdd")) >= Value(Text(RequestDates.SelectedDate, "yyyymmdd")) && Value(Text(TransactionDate, "yyyymmdd")) <= Value(Text(RequestDates_1.SelectedDate, "yyyymmdd"))), Filter('[dbo].[Request]', Status =1 && ApprovalStatus = _requestTypeFilter && If(_managerView, Approver = _selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && Value(Text(TransactionDate, "yyyymmdd")) >= Value(Text(RequestDates.SelectedDate, "yyyymmdd")) && Value(Text(TransactionDate, "yyyymmdd")) <= Value(Text(RequestDates_1.SelectedDate, "yyyymmdd"))) /* <-- Modify formula here */
),
SequenceNo, Descending
) )

 

Please take a try with above solution, apply the date filter to other Approval status (Approved, Declined and Processed), check if the issue is solved.

 

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.

View solution in original post

10 REPLIES 10
lucia
Level 8

Re: How to sort "All" request type

Hey,

 

if you want to show all items, I think it means that it does not depends what the status column value is. I would just Set(value, "all") and if this value is all in your gallery i would chnage the formula with if statement accordingly.

 

For example If(value="all", Filter(Datasource, your filters which do not include status), 
                       value="pending", Filter(Datasource, Status="Pending", rest of the filters)...)

 

Hope this helps

Big_S
Level: Powered On

Re: How to sort "All" request type

Hi @lucia ,

 

Thank you so much, I wanted to create a sort formula for each "All,Approved,Pending,Declined,Processed") but i got errors when I add the Pending this is my formula how to do it?.

If(_search && !_requestTypeFilter="All",Sort(Filter('[dbo].[Request]', Status = 1 && search.Text in SequenceNo && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && If(_search,ApprovalStatus = _requestTypeFilter) && Text(RequestDates.SelectedDate) in TransactionDate),SequenceNo,Descending)
,
_search && _requestTypeFilter="All" ,Sort(Filter('[dbo].[Request]',search.Text in SequenceNo && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && Status =1),SequenceNo,Descending)
,

Sort(
If( _requestTypeFilter="All",
Filter('[dbo].[Request]',Status =1 && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && DateValue(Text(TransactionDate),"en") >= DateValue(Text(RequestDates.SelectedDate)) && DateValue(Text(TransactionDate),"en") <= DateValue(Text(RequestDates_1.SelectedDate))),
Filter('[dbo].[Request]',Status =1 && ApprovalStatus=_requestTypeFilter && Text(RequestDates.SelectedDate) in TransactionDate && If(_managerView, Approver = _myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName)) ), SequenceNo, Descending)

Sort(
If( _requestTypeFilter="Pending",
Filter('[dbo].[Request]',Status =1 && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && DateValue(Text(TransactionDate),"en") >= DateValue(Text(RequestDates.SelectedDate)) && DateValue(Text(TransactionDate),"en") <= DateValue(Text(RequestDates_1.SelectedDate))),
Filter('[dbo].[Request]',Status =1 && ApprovalStatus=_requestTypeFilter && Text(RequestDates.SelectedDate) in TransactionDate && If(_managerView, Approver = _myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName)) ), SequenceNo, Descending))

Community Support Team
Community Support Team

Re: How to sort "All" request type

Hi @Big_S ,

Could you please share a bit more about your issue?

Do you mean that the formula you provided could not display corresponding records when you click the "All" button?

Further, do you create your app based on the "Leave Request" template app?

 

I assume that you create your canvas app based on the "Leave Request" template app, is it true? Based on the formula that you provided, I think there is something wrong with it. I have made a test on my side, please take a try with the following workaround:

I assume that the OnVisible property of the LoginScreen in your app has been set to following formula:

//whenever user navigates to this page, set their approver to their manager
Set(_selectedApprover, Office365Users.Manager(_myProfile.Id));
Set(_selectedApproverPhoto, If(Office365Users.UserPhotoMetadata(_selectedApprover.Id).HasPhoto, Office365Users.UserPhoto(_selectedApprover.Id)))

Please consider modify your formula as below (Set the Items property of the GalleryRequests gallery to following😞

If(
_search && !_requestTypeFilter="All",
Sort(Filter('[dbo].[Request]', Status = 1 && search.Text in SequenceNo && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && If(_search, ApprovalStatus = _requestTypeFilter) && Text(RequestDates.SelectedDate) in TransactionDate),SequenceNo,Descending), _search && _requestTypeFilter="All", Sort(Filter('[dbo].[Request]', search.Text in SequenceNo && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && Status =1), SequenceNo, Descending), Sort( If(
_requestTypeFilter="Pending", Filter('[dbo].[Request]', Status =1 && ApprovalStatus = "Pending" && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName)), Filter('[dbo].[Request]', Status =1 && ApprovalStatus=_requestTypeFilter && Text(RequestDates.SelectedDate) in TransactionDate && If(_managerView, Approver = _selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName))
),
SequenceNo, Descending
)
)

Note: I assume that there is a Approver column existed in your '[dbo].[Request]' table, the Approver column should be compared with _selectedApprover variable.

Please take a try with above solution, then check if the issue is solved.

 

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

Re: How to sort "All" request type

Hi @v-xida-msft 

Yes you're right, created the app based on Leave Request this is my issue
1. I wanted to Add From-To date filter(All,Approved,Decline,Process)
2. If Pending it will not apply the From-To date filter.
fromto.PNG

Community Support Team
Community Support Team

Re: How to sort "All" request type

Hi @Big_S ,

Based on the formula you provided, I think there is something with the If function.

 

Please consider modify your formula as below:

 

If(
_search && !_requestTypeFilter = "All",
Sort(Filter('[dbo].[Request]', Status = 1 && search.Text in SequenceNo && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && If(_search, ApprovalStatus = _requestTypeFilter) && Text(RequestDates.SelectedDate) in TransactionDate),SequenceNo,Descending), _search && _requestTypeFilter = "All" ,
Sort(Filter('[dbo].[Request]',search.Text in SequenceNo && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && Status =1),SequenceNo,Descending), Sort( If(
_requestTypeFilter="All", Filter('[dbo].[Request]',Status =1 && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && DateValue(Text(TransactionDate),"en") >= DateValue(Text(RequestDates.SelectedDate)) && DateValue(Text(TransactionDate),"en") <= DateValue(Text(RequestDates_1.SelectedDate))),
_requestTypeFilter="Pending",
Filter('[dbo].[Request]', Status =1 && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName) && DateValue(Text(TransactionDate),"en") >= DateValue(Text(RequestDates.SelectedDate)) && DateValue(Text(TransactionDate),"en") <= DateValue(Text(RequestDates_1.SelectedDate))), Filter('[dbo].[Request]', Status =1 && ApprovalStatus=_requestTypeFilter && Text(RequestDates.SelectedDate) in TransactionDate && If(_managerView, Approver = _selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName))
),
SequenceNo, Descending
) )

 

Please take a try with above solution, check if the issue is solved.

 

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

Re: How to sort "All" request type

Hi @v-xida-msft 
This formula works but I cant filter the From-To date of the Approved, Declined and Process
Sort(
If(_requestTypeFilter="Pending",
Filter('[dbo].[Request]',Status =1 && ApprovalStatus="Pending" && If(_managerView, Approver =_myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName)),
Filter('[dbo].[Request]',Status =1 && ApprovalStatus=_requestTypeFilter ||_requestTypeFilter = "All" && DateValue(Text(TransactionDate),"en") >= DateValue(Text(RequestDates.SelectedDate)) && DateValue(Text(TransactionDate),"en") <= DateValue(Text(RequestDates_1.SelectedDate)) && If(_managerView, Approver = _myProfile.UserPrincipalName, Requester = _myProfile.UserPrincipalName)) ), SequenceNo, Descending))

Community Support Team
Community Support Team

Re: How to sort "All" request type

Hi @Big_S ,

Please consider modify your Date Filter condition as below:

Sort(
     If(
_requestTypeFilter="Pending", Filter('[dbo].[Request]',Status =1 && ApprovalStatus="Pending" && If(_managerView, Approver =_selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName)), /* <-- Please type _selectedApprover rather than _myProfile */ Filter('[dbo].[Request]',Status =1 && (ApprovalStatus = _requestTypeFilter || _requestTypeFilter = "All") && Value(Text(TransactionDate, "yyyymmdd")) >= Value(Text(RequestDates.SelectedDate, "yyyymmdd")) && Value(Text(TransactionDate, "yyyymmdd")) <= Value(Text(RequestDates_1.SelectedDate, "yyyymmdd")) && If(_managerView, Approver = _selectedApprover.UserPrincipalName, Requester = _myProfile.UserPrincipalName)) /* <-- Please type _selectedApprover rather than _myProfile */
),
SequenceNo, Descending
)

Note: Please use the _selectedApprover to compare with the Approver column.

Please take a try with above solution, check if the issue is solved.

 

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

Re: How to sort "All" request type

Hi @v-xida-msft 
It works but only for pending and All I cant filter the Approved,Declined and Processed how to do it?

Big_S
Level: Powered On

Re: How to sort "All" request type

@v-xida-msft 
How to Sort the Approved, Declined and Processed?

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 (6,381)