Filtering a gallery using two tables that contain Lookup columns

[Anonymous]

I am building a task tracking app that requires a gallery to be filtered using two tablets - some of the colums are Lookup.  Just can't seem to get my head around how to structure the filter and reference the lookup columns from multiple tables.

First I will go over the Sharepoint lists and their columns.

Table 1 (Task Tracking)

Date:  Date and time

Employee:  Person or Group (Office 365)

Task:  Lookup (This references the Name from the Tasks table)

Table 2 (Tasks)

ID: (Automatically generated)

Task:  Single line of text

Depot: Choice (multiple select)

Role: Choice (multiple select)

What is input to tables

The Task Tracking list contains a record of the date and a person did a task.  Each record only contains a single task.

The Task list contains specific tasks and then what Depot(s) and Role(s) they are applicable to.  i.e. one task might be applicable to multiple depots or multiple roles (hence the fields being multi select)

What I am trying to achieve

The dropdowns above are linked to the following:

Employee:  Office365Users.SearchUser({searchTerm: ComplianceEmployeeBox.SearchText})

Role:  Choices(Task.Role)

Depot:  Choices(Task.Depot)

Users will select themselves from the Employee search (Office 365), select the Role and Depot that is applicable.

Below is a Gallery that I would like to show every task that the employee has done from the Task Tracking list where the task also has the selected Role and Depot from the Task list as attributes.

For example:

If the Task Tracking list had the following:

and the Task list had the following:

and the user named "Jim" selected himself and then "Brisbane" for Depot and "Worker" for role it would display the following record:

This would be shown because it's his record, and car driving as a task is applicable to Brisbane and Worker.  It wouldn't show is "Hammering" task because it isn't applicable to the Brisbane Depot.

I hope this explains it clearly and I would greatly appreciate any help.

Cheers,

Joel

[yashag2255]

Hey @Anonymous 

Can you try to use the expression as:

Filter('Task Tracking', Employee.DisplayName = ComplianceEmployeeBox.Selected.DisplayName && Dropdown1.Selected in LookUp(Tasks,ID = Task.Id).Role && Dropdown2.Selected in LookUp(Tasks,ID = Task.Id).Depot)

Here, ComplianceEmployeeBox is for selecting the employee, Dropdown1 for selecting the Role and Dropdown2 for selecting the Depot.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

Hi @yashag2255 ,

Thanks for trying to assist.

Unfortunately even if I just use the "Role" to filter for simplicity to start with I still get errors.

Filter('Task Tracking', Employee.DisplayName = ComplianceEmployeeBox.Selected.DisplayName && RoleSelectionCompliance.Selected in LookUp(Tasks,ID = Task.Id).Role)

The above gets two errors in the Lookup:

• "Invalid argument type" when hovering over "Tasks"
• "Invalid use of "." when you hover over the full stop before "Role"

I thought maybe it was because a lookup needs to return a column so I changed it to:

Filter('Task Tracking', Employee.DisplayName = ComplianceEmployeeBox.Selected.DisplayName && RoleSelectionCompliance.Selected in LookUp(Tasks,ID = Task.Id, ID).Role)

To have the ID as the return, but I still get the same errors.

Any further suggestions?

[yashag2255]

Hey @Anonymous 

Can you please confirm if Tasks is the correct name of second SP list and that is added as a valid datasource?

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

Hi @yashag2255 ,

Can definitely confirm that the second SP list is called "Tasks" and that the datasource has been refreshed.