Filter Gallery Formula Help

(0) Share

Report

Posted on by NP-18101421-0

237

Hi,

I have a gallery that has to be filtered by the user that is logged in and if they use one of the filters or search box. Up until now my formula only had to filter using one drop down or the search box, but I have to add in an additional drop down to filter. The user should be able to see their own requests and be able to filter by status, assignee or by searching by title.

Current Formula

If(
 IsBlank(cbRequestStatus.Selected.Value),
 Filter(
 Sort(
 'Vendor Setup',
 ID,
 SortOrder.Descending
 ),
 'Created By'.DisplayName = Office365Users.MyProfile().DisplayName && (inputSearch.Text in Title)
 ),
 Filter(
 Sort(
 'Vendor Setup',
 ID,
 SortOrder.Descending
 ),
 'Created By'.DisplayName = Office365Users.MyProfile().DisplayName && (inputSearch.Text in Title) && 'Request Status'.Value = cbRequestStatus.Selected.Value))

How can I add in the logic to allow the user to filter by a 2nd drop down as well? They should be able to select multiple drop downs and use the search box in parallel.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

LaurensM 12,516 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @NPatel12498,

I have made some adjustments to the code and added inline comments to further explain the functionality.

With( { //Prefilter records (delegable query) - should return less records than your Data Row Limit wPrefilter: Filter( 'Vendor Setup', //Filter based on email via the native User() function 'Created By'.Email = User().Email, //Filter via Request Status if cbRequestStatus has a selection IsBlank(cbRequestStatus.Selected.Value) || 'Request Status'.Value = cbRequestStatus.Selected.Value, //Example for Assignee (we expect a Person column), change combobox name to the correct name //Use the SearchUsersV2 function as the items property of your Assignee cmb IsBlank(cbAssignee.Selected.Mail) || AssigneeColumn.Email = cbAssignee.Selected.Mail ) }, Sort( //Apply non-delegable condition Filter( wPrefilter, inputSearch.Text in Title ), ID, SortOrder.Descending ) )

The Assignee Items property could look as follows:

Office365Users.SearchUserV2( { isSearchTermRequired: false, searchTerm: Self.SearchText, top: 10 } ).value

Please note that the new Filter is only partly delegable. The Prefilter section should return less records than your Data Row Limit (see app settings, Default 500 - Max 2000).

In case the Created By filter is used to hide sensitive data - I would recommend using Dataverse Security Roles instead to limit the view permissions on that table.

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

Thanks!

Was this reply helpful? Yes No
NP-18101421-0 237 on at

Like (1)

Report
Copy link

Link copied!

Hi,

I tried this and my gallery shows up with no items. Additionally, my Assignee drop down is a choice column. The items have names of 5 people there that the user would select from. When I change the drop down Assignee items to the formula you have above, the 5 names that should be there are no longer there,

Was this reply helpful? Yes No
Verified answer

LaurensM 12,516 Moderator on at

Like (0)

Report
Copy link

Link copied!
@NPatel12498,

Thank you for the additional information! 😊

My apologies I though the Assignee column was of type person. You can change your dropdown items property back to its original code.

I have made some adjustments to the code (with Assignee being a single-select choice column):

With( { //Prefilter records (delegable query) - should return less records than your Data Row Limit wPrefilter: Filter( 'Vendor Setup', //Filter based on email via the native User() function 'Created By'.Email = User().Email, //Filter via Request Status if cbRequestStatus has a selection IsBlank(cbRequestStatus.Selected.Value) || 'Request Status'.Value = cbRequestStatus.Selected.Value, //Code below expects a Assignee to be a single-select choice column IsBlank(cbAssignee.Selected.Value) || AssigneeColumn.Value = cbAssignee.Selected.Value ) }, Sort( //Apply non-delegable condition Filter( wPrefilter, inputSearch.Text in Title ), ID, SortOrder.Descending ) )

I hope this helps!

Was this reply helpful? Yes No
NP-18101421-0 237 on at

Like (0)

Report
Copy link

Link copied!

Yes this worked perfectly! Thank you so much for the help 🙂

Was this reply helpful? Yes No