Hello everyone,
I need to display list of resources available for specific tasks. The list needs to display all employees and should satisfy the following;
- All employee should be displayed
- Employee available for task should be selectable
- Employee unavailable for task should also be shown but not selectable or not be able to assign a task
- List of employees should be distinct
- Available or Unavailable should be added to the employee in the form of Employee - Available or Employee - Unavailable.
The user will click the task from a given list, and upon clicking will be taken to a form or detail view where a dropdown list will be given of the employee. They will then select the employee from the given list.
I need help with 3 and 5. I am stuck as to what’s the best way to meet 2 & 3 based on my table structure. I can show the employee that is available for a task, but how do I show an employee record that isn’t available for the selected task? In SQL, I would have done an outer join, but unsure how to do it in App Sheet.
For point 5, I am unable to do a concatenation of two fields inside a select statement, perhaps because of inexperience with the platform.
I have got the following tables, Employee_Task_Availability table below contains employee available for a task.
Employee table (master)
| Emploeyee_ID (Unique) | Employee_Name |
|---|---|
| 1 | Fred Smith |
| 2 | Scott Neil |
Task table (master)
| Task_ID | Task_Name |
|---|---|
| 1 | Deliver Hardware |
| 2 | Configure Hardware |
Employee_Task_Availabilty
| Task_ID | Task_Name | Employee_ID | |
|---|---|---|---|
| 1 | Deliver Hardware | 1 | Fred Smith |
| 2 | Configure Hardware | 2 | Scott Neil |
| 1 | Deliver Hardware | 2 | Scott Neil |
Thanks
Kaz