Filter: joins with tables

Hi, I am looking for joining multiple tables in filter expressions. My specific use-case is like this:

Projects consists of tasks, tasks need specific skill. On the other hand employees provide multiple specific skills. So, in the application I want to assign employees to tasks and like to filter the employee table for employees providing the needed skill.

Tables:

  • Employees
  • Tasks
  • Skills
  • EmployeeSkills (defining, which employee has which skills, references to rows in employees and skills tables)
  • TasksSkills (defining, which tasks need which skills, references to rows in tasks and skills table)
  • ProjectsTasks (contains list of tasks per project and which employee will do the task)

In the ProjectsTasks, I have a Project column, a Task column and a Employee column. I want the employee column to be a Filter / Dropdown and it should only show employees with the needed skills. So I would need to do something like

=FILTER(Employees, "ProjectsTasks[Task]=TasksSkills[Task] AND TasksSkills[Skill]=EmployeesSkills[Skill] AND EmployeesSkills[Employee]=Employees[Name]")

but this is not working. Anyone can help with the Filter expression?

Also, is there already an exhausting documentation available?

I don’t think you can switch tables inside a filter. The formula you have starts out checking the employees table, then is comparing column values to columns in other tables. I don’t think that’s possible.

Instead, try filtering your employee skills table by a specific skill, and have it return all the employees that match that skill.

If you want the skill to be able to change, then first create a data cell, make it a variable, and source it to the skills table. Then reference that data cell within your new filter of the employee skills table.

1 Like

Thanks Andrew! I think I cannot achieve what I was looking for at the moment. With your help I can now see which different skills are needed, though I cannot show employees with needed skills. If someone is interested, this is what I am using for now:

=FILTER(TasksSkills,"TasksSkills[Task]=[Task]")

which returns all skills needed for a specific task.

I guess “joins” over multiple tables are not yet supported, maybe they will in the future? We are still at the beginning of this neat tool :wink:

1 Like

Is the Filter evaluating to a something or returning an error?

HI @anon7373198 welcome to the forum! I was able to get something like this to work with the following setup. My employees table looks like so:

join_2020-06-29_13-43-54

The EmployeeSkill column filter is =Filter(A_EmployeeSkills,"A_EmployeeSkills[Employee]=ThisRow()") and the Skills column filter is =Filter(A_Skills,"A_Skills IN [EmployeeSkills][Skill]").

I modeled skills table like so:

join_2020-06-29_13-46-30

I modeled tasks like so where TaskSkills column filter is =Filter(A_TaskSkills,"A_TaskSkills[Task]=ThisRow()") and Skill column filter is =Filter(A_Skills,"A_Skills IN [TaskSkills][Skill]")

join_2020-06-29_13-47-54

I modeled EmployeeSkills like so, where the first column is a rowlink to my A_Employees table and second column is a rowlink to my A_Skills table:

join_2020-06-29_13-51-55

Similarly, I modeled TaskSkills like so, where the first column is a rowlink to my A_Tasks table and second column is a rowlink to my A_Skills table:

join_2020-06-29_13-53-47

With this setup, I was able to create the ProjectTasks table like so, where Task column is a rowlink to the Tasks table, the Employee able to perform the Task column filter is =Filter(A_Employee,"[Task][Skill] IN A_Employee[Skills][Skill]"), and the Employee column is set as a picklist on a filter whose source is =[Employee able to perform the Task].

Let us know if this works for you!

2 Likes

Hey @RajeshGoli,

Thank you so much for your detailed answer! I learned a lot from it; especially from the filters like =Filter(Employees,"[Task][Skills] IN Employees[Skills][Skill]"). I wasn’t aware of the IN operator.

I also like the “trick” with a filter column as in the A_Tasks table and the TaskSkills and Skill columns to get around not being able to join multiple tables.

It is working fine! In the ProjectTasks table I get a list of employees having all skills demanded by a specific task. Awesome!

The next thing I am looking for is job titles in project phases: project phases needs several different “job titles” to be involved (think of a sales representative and a solution architect that is needed for a phase “consulting with customer”). So, if I am adding a phase to a project, I need to assign employees holding one of the job titles - and not all, like with the skills.

So instead of an AND operation on all task skills I am looking for a way to express an OR operation. Like, I am looking for employees having either this or that job title (defined by the phase).

Is there a way to soften an expression like =Filter(Employees,"[Task][JobTitles] IN Employees[JobTitles][Name]") into something like “only one or more of the items in the left list needs to exist in the list on the right side”? I think, I am looking for a way to check for if the intersection of two lists is not empty.

It’s fun to see how far you can get with this service :slight_smile:

Hi @anon7373198 I think you can express something like that. The way I imagine I'd do that would be to have a filter column in the project phases table that contained all the job titles that can work on it. It could look something like:

Project Phase Job Titles
Sales =Filter(Job_Titles, "Job_Titles[Title] = % OR Job_Titles[Title] = %", "CXO", "Solutions Sales")
Deployment =Filter(Job_Titles, "Job_Titles[Title] = % OR Job_Titles[Title] = %", "Tech Sales", "Product")
Support =Filter(Job_Titles, "Job_Titles[Title] = % OR Job_Titles[Title] = %", "Tech Support", "Product")

I'd also put Job Title column in the Employees table, make it a picklist on the Job Titles table and assign everyone their titles. Then, assuming there is a Project Phase column in the Projects table, and assuming it is formatted as a rowlink to Project Phases table I defined above, I would create a column Employees for Phase in the Projects. This column can then find all the employees who can help with a filter like =Filter(Employees, "Employees[Job Title] IN [Project Phase][Job Titles]"). I would then build a picklist on the table like we did in the example above.

Does this solve your use case?