Interesting scripting problem?
In a table of employees, I have a dynamic, multiple choice field where managers can select from a list of tasks that the employee is trained to complete. (Employee Table)
In another table there is one record for each assignable task. (Task Table)
For example, in the Employee Table
Employee Amy is assigned tasks A-1 and B-4 in the employee table.
Employee Bill is assigned to tasks A-1, C-5, C-6 and D-7.
I need to generate a fx field in the Task Table which shows which employees are assigned to each task.
For example, task A-1 would list Amy and Bill
Is there a genius out there with a simple solution?
3 replies
-
Here is a simple solution:
let t := this; let x := (select Employee where contains(numbers(Tasks), number(t))); concat(x.Name)Since you use a dynamic multichoice (dMC) field you have to use a select statement to find the records that are linked.
Line 2, the Tasks field is my guess at the name of the dMC field in Employees, Please change it to reflect what is in your DB.
Something to think about, you may want to create a N:N table between Employee and Tasks, called Assigned Tasks. This way you can track the tasks assigned to employees and also keep track of data that is specific to the task, i.e. due date.
Another advantage of the N:N table is that now tables are linked so you can use relationship to get data from another table without doing any select statements. You can now also create different views for different people. Managers see one view with the dMC element and Workers see another, a table with just their assigned tasks.
You can still use a dMC field as an UI element to create the records in the Assigned Tasks table. You can check out this post.
-
Here is a mock up of the N:N table idea.
-
Yes, that worked perfectly. Used both solutions.
Content aside
- Status Answered
- 9 mths agoLast active
- 3Replies
- 88Views
-
2
Following