Problem creating slice from referenced tables

[Wiktor_Jurek]

Hi,
I have three tables, and I’m trying to create a view “My Jobs”

• Jobs
• Employees
• Tasks

I have an issue creating a slice that only shows jobs that an employee is a part of.

A job should only be shown in the view/slice if:
In the related tasks for that job, if there is ANY task that the employee logged in is assigned to, show the job.

The ‘pseudocode’ version of this would look like

For each job:
    For each related task in the job:
        If task.person.email == useremail ():
              show job
        else:
              don't show job

I’ve been trying to knock up an appsheet formula to satisfy this In the slicer, but I’m getting myself tangled up in a mess of IN() and LOOKUP() statements. Help would be much appreciated.

[WillowMobileSys · Accepted solution]

If I understand correctly, Jobs have Tasks and Tasks are assigned to Employees. In the My Jobs slice you want to see only those Jobs where Task are assigned to the Logged In user.

There is also these assumptions:

1. The Jobs table has a Job ID column as the row key
2. Tasks have a reference to the Job they belong to - column named Job
3. Tasks have a reference to the assigned Employee - column named Employee

Basically, you want to check if the [Job ID] belongs to the Jobs list from the Task table where the Employee is the logged in user given by the USEREMAIL() function.

So…your Slice against the Jobs table would have criteria similar to:

IN([Job ID], SELECT(Tasks[Job], [Employee].[Email] = USEREMAIL()))

That’s it! I don;t know your experience level so ask if you have questions

help.appsheet.com

IN()

Is item in list?

help.appsheet.com

USEREMAIL()

User's email address

[Wiktor_Jurek]

WillowMobileSystems:

Basically, you want to check if the [Job ID] belongs to the Jobs list from the Task table where the Employee is the logged in user given by the USEREMAIL() function.

Absolute lifesaver. Thank you so much. The formula is very similar to what I had initially, but I just didn’t fully understand how the connection was made between tasks, jobs and people.

Many thanks!