Double LOOKUP()? (Security Filters)

[wrodriguez1]

Hello,

I’m trying to look up the email of the person who is logged in, in the employee table to find out their position (text value). I have that part working fine. What I need next is to use their position to find out what role they are in, based on the role table. That will give me an fk.id that is on a role_level table.

IN(
LOOKUP(
USEREMAIL(),
employee, workAddress_emailAddress, departmentPosition_jobTitle),
LIST(“1”, “2”, “3”, “4”, “5”, “6”)
)

That code will check the position to see if it is in the listed numbers. Unfortunately the positions are a text value, so I need to cross-reference another table to see what the position role is.

---

Logic Example:

email@email.com → employee (table)
→ departmentPosition_jobTitle: manager (position)

manager → role (table)
→ role_level_id (this is an id = to 0-8)

Then LIST() with those 0-8 to give access to a view/form/etc

[wrodriguez1 · Accepted solution]

I figured this out. I used a select after the lookup to check if the lookup result was in a list of that select.

IN(
LOOKUP(
USEREMAIL(),
employee, workAddress_emailAddress, departmentPosition_jobTitle),

SELECT(role[departmentPosition_jobTitle],
AND(
IN([departmentPosition_costCenter1], LIST(“INSTALL”)),
IN(role_level_[id], LIST(8)))
)
)