cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jkperth
Frequent Visitor

Filtering a gallery using two tables that contain Lookup columns

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

 

Filter.jpg

 

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:

 

Task Tracking.jpg

 

and the Task list had the following:

 

Task.jpg

 

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

 

Result.jpg

 

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

 

 

 

 

4 REPLIES 4
yashag2255
Dual Super User II
Dual Super User II

Hey @jkperth 

 

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!

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
Dual Super User II
Dual Super User II

Hey @jkperth 

 

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!

Hi @yashag2255 ,

 

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (29,783)