Link to home
Start Free TrialLog in
Avatar of Sue Taylor
Sue TaylorFlag for United States of America

asked on

Display names part 3

James and mlmmc,

I didn't notice this right away because I was so happy that the shifts were displaying correctly......

Now that we have successfully displayed all three shifts on my report, I no longer see the names of employees that failed to punch in on jobs.

I can get the names to display again, if I remove all the data in record selection that filtered the dates and times by shift.

Is there any other way for me to display those employees that fail to clock in?
Avatar of Mike McCracken
Mike McCracken

You can try putting that in the command but it would have to change.

The issue is a NULL datetime cannot fit into the range.

How do you know that someone didn't punch in?  Do they still have records in aptp_view_time_cards for that day?

  The Command was (I assume still is) checking for "aptp_view_time_cards"."cardstarttime_ts" with a date that's yesterday or today.  If they don't have a record in aptp_view_time_cards for that day, then the Command won't include them and that will have to be changed.  If they do have a record for that day, then the Command will include them, but then how do you know that they didn't punch in?

Avatar of Sue Taylor


There is an application that I go in and see that an employee didn't have any punches.  Supervisors also use this application to make corrections to an employees punch if they entered the wrong job number or something.  At this time, we are not using bar coding or anything like that to prevent employees from making errors.

Also, I removed all of the time filtering stuff in my record selection and re-ran the report and was able to see a couple of people that didn't have any punches.
By filtering on the joined table, Crystal automatically treats the join as inner so you only get records with data in both tables.

WHat you want is to see a list of ALL employees with the time they worked on a specific day or timeperiod.  You even want to see those who did not work or never clocked in/out.

The easiest way to do this is to use a subreport to show the time worked

The main report has the employee list
The subreport will be the report you have link it to the main report on the employee id and the time frame


 But if the report is using a Command, will CR change the Join in the Command?


 I meant how would the _report_ know that someone didn't punch in?  An obvious guess would be that they either don't have a record in aptp_view_time_cards for that day, or they have a record for that day, but there is no time, or something else in that record tells you that they didn't punch in.

 If they don't have a record for that day, then the date check in the Command should be excluding them (assuming that you're still using the Command that you posted in a previous thread).  So, if you're seeing some of those people when you remove the record selection formula, and you're still using the same Command, then it seems that they have records for that date.  If so, that brings us back to the question of how to tell those records from the ones for people that _did_ punch in.

It probably won't really change the join but the null records will be discarded on the selection since the date is not in range so it is essentially the same thing

Avatar of James0628

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I ran my report just now and with this being a weekend, there was limited number of people working.  Therefore, there should have been a lot of people with no punches.

1.pdf shows what the report would look like as it is written (No punches are NOT being displayed)
2.pdf shows it after I removed all of the select expert data
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That did it!

Thank you both for all your help on this.

You're welcome.  I'm glad you were able to get it working.

 FWIW, I finally figured out _how_ it's working.  :-)  That was throwing me.  Your Command tests cardstarttime_ts to see if it's yesterday or today.  That's the kind of thing I would  normally do in a Where, which would eliminate any people with no punches, because they didn't have a record for those dates.  But you're doing the test in the Join, which is Left Outer, so you get a null cardstarttime_ts for anyone that had no punches for those dates.  That's the part I kept missing.  I kept thinking that the test was in the Where.

  So now I see what's happening.  That was really bugging me.  :-)