Ok, so I am trying to create this Audit app where I have Internal Auditors, who as company employees, belong to a department.
We don´t want internal auditors auditing their own departments.
So in the audit department, when assigning internal auditors to each department, I want the reference column for auditors to show ONLY auditors that don´t belong to that department.
In theory that should be easy right? Reference Dropdown pointing to Auditor table with rule =choice.department != $Department
(that is, shows only auditors whose department is different than current department. Ps: it doesn´t work with == to show only auditors who belong to that department)
The problem however, is that the Auditors table is made of reference and formula columns only.
The help article uses as a source, a table where you have one column that is a reference (country) and the second column is simply text
But this is my auditores table
First column is a reference to Users table (Users.name). (where I list several users from the company)
Second column just gets the department that user belongs to, from the departamento column in the users table.
Here is the users table
name and email columns are simple text.
Third column has the formula
Departamentos.lookupRecords(email=$id).Departamento
Where it looks Departamento table to see if that person is the Leader of that department.
The other column is a reference so I can just select departments to each user. Thus it shows department user belongs to, even if he/she is not leader/manager.
And finally the Departamentos table
Where I list departments, assign a manager (from the users) to it. I also created a field where it lists all users who belong to that department, trying to make my filtered reference dropdown work.
But it didnt work.
