My employee table includes both managers and non-managers. I want to define a field for manager's name and number so that I can list employee_name, employee_nbr, mgr_name, mgr_nbr on a report without listing the manager's name twice. There are several fields that describes the manager (for example, mgmtlevel).However, I don't know how to separate the mangers from the non-managers. Will this require creating two additional tables? Please help. I'm a Novice trying to fill the shoes of an Expert.

Try a Define.
Here's and example you can use...I don't know what your descriptions are.
DEFINE FILE EMPLOYYE
MANAGER/A10 = IF MGR_NBR GT 0 THEN MGR_NAME ELSE ' ';
NON-MANAGER/A10 = IF MGR_NBR EQ 0 THEN EMPLOYEE_NAME ELSE ' ';
END
TABLE FILE EMPLOYEE
PRINT SALARY
BY MANAGER
BY NON-MANAGER
END

Hi

DB using the Define u will seprate the Managers and non Managers.

Tanks

Hi db,

You could create a temporary hold file (from your employee file) for the manager information and then join your employee file back to your new temporary manager file. Here's a piece of untested code to illustrate the concept.

-************************************************************
-* STEP 1
-* CREATE TEMPORARY MANAGER FILE NAMED HOLD_M01
-************************************************************
DEFINE FILE EMPLOYEE
M01_KEY/I9 = EMPLOYEE_NBR;
M01_NAME/A30 = EMPLOYEE_NAME;
M01_LEVEL/A1 = MGMTLEVEL;
END
TABLE FILE EMPLOYEE
SUM
M01_NAME
M01_LEVEL
WHERE YOUR_MANAGER_INDICATOR EQ 'YES'
BY M01_KEY
ON TABLE HOLD AS HOLD_M01 FORMAT FOCUS INDEX M01_KEY
END
-************************************************************
-* DISPLAY FILE LAYOUT OF MANAGER FILE(HOLD_M01)
-************************************************************
? HOLD HOLD_M01
-************************************************************
-* STEP 2
-* JOIN EMPLOYEE FILE TO THE TEMPORARY MANAGER FILE(HOLD_M01)
-************************************************************
JOIN MGR_NBR IN EMPLOYEE TO M01_KEY IN HOLD_M01 AS J01
-*
TABLE FILE EMPLOYEE
PRINT
EMPLOYEE_NAME
MGR_NBR
M01_NAME
M01_LEVEL
BY EMPLOYEE_NBR
WHERE RECORDLIMIT EQ 10
END

I don't know if this is what you need, but maybe it will help.

Jim

Thanks for responding all. It's working somewhat, but not exactly the way I want. I have for example fields from the source for FIRST_NM and LAST_NM which I concatenated and defined as FULLNAME. My goal is to create a field for MGRNAME and one for EMPNAME so that I can list the employeess in one column and their manager in another. Thanks for your help, I'll continue to work with it.

Here'e the same code with defines to put the first and last names together for both the employee and the manager.

-************************************************************
-* STEP 1
-* CREATE TEMPORARY MANAGER FILE NAMED HOLD_M01
-************************************************************
DEFINE FILE EMPLOYEE
M01_KEY/I9 = EMPLOYEE_NBR;
M01_MGR_NAME/A62 = LAST_NM || (', ' | FIRST_NM);
M01_MGR_LEVEL/A1 = MGMTLEVEL;
END
TABLE FILE EMPLOYEE
SUM
M01_MGR_NAME
M01_MGR_LEVEL
WHERE YOUR_MANAGER_INDICATOR EQ 'YES'
BY M01_KEY
ON TABLE HOLD AS HOLD_M01 FORMAT FOCUS INDEX M01_KEY
END
-************************************************************
-* DISPLAY FILE LAYOUT OF MANAGER FILE(HOLD_M01)
-************************************************************
? HOLD HOLD_M01
-************************************************************
-* STEP 2
-* JOIN EMPLOYEE FILE TO THE TEMPORARY MANAGER FILE(HOLD_M01)
-************************************************************
JOIN MGR_NBR IN EMPLOYEE TO M01_KEY IN HOLD_M01 AS J01
-*
DEFINE FILE EMPLOYEE
EMP_NAME/A62 = LAST_NM || (', ' | FIRST_NM);
END
-*
TABLE FILE EMPLOYEE
PRINT
EMP_NAME
MGR_NBR
M01_MGR_NAME
M01_MGR_LEVEL
BY EMPLOYEE_NBR
WHERE RECORDLIMIT EQ 10
END

Does this help any?

Jim

You said "My goal is to create a field for MGRNAME and one for EMPNAME so that I can list the employeess in one column and their manager in another. "

How do you identify which manager applies to an individual employee?

Is there a manager number associated with the employee?
What does your master fiele description look like? That would proabably help make this more clear.

I'm thinking a recursive join could be a possiblity here (join employee table to employee table). If there is an manager number associated with the employee you would join that back to the employee number to pick up the manager's name.

I've looked at the recursive joins, but don't fully understand. I have an employee table which includes both managers and employees. The ID number emp_nbr identifies both manager and employee. The manager does not have a diffrent field name for the ID number. All managers have a mgmt_level number (2 thru 9). Thanks for your help. I know this should be easier than I'm making it, but I'm still learning.

db,

For this to work, there has to be some piece of information that identifies for whom a given employee works. If I'm looking at the record for an employee, is there a field in that record that identifies that employee's manager? If there is, then you can implement the recursive join Piipster is talking about. Suppose employee id is EMP_ID and that employee's manager (in the same record) is MGR_ID. Then:

JOIN MGR_ID in EMPLOYEE
TO EMP_ID in EMPLOYEE AS MGR
END

TABLE FILE EMPLOYEE
PRINT EMP_NAME
BY MGREMP_NAME
END

This prints the employee's name by the manager's name. The MGR from the AS clause is used to prefix EMP_NAME so you get the name from the 'joined to file'. Confusing, huh? I haven't done this in a while, so I may have some details wrong. Read through the join documentation in your Creating Reports manual. I think you can probably puzzle it out from here. However, if you post your master file description, we might be able to provide more exact help.

dwf... that's exactly the type of thing I was thinking. And... yes... the master file description would help us point you further down the track.