How to sort "All" request type

[Big_S]

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

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!

[lucia]

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]

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))

[v-xida-msft]

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,

[Big_S]

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.

[v-xida-msft]

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,

[Big_S]

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))

[v-xida-msft]

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,

[Big_S]

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]

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