cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMeena_9
Resolver I
Resolver I

How to Filter the gallery with multiple columns

Hello Guys,

 

 

I need some help with Items in the gallery to be displayed just the way I filter the Items inside it. I tried to use two column names in my data source excel but it is not working. 

set(varMailid, User().Email)

Filter(datasource_name, Task_Status = Done) && Filter(datasource_name, mail_id = varMailid)

 

I want to filter first the items of user specific tasks and then display completed tasks only. Is there anyway this can workout ?? please suggest me how to work with Nested filters like this?

 

Any help would be appreciated

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @RMeena_9 ,

 

Please try below formula:

With(
     {UserTasks: 
            Filter(
                  TaskTable ,mail_id = varMailId
            )
     },
     Filter(
            UserTasks, Task_Status = "Done"
     )
)

 

This formula means use the email filtered result as the source of second Filter.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

9 REPLIES 9
MichelK
Resolver II
Resolver II

Hello @RMeena_9 ,

 

Actually you need to combine all your filtering conditions in the same Filter expression:

Filter(datasource_name, Task_Status = Done && mail_id = varMailid);

 This should solve your problem.

 

Let me know if this helps!

Hi @MichelK,

 

It is working with this filter but it showing delegation warning ?? How to remove the warning

MichelK
Resolver II
Resolver II

@RMeena_9  Could you show me a screenshot of your expression so that I can see?

What type of datasource are you using?

@MichelK,

 

Filter(TaskTable ,mail_id = varMailId && Task_Status = "Done")

I am using excel as data source which as around 75 rows with 10 different columns

Hi @RMeena_9 ,

 

Please try below formula:

With(
     {UserTasks: 
            Filter(
                  TaskTable ,mail_id = varMailId
            )
     },
     Filter(
            UserTasks, Task_Status = "Done"
     )
)

 

This formula means use the email filtered result as the source of second Filter.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Hello @v-jefferni ,

 

It works like charm, thank you so much but whether the same code will work for search filter also ?? I trying to filter a gallery if user search any items from the list, the below code is working but I am getting delegation issue.

Filter(TaskTable,StartsWith(Name,SearchBar.Text) || StartsWith(Project,SearchBar.Text)|| StartsWith(Task_Status,SearchBar.Text) || StartsWith(mail_Id,SearchBar.Text) ..........)

 

I have few other columns also added to same filter - SearchBar is my search text box

 

Please help me with this issue.

Hi @RMeena_9 ,

 

You will need to find out which criteria is non-delegable. Cut them all from the original filter, try and paste each one in the filter function to check the result. Use the non-delegable criteria(s) for the second parameter of the With function.

 

For example:

With(
     {Filter1:
             Filter(
                    TaskTable,
                    StartsWith(Name,SearchBar.Text) || StartsWith(Project,SearchBar.Text)
             )
     },
     Filter(
            Filter1, 
            StartsWith(Task_Status,SearchBar.Text) || StartsWith(mail_Id,SearchBar.Text)...)
     )
)

 

You can also use Search function to improve the performance if all those a static column names from the data source:

Search(TaskTable,SearchBar.Text,"Name","Project","Task_Status","mail_Id",...)

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni,

 

I have tried this but when I search any item listed in one of the column it is not displaying the content in the gallery

Search(TaskTable,SearchBar.Text,"Name","Project","Task_Status","mail_Id",...)

also showing error as

The requested operation is invalid. Server Response: TaskTable failed: Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported. clientRequestId: ................... 

Hi @RMeena_9 ,

 

I just noticed it's an Excel table, and that's why Search function is not supported. Please use Filter function.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (1,790)