Filtering Crystal Records - Show all Records if any record matches a criteria

I am trying to modify an existing report to show all scans if there was a scan in the last month. Database is Access 97.  The DB linking SS is attached, as well as my current filters.  Logically it seems like it should be something like this: "If {newtrack.job} has a {Newtrack.Date}  in LastFullMonth then show all records for all dates for that {newtrack.job}"

Any help turning that into crystal language would be appreciated. My main problem is when i try and filter It ends up filtering out just those records that occured last months and displays only those. I need it to display stuff previous to that as well but only if there is a date in this month preferably a date that has a station 100 code in the last full month.  From the link SS newtrack.station would have code 100 nwpotrk.laststation (last scan to newtrack.station) and Station.number would also have code 100 (reference list)
Current Filters:
 {Newtrack.Station} <> 10001 and
not ({STATION.Station_Name} startswith ["IN BILLING", "INTERRUPTED", "LUNCH", "Lunch IN", "LUNCH OUT", "PARTIAL", "RESUME", "SHIPPED", "STAGED"]) and
{NWPOTRK.Dollar_Amt} > $2499.00 and
{NWPOTRK.Last_Station} in [100, 16] and
{NWPOTRK.Ordered} > DateTime (2008, 06, 01, 00, 00, 00) and
(IsNull({NWPOTRK.Requested}) OR {NWPOTRK.Requested} > DateTime (2008, 12, 01, 00, 00, 00)) and
not ({STATION.Station_Number} in 90000 to 99999) and
{Newtrack.Date} in LastFullMonth

Open in new window

Who is Participating?
James0628Connect With a Mentor Commented:
First of all, LastFullMonth is the previous calendar month.  For example, as of today, it would be June 1 - June 30.  Is that what you mean by "in the last month", or are you looking for dates within the past month, ending with today (eg. June 17 - July 16)?

 If you have a group on {newtrack.job}, I think you can get just those jobs to show by using a group selection formula.  Create a formula like the following (call it whatever you like):

if {Newtrack.Date} in LastFullMonth then

 All that does is output a 1 if Date is in LastFullMonth.

 Then go to Report > "Selection Formulas" > Group and enter the following:

Sum ({@formula created above}, {newtrack.job}) > 0

 That tells CR to only show a job group (ie. job) if the total from that first formula is > 0 (ie. where at least one Date was in LastFullMonth).

 If you try that, don't forget to take the {Newtrack.Date} in LastFullMonth out of your record selection formula.

 Note that a group selection only suppresses the groups that don't meet the criteria.  The report will still read all of the records and any regular summaries in the report will still include them.  Also, if the report is displaying the group tree, the groups (jobs) that aren't shown will still be listed in the tree, but if you try to select one of them, you'll just be taken to the nearest job that is _not_ suppressed.

 I'm not sure exactly what you were trying to do with the "code 100" stuff, but if you only want to check the date on certain records, you can probably handle that by adding the appropriate tests to the first formula above, so that, for example, it only outputs 1 if the date is in range _and_ newtrack.station = 100 (or whatever).

knausscpaAuthor Commented:
Added Note: Newtrack has several records per job # (records each scan to a job) NWPOTRK has 1 record per job number.
Seems like you will need to use a sub report.  If you put a sub in the detail section of the main report, then you could pass the newtrack.station to the sub, then in the sub use a similar filter but set newtrack.station=?PM-newtrack.station and remove the part about Date in LastFullMonth
Agree.  I cant think of any way to include all records if there is a current record except through a subreport.

You might be able to do it through some fancy SQL code.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.