Sage 100

ChatGPT will write/debug most of this in Crystal Reports for you. It's not always 100% but it will get you really close and cut down on any trial and error.

Try a prompt like this: 

Client has 200+ employee PR.  They are asking for a report that lists MISSING employees in the PR Data Entry.  Looking for a way to detect the MIA's.  Am I missing something?

Relevant table for the customer master is PR_Employee and we'd want to test the the field EmployeeStatus="A" (for active ) and use this master table against the payroll data entry to see if an employee is NOT in the payroll data entry table - PR_PayrollHeader -- can you write the join that  I need to show me all employees in the PR_Employee whose EmployeeStatus="A" and are NOT in PR_PayrollHeader. I want to use this as a Crystal Report select.

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT
------------------------------

Using Wayne's prompt ChatGBT gave me:

SELECT e.EmployeeID, e.EmployeeName, e.EmployeeStatusFROM PR_Employee eLEFT JOIN PR_PayrollHeader p ON e.EmployeeID = p.EmployeeIDWHERE e.EmployeeStatus = 'A'  AND p.EmployeeID IS NULL;

While impressive, even changing EmployeeID to EmployeeKey and EmployeeName to LastName it doesn't get the linking correct. Plus I needed to add the Payroll Header EmployeeID in addition to the PR_Employee one when entering this into a connection command which is the only place I know where to enter SQL directly into Crystal.

What works is:

 SELECT e.EmployeeKey, e.LastName, e.EmployeeStatus, p.EmployeeKey FROM   {oj PR_Employee e LEFT OUTER JOIN PR_PayrollHeader p ON e.EmployeeKey=p.EmployeeKey} WHERE e.EmployeeStatus='A' AND p.EmployeeKey IS  NULL 

I use ChatGBT extensively, but mostly for generating small algorithms.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------


Original Message:
Sent: 05-31-2024 10:45
From: Wayne Schulz
Subject: Crystal Report to Detect MISSING Employees

ChatGPT will write/debug most of this in Crystal Reports for you. It's not always 100% but it will get you really close and cut down on any trial and error.

Try a prompt like this: 

Client has 200+ employee PR.  They are asking for a report that lists MISSING employees in the PR Data Entry.  Looking for a way to detect the MIA's.  Am I missing something?

Relevant table for the customer master is PR_Employee and we'd want to test the the field EmployeeStatus="A" (for active ) and use this master table against the payroll data entry to see if an employee is NOT in the payroll data entry table - PR_PayrollHeader -- can you write the join that  I need to show me all employees in the PR_Employee whose EmployeeStatus="A" and are NOT in PR_PayrollHeader. I want to use this as a Crystal Report select.

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT