User Fields and Crystal Reports

Sort:
You are not authorized to post a reply.
Author
Messages
Shane Jones
Veteran Member
Posts: 460
Veteran Member

    I have been asked about this a few times recently and wanted to share what we did to make user fields easier in Crystal Reports:   If course, the field_name field should be your field names... 

    We added the following as a database view:

    SELECT     COMPANY, EMPLOYEE,
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'AAP Job Groups') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [AAP Job Groups],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Acquisition') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS Acquisition,
                              (SELECT     ufv.D_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Acquisition Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Acquisition Date],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Acquisition source') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Acquisition source],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Agreements') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS Agreements,
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Announcement needed?') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Announcement needed?],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Application Received') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Application Received],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'BN-EL/DI  Date 5') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [BN-EL/DI  Date 5],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'BN-HL/DN  Date 2') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [BN-HL/DN  Date 2],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'BTI Completed') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [BTI Completed],
                              (SELECT     ufv.D_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'BenSal1_Start') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS BenSal1_Start,
                              (SELECT     ufv.D_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'BenSal1_Stop') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS BenSal1_Stop,
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'CLEAVER C') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [CLEAVER C],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'CLEAVER D') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [CLEAVER D],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'CLEAVER I') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [CLEAVER I],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'CLEAVER S') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [CLEAVER S],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Ceridian ID') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Ceridian ID],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Checklist Completed') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Checklist Completed],
                              (SELECT     ufv.N_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Code of Ethics') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Code of Ethics],
                              (SELECT     ufv.D_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Computer Loan Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Computer Loan Date],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'DB Code') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [DB Code],
                              (SELECT     ufv.N_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Drug Policy') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Drug Policy],
                              (SELECT     ufv.D_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Drug Test Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Drug Test Date],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Equity-Holder') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Equity-Holder],
                              (SELECT     ufv.D_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Expected Term Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Expected Term Date],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Fingerprint') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS Fingerprint,
                              (SELECT     ufv.D_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Fingerprint Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Fingerprint Date],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'I-9 Received') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [I-9 Received],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Kronos') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS Kronos,
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Kronos Accrual Prof') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Kronos Accrual Prof],
                              (SELECT     ufv.N_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Lump Sum Merit Amt') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Lump Sum Merit Amt],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Merit Increase') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Merit Increase],
                              (SELECT     ufv.N_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Midpoint2006') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS Midpoint2006,
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'NT Logon') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [NT Logon],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Name Separator - IS') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Name Separator - IS],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'NonEmp/Retire Health') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [NonEmp/Retire Health],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Offer Letter') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Offer Letter],
                              (SELECT     ufv.N_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'PC Policy') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [PC Policy],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Pay Range') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Pay Range],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Pay Rule') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Pay Rule],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Pension Code') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Pension Code],
                              (SELECT     ufv.N_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Policy Manual') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Policy Manual],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'President/LOB') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [President/LOB],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'RS Acct Type') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [RS Acct Type],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'RS Bank Acct') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [RS Bank Acct],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'RS Rollover') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [RS Rollover],
                              (SELECT     ufv.D_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'RS Rollover Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                           AS [RS Rollover Date],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'RS Route') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [RS Route],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Rehire') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS Rehire,
                              (SELECT     ufv.D_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Rehire Date') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Rehire Date],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Salary Review Rating') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Salary Review Rating],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Security Profile') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Security Profile],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Status Change') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Status Change],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Term Code') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Term Code],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Term Controllable') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Term Controllable],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Term PIP?') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [Term PIP?],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Term Regrettable?') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND
                                                       (ufv.EMP_APP = 0)) AS [Term Regrettable?],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'VAC Adj Reason') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [VAC Adj Reason],
                              (SELECT     ufv.N_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'VAC Hours Adj') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [VAC Hours Adj],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'VAC Supv/Officer') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                          AS [VAC Supv/Officer],
                              (SELECT     ufv.A_FIELD AS FIELD_VALUE
                                FROM          dbo.HREMPUSF AS ufv INNER JOIN
                                                       dbo.HRUSERFLDS AS ufn ON ufv.FIELD_KEY = ufn.FIELD_KEY
                                WHERE      (ufn.FIELD_NAME = 'Walking Program') AND (ufv.COMPANY = e.COMPANY) AND (ufv.EMPLOYEE = e.EMPLOYEE) AND (ufv.EMP_APP = 0))
                           AS [Walking Program]
      

    Shane Jones
    Tools: HR, Payroll, Benefits, PFI, Smart Office, BSI, Portal and Self-Service
    Systems: Lawson, Open Hire, Kronos, Crystal Reporting, SumTotal Learning
    ** Teach others to fish...
    You are not authorized to post a reply.