Gallery Filtering

[Jayant_2203]

Hi All,

Background:  I am currently building a project Management Tool and i am currently stuck at a use case for gallery filtering. 

Requirement: I have a gallery that shows task for individual against projects, I want to filter by these below conditions:

1. The task should be shown to People by whom it has been created( For Example: If a Task A has been created by User B, he should only see that task)
2. Once a task is created, the manager field is automatically populated with their manager name. The task should be shown to a Preset Manager Person( For Example: If a task A has been created by User A and If a Task B has been created by User B, both these task should be shown to the person who is in the manager field but User A should see Task A and User B should see Task B) 
3. I have to and from Date and once the user selects both the dates the gallery should filter the records based on those dates comparing the created by record.
4. I have a dropdown which will showcase list of Users, The gallery should filter the records based upon user selection also.
5. I have a dropdown of Projects, the gallery should filter records based upon project selection

Now i want these filter to work simultaneously. Please Help me out!

Any help would be appreciated.

[zmansuri]

1.

App.OnStart:

Set(varUser,User())

In the gallery it will be like this:

Filter(SpLIst,'Created by'.Email = varUser.Email)

2. You'll have to run Power Automate flow or use OnSuccess of your form to call the Office365User Connector to patch manager field of the user who created the task. You can pass 'Created by'.Email as parameter in manager function to fetch the manager name. and storte it.

In the gallery it will be like this:

Filter(SpList,Manager.Email=varUser.Email)

3. Filter(SpList,Created>=DatePickerFrom.SelectedDate && Created<=DateAdd(DatePickerTo.SelectedDate,1,Days))

4.Add a dropdown. In it's item property write this:

Distinct(SpList,'Created by'.DisplayName).Result

This will populate your dropdown with all the users whi have created atleast one task

Gallery:

Filter(SpList,'Created by'.DisplayName=Dropdown.Selected.Result)

you can add all the above condition in one filter block:

Filter(SpList,'Created by'.Email = varUser.Email,,Manager.Email=varUser.Email,Created>=DatePickerFrom.SelectedDate && Created<=DateAdd(DatePickerTo.SelectedDate,1,Days),'Created by'.DisplayName=Dropdown.Selected.Result || Dropdown.Selected.Result=Blank(),ProjectColumn=DropDown.Selected.Value||DropDown.Selected.Value=Blank())

[Abhilash_Swain]

Hi @Jayant_2203 

Tried to give you some sample codes. For simplification, I am sharing it separately. You can combine them as per your requirement.

1> Go to Gallery > Items and Filter based on current user email ID. ( Employee_Office is a SP site for example)

Filter(Employee_Office,'Created By'.Email =User().Email)

2> For manager field 
If it is a text field:-  use Office365Users.Manager(User().Email).DisplayName [to use this add Office365users as a data)

3> For Date Filter  Items = Filter( 'Employee_Office', 'Created' >= DateAdd( Today(), -30, Days ) && 'Created' <= Today( )) - This will filter the source to show you items created between last 30 days.

4> to display a group of members you can use Items= ClearCollect(CollectionDD1,Office365Groups.ListGroupMembers("bb5a6396-4146-492a-bae9-de1f5e5f4cc1").value) . [Office365Groups -> connector and you can get the group ID from MS teams)

5>Filter(Employee_Office,'Dept' =dropdowd.selected.value).

You can combine the filters using And/Or operators . Or Filter(Filter(Filter(Source, Created By.email = User().email), Created <= DatePicker1.value),ProjectID = Dropdown.Selected.value)

Please give a thumbs-up or accept the solution if the above explanation clears your doubts.

Thanks

Abhi

[Jayant_2203]

@zmansuri this doesn't seem to work. See the below screenshot.

[zmansuri]

'Created by'.Email = varUser.Email,Manager.Email=varUser.Email

This means user have to be in both field. Try this:

'Created by'.Email = varUser.Email || Manager.Email=varUser.Email

[Jayant_2203]

@zmansuri 

still not working, it is not fetching the record.

all other filters are working but not the date one. Any fix for that?