Filtering Gallery

[Anonymous]

I have built a Time Off Request app for all our employees. This app has a Manager Screen so the manager of each department can approve or deny time off requests from employees within their department. I am using a Filter formula to filter the Gallery by the users email to signify which department requests they should be able to view. This list was created with a collection. The filter is currently working as intended, however, I need a few managers to be able to see all departments. So I added them to the collection with all departments but the gallery still only shows the first department listed for that user. Any ideas how to do this? Thank you.

Here is my current formula on the Items property of the gallery: 

SortByColumns(Filter('[dbo].[wynne_TimeOff_PendingRequests]', Dept=MgrDeptText.Text&&Status="Approved"), "DateSubmitted", Descending)

[newk1991]

SortByColumns(Filter('[dbo].[wynne_TimeOff_PendingRequests]', Dept=MgrDeptText.Text,Status="Approved"), "DateSubmitted", Descending)

[Anonymous]

I tried changing the formula to your suggestion, however, it still only shows the first department listed for that user.

The screenshot shows the collection I am using. The ones in orange are all the same email address with all our departments.

[newk1991]

Please explain "but the gallery still only shows the first department listed for that user" 

What is the department data type?

[Anonymous]

So basically, when an employee fills out a time off request, it uses their department to send an email notification to their manager. On the Manager screen, the managers can only see requests for their department. So for a couple managers they need to see all the departments. So I filter that by the MgrDept column in collection called ManagerEmail. So for example, if the manager's department is Operations then they only see requests from Operations dept. However, for a couple execs and the HR manager, they need to see all departments. Does that clear it up at all? Right now, even though the HR manager is listed with all departments in the collection, they only can see one department.

[RandyHayes]

@Anonymous 

So my first question to you would be...don't you already have the department set up in the user profile?

If you do, then you can reduce the need for keeping a sperate list of emails and departments and you could use that to do all your filtering.  Like this:

With({profileDept:Office365User.MyProfile().department},

    SortByColumns(
        Filter('[dbo].[wynne_TimeOff_PendingRequests]', 
            (
                Dept =  profileDept ||
                profileDept = "HR" || profileDept = "Excutive"
            ) &&
            Status="Approved"
        ), 
        "DateSubmitted", Descending
    )
)

If you use what you have now instead of the User Profile, then you need to alter your formula to this:

With({departments: Filter(yourList, EmailAddress=User().Email)},

    SortByColumns(
        Filter('[dbo].[wynne_TimeOff_PendingRequests]', 
            Dept in departments.MgrDept &&
            Status="Approved"
        ), 
        "DateSubmitted", Descending
    )
)

Personally I like doing this off of the profile instead of a list.  If a new person joins the company, they will have an account set up in 365, usually they will have their department set as well.  That is all that is needed...your app does not need to be updated, nor do you need to add that user then to a list.  It will just work.

[Anonymous]

@RandyHayes  @newk1991 

Hi Randy! So originally I had not been using the Department field on my Office 365 users. I have now updated them to the proper Department in Office 365. It seems like a good practice to use that. So my four departments are: Office, Operations, Maintenance, and Management. I have 3 users that I need to be able to see all four departments. The other managers only need to see their specific department. So for example, our Operations Manager who is in the department Management, needs to see all the Operations requests only. Our HR Manager who is in the department Management, needs to see all four departments. Make sense? Thanks!

[RandyHayes]

@Anonymous 

Yes, very good!  Did you try the formula I provided then?  It should do that for you.

[Anonymous]

@RandyHayes 

So for my specific app the formula should look like this:

With({profileDept:Office365User.MyProfile().department},

    SortByColumns(
        Filter('[dbo].[wynne_TimeOff_PendingRequests]', 
            (
                Dept =  profileDept ||
                profileDept = "Operations" || profileDept = "Office" || profileDept = "Maintenance" || profileDept = "Management"
            ) &&
            Status="Approved"
        ), 
        "DateSubmitted", Descending
    )
)

[RandyHayes]

@Anonymous 

Actually I jumped before reading completely...your requirements have changed slightly.  Before it was more department based (the people that needed to see "ALL"), now it seems more user based (although re-reading back a way, it seems like that point is now more the case).

So, the formula change, if you want specific users Only to see all, would be:

With({profileDept:Office365User.MyProfile().department,
      isSeeAll: User().Email in "user1@domain.com|user2@domain.com|user3@domain.com"},

    SortByColumns(
        Filter('[dbo].[wynne_TimeOff_PendingRequests]', 
            (
                Dept =  profileDept ||
                isSeeAll
            ) &&
            Status="Approved"
        ), 
        "DateSubmitted", Descending
    )
)