| Author |
Message |
Sime
Newbie
Joined: 07 Jan 2010
Online Status: Offline
Posts: 6
|
  
Topic: Find last date entered
Posted: 07 Jan 2010 at 8:21am |
|
Hi experts,
I'm fairly new to Crystal, basically I have a staff in / out system here - staff come in and register in, staff leave and register out.
I need to find out who isn't doing this - so I need to find the last in record which is older than say 30 days.
The only problem is, if I do something like.... not ({staffmovement.movementindate} in LastFullMonth) ....it will still show everyone because some people were logging in over a month ago but are not now.
Is there a way of telling crystal too look at only the last in date and then if it's more than 30 days in the past, show me their name?
I'm quite new so simple terms would help! I've spent hours on this trying different things off the internet but nothing has worked so far.
Many thanks.
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 07 Jan 2010 at 9:46am |
There are a couple of ways to do this. Try this:
1. Group by person - I would use name plus some sort of ID so that you get this in alphabetical order but you can also show folks who might have the same name.
2. Sort by {staffmovement.movementindate} descending.
3. Put your data in the group header for the person. Because you've sorted descending by date, the most recent date will be the first one in the list and will appear when you show the header.
4. In the Section Expert, put a suppression formula on the person group header that looks something like this:
{staffmovement.movementindate} < CurrentDate - 30
Do Not check the Suppress check box!
-Dell
|
|
|
| IP Logged |
|
Sime
Newbie
Joined: 07 Jan 2010
Online Status: Offline
Posts: 6
|
Posted: 08 Jan 2010 at 1:29am |
|
Thanks for your advice, I followed what you said but it now shows the last date that someone logged in above 30 days ago. I really need to find who hasn't logged in for a solid 30 in a row.
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 08 Jan 2010 at 7:07am |
What type of database are you connecting to? Are you familliar with SQL? I think you may have to use a command to do this.
You're sql will have the following logic:
Select (fields for your report)
from login_table as lt
(joins to other tables if necessary)
where (any criteria other than the login date)
and (CurrentDate - 30) >=
(Select max(login date)
from login_table as lt1
where lt1.user_id = lt.user_id)
For "CurrentDate" use whatever syntax your database has to get the current date. In MS Sql Server this is GetDate() and in Oracle it's Sysdate.
-Dell
where
|
|
|
| IP Logged |
|
Sime
Newbie
Joined: 07 Jan 2010
Online Status: Offline
Posts: 6
|
Posted: 11 Jan 2010 at 12:36am |
|
It's a sequel database, I'm not sure how to implement the SQL though. Going back to your first solution which is almost what I need - is there any way to sort the list by last login time rather than surname? At least then I can see who hasn't logged in for quite some time first.
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 11 Jan 2010 at 6:39am |
I'm not sure if this will work, but it's worth a shot.....
Following the instructions from my first post, do the following:
1. In the person group header, replace the date with a summary that is the Maximum login date for the person.
2. In the suppression formula for that section, put something like the following formula:
WhilePrintingRecords;
{staffmovement.movementindate} = Maximum({staffmovement.movementindate}, {staffmovement.userID})
The field after the comma needs to be the field that you're grouping on for the person. DO NOT check the Suppress checkbox and the suppress formula will have to be applied to any section with user data in it - I would not use details sections, use the group header and footer instead.
-Dell
|
|
|
| IP Logged |
|
Sime
Newbie
Joined: 07 Jan 2010
Online Status: Offline
Posts: 6
|
Posted: 12 Jan 2010 at 1:54am |
|
I'll give it a try thankyou. I'm just too sure on what you mean by point one - putting a summary?
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 12 Jan 2010 at 6:20am |
There are two ways to do this:
1. Click on the Summary button (the one that has a Sigma on it - looks like an angular E).
2. Go to the Insert menu and select Summary.
Select the staffmovement.movementindate field, maximum, and the field or formula that you're using to group by person. This will give you the most recent date for each person.
-Dell
|
|
|
| IP Logged |
|
Sime
Newbie
Joined: 07 Jan 2010
Online Status: Offline
Posts: 6
|
Posted: 13 Jan 2010 at 1:57am |
Had a go - I did it OK but it still just shows the same as
{staffmovement.movementindate} < CurrentDate - 30 does.
I had another idea, could I put 30 days into a field somewhere, then say if last movement in date is less than or equal to that field display it?
|
| IP Logged |
|
jaykav99
Newbie
Joined: 07 Apr 2009
Online Status: Offline
Posts: 14
|
Posted: 13 Jan 2010 at 1:32pm |
Sime, i came on this board today for the exact same thing. Me and my coworker figured out how to do it for us and i think it may work for you.
First
Group by employee
then create a formula using the maximum fld,condfld option under the summary function
here is what the formula should look like
Maximum ({saw_order1.Date},{saw_orderitem1.Product})
In your case order date is "in date" and product will be your "employee"
The last thing you need to do is throw your employee fields and the formula all into the group footer so it only shows you one record for each employee
Also one more thing the only search parameter we used so the report wasn't insanely large was order date > or = 01/01/2009 Edited by jaykav99 - 13 Jan 2010 at 3:20pm
|
| IP Logged |
|
|
|
