AnsweredAssumed Answered

ExecuteSQL Conditional Where Clause?

Question asked by jakebutt14 on May 2, 2017
Latest reply on May 2, 2017 by jakebutt14

Hi everyone -

 

I'm using ExecuteSQL to create dynamic value lists and have run into a bit of a snag. Here's my scenario:

 

We have an application that is for assigning documents for review and signature. I have an EMPLOYEES table where each employee is classified by a "role", and an SIGNATURES table where each individual reading assignment/signature is given a responsibility. For simplicity sake, let's say the only three roles for employees are Employee, Scientific, and Management. Right now, I am creating a value list where only certain employees are displayed based on their role and the responsibility designated to the signature assignment, so if a signature assignment is designated as "Scientific" responsibility, only those employees classified with a role of "Scientific" will appear in the value list.

 

What I want to do, however, is make it so that if the responsibility is labeled "Employee", ALL employees (regardless of their role classification) are displayed in the value list. This way, we can have a limited value list if a signature is needed for a certain level of employee (such as Scientific), but it would still allow us the ability to have certain signatures that can be assigned to any employee, regardless of their assigned role.

 

Here is my current ExecuteSQL statement... right now, the part highlighted in red provides matching the signature responsibility to the employee's role, but I'm not sure how to build in allowing for a responsibility of "Employee" to generate a list of all employees. Any suggestions? I have played around a bit with using a Case statement in place of the red text, but haven't been able to get it working yet.

 

ExecuteSQL (

 

"SELECT nameFirstLast

FROM Employees

WHERE role = ?

ORDER BY nameFirstLast" ;

 

"" ; "" ; SIGNATURES::responsibility )

 

As always, thanks in advance for all the help!

Outcomes