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.
- 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.
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:
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
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:
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:
I modeled tasks like so where
TaskSkills column filter is
Skill column filter is
=Filter(A_Skills,"A_Skills IN [TaskSkills][Skill]")
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
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
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!
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
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:
||=Filter(Job_Titles, "Job_Titles[Title] = % OR Job_Titles[Title] = %", "CXO", "Solutions Sales")
||=Filter(Job_Titles, "Job_Titles[Title] = % OR Job_Titles[Title] = %", "Tech Sales", "Product")
||=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
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?