How to Filter the gallery with multiple columns

[RMeena_9]

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

[MichelK]

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!

[RMeena_9]

Hi @MichelK,

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

[MichelK]

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

What type of datasource are you using?

[RMeena_9]

@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

[v-jefferni · Accepted solution]

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.

[RMeena_9]

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.

[v-jefferni]

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.

[RMeena_9]

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: ................... 

[v-jefferni]

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.