Answered

Multiple Search Criteria for Gallery

(0) Share

Report

Posted on by Phineas

4,714 Super User 2025 Season 1

You may have to pack a lunch for this one -🤣

I have a Gallery that is displaying data that individuals up and down the organizational chart will be viewing.

There is also a Combo Box and RadioControl to facilitate search/filter of the gallery contents.

The data displayed in the Gallery is contained in 'RequestTrackerList' (RequestTrackerListCollection).

Access to the data is based upon the employee's job title, employee name and employee supervisor.

Gallery -

/*Search Box Filter for positions of Driver, Teacher, Maintenance, Security*/
If(UserJobTitleLbl.Text = "Driver" || UserJobTitleLbl.Text = "Teacher" || UserJobTitleLbl.Text = "Maintenance" || UserJobTitleLbl.Text = "Security",
Switch(FilterByCategoryRadioCntrl.Selected.Value,
"Status",
SortByColumns(Filter(RequestTrackerListCollection,Status.Value =

CategorySearchComboBox.Selected.Value),"MeetingDate",SortOrder.Descending),

"Created By",
SortByColumns(Filter(RequestTrackerListCollection,'Created By'.DisplayName =
CategorySearchComboBox.Selected.Value),"MeetingDate",SortOrder.Descending),

"Meeting Subject",
SortByColumns(Filter('Competitor External Meeting Request Tracker','Meeting Subject' =
CategorySearchComboBox.Selected.Value),"MeetingDate",SortOrder.Descending),

/*Default Filter If Search Combo Box IsBlank, for Driver, Teacher, Maintenance, Security*/
If(IsBlank(CategorySearchComboBox.Selected.Value) && UserJobTitleLbl.Text = "Driver" || UserJobTitleLbl.Text = "Teacher" || UserJobTitleLbl.Text = "Maintenance" || UserJobTitleLbl.Text = "Security",
SortByColumns(Filter(RequestTrackerListCollection,
Status.Value = "In Draft" ||
Status.Value = "Pending Approval by Teacher" ||
Status.Value = "Pending Security Approval" ||
Status.Value = "Request for More Information" ||
Status.Value = "Minutes Under Teacher Review" ||
Status.Value = "Minutes Under Security Review" ||
Status.Value = "Meeting Completed-Upload Minutes"),"MeetingDate",SortOrder.Descending),

/*Default Filter for Employee*/
If(IsBlank(CategorySearchComboBox.Selected.Value) && UserJobTitleLbl.Text = "Employee",
SortByColumns(Filter(RequestTrackerListCollection,'Created By'.DisplayName =
User().FullName),"MeetingDate",SortOrder.Descending),

/*Default Filter for Manager and Supervisor; The filter should show all items created by the Manger/Supervisor and ONLY the subordinate employees of the Supervisor/Manager viewing the data*/
If(IsBlank(CategorySearchComboBox.Selected.Value) && "Supervisor" || "Manager" in UserJobTitleLbl.Text && UserFullName.Text <> "Joe Schmoe",/*Joe Schmoe has 'Manager' in his Job Title but I don't want him impacted by this filter)*/
SortByColumns(Filter('Competitor External Meeting Request Tracker',JobTitle6EmailAddress = User().Email && 'Created
By'.DisplayName = User().FullName),"MeetingDate",SortOrder.Descending))))))

The last requirement is there are many other 'job titles' that may view the data.

Each of them should have the same viewing limitations as imposed by the 'Employee' IsBlank filter.

There are far too many other jobs to list, so how would I account for this issue? These others should be
allowed to see only their data (these individuals and the 'employee' do not have access to the search
Combo Box, so they only need a default filter for Combo Box 'IsBlank' condition.

Categories:

General Questions

All responses (5)

Answers (1)

Verified answer

narayan225 2,449 Super User 2025 Season 1 on at

Like (0)

Report

Re: Multiple Search Criteria for Gallery

@Phineas

If you have dozens of roles, to do the filtering efficiently, you might need to introduce a separate table that uses permission level in it. You can then associate the records to users in such a way that the records can be filtered on the basis of the permission table.

The rest of the filters based on combo box and sorting can then be applied to the filtered records.

Hope this helps.

Cheers!
Phineas 4,714 Super User 2025 Season 1 on at

Like (0)

Report

Re: Multiple Search Criteria for Gallery

The gallery content, by default, should be filtered as follows -

Manager/Supervisor - Should see their own records or those of their subordinates, in order of creation at Start.

Same applies if they engage the Combo Box. The gallery should only show their records or those of their subordinates, based on the choice made in the Radio control (Subject, Created by, Status).

Admin should see all records at default, and all records based on Radio control choice.

'Associate' should see only their records. The Combo Box is hidden from Associates.

All Others - it's a very large company. I do not know all the various job titles nor how many, but if the title is not one of the titles mentioned above, these individuals should see only their records, unless their emai addressl appears in the 'supervisoremail' column of the SharePoint list (collection). If it does as they should see the data for those employees, too.
narayan225 2,449 Super User 2025 Season 1 on at

Like (0)

Report
Re: Multiple Search Criteria for Gallery

@Phineas
The recommendation was based on the information that you provided.
Couple of questions:
How many user roles do you have? Also, the permissions to be applied based on each role. For example, teacher, staff and it could have permission to view certain records and admin might have permission to view all records.
Also, after the data has been filtered, what sorting or further filter do you wish to implement?
Because you said that there could be dozens of roles, what permission levels would you define for each of the role?

Cheers!
Phineas 4,714 Super User 2025 Season 1 on at

Like (0)

Report

Re: Multiple Search Criteria for Gallery

Thank you for the detailed reply.

I can be a little slow on understanding.

If I may...

I've duplicated your formula, just to further familiarize myself with it.

I'm not understanding how the Combo Box fits in, and how the filter by date ascending is considered.
Can you clarify?

Also, your formula considers 'identified' positions (Roles), but what of the potentially dozens of positions (Roles) not written into the formula?

Does your formula account for the dozens of other positions (Roles) not identified in your formula?

Set(varRole, LookUp(employeeTable,User().Email = emailColumn).Role)
Collect(appData,Filter(tableName,If(varRole = "Role1" || varRole = "Role2" || varRole = "Role3" || varRole = "Role4",
columnName = "Role1" || columnName = "Role2" || columnName = "Role3" || columnName = "Role4, tableName)))

Fitler(appData,CategorySeachComboBox in ColumnName)
narayan225 2,449 Super User 2025 Season 1 on at

Like (0)

Report
Re: Multiple Search Criteria for Gallery

@Phineas

You can create a collection on start of the application that initialises the list to be displayed in the app.
Something like this.
Set(varRole,LookUp(employeeTable,User().Email = emailColumn).Role) Collect(appData,Filter(tableName,If(varRole = "Role1" || varRole = "Role2" || varRole = "Role3" || varRole = "Role4",columnName = "Role1" || columnName = "Role2" || columnName = "Role3"|| columnName = "Role4",tableName))) //We check if one of the roles is met then we filter data with all the other necessary roles //Replace role1 variable with your roles. //If the user has none of the roles, we load all the rows from the table
After that, you can apply the rest of your filter to the loaded collection called appData.
Filter(appData,CategorySeachComboBox in ColumnName) //this will fitler appData based on the comobox value for a particular column. //Don't need to worry about the field being empty with this one.
Hope this helps.

Cheers!