How to identify employee's who are also supervisors

The easy way is to add the supervisor table in again and link (left outer join) it to the Employee table on Employee Number = Supervisor Employee  Number.

You can then test for NULL on any field from the supervisor table.  If it is not null then the employee is a supervisor

mlmcc

Hi,

Which is the database?
How do you pull the data from database? Is it through command object or through direct table linking?

If you are pulling data through command object, then create an outer join between employee and superv table and pull the employee id details from superv table and check in a formula in Crystal Report to check whether the value is null or not. If the value is not null, then display the message what you want.

You can pull the details using sql as below(Oracle syntax):

select
      emp.employee_id,
      emp.first_name,
      emp.last_name,
      emp.salary,
      mgr.employee_id superviser
from
      employees emp, superv mgr
where emp.employee_id = mgr.employee_id(+)


This will pull all the employee details from employee table and superviser id's from superv table with a null for an employee whose id is not present in superv table

You can then simply create a formula to check if the supervise field value is null or not. When it's not null, you just display the message you want.

mlmcc is faster than me :)

Thank you!  I added the HRSUPER table a 2nd time with a left outer join from the EMPLOYEE table.  I wrote a formula
if {EMPLOYEE.EMPLOYEE} = {HRSUPER_1.EMPLOYEE} then "Manager" and it did not work.  I'm not sure how to write the test for null.

Hi,


What is the issue?  You need to check in formula whether the field value is null, if not null, then it will be a Superwiser.

From what you shared, you seem to be checking for an equi join and setting the value as manager, but not all the employees will be managers and equi join won't work. Check the code I shared and modify it according to your tables for the joins.

If you still need assistance, can you share the code or .rpt file with saved data with expected values?

Thank you.  I'm attaching a sample .rpt with 4 employees.  Evans (emp 9756) and Tolle (9266) are also in the HRSUPER table as managers.  Employees Haden (5880) and Toms (20017) are just employees and are not managers.  In the column titled "Is_emp_also_a_manager?" I expect to see "Yes" or "Manager" for Evans and Tolle.
EQUITY--2.rpt

Just updated my previous response as I think the outer join is correct with what you have used from Employee to HRSUPER1 table.

Thank you, that worked!

Thank you very much, exactly what I needed.