cancel
Showing results for 
Search instead for 
Did you mean: 

Flag records missing a matching 'next' record

Former Member
0 Kudos

I have a series of data in a report that looks like the following:

827,489

Start Date/Time 8/18/2010 3:23:57 PM 0.00 0.00 1

Stop Date/Time 8/18/2010 4:35:19 PM 72.00 72.00 2

*Stop Date/Time 8/18/2010 6:03:06 PM 160.00 232.00 3

829,819

Start Date/Time 8/25/2010 4:14:22 PM 0.00 0.00 1

Stop Date/Time 8/25/2010 4:26:22 PM 12.00 12.00 2

827,519

*Start Date/Time 8/18/2010 4:05:53 PM 0.00 0.00 1

830,662

Start Date/Time 8/27/2010 3:52:17 PM 0.00 0.00 1

Stop Date/Time 8/27/2010 5:04:43 PM 72.00 72.00 2

830,721

Start Date/Time 8/27/2010 5:15:03 PM 0.00 0.00 1

Stop Date/Time 8/27/2010 5:59:21 PM 44.00 44.00 2

*Stop Date/Time 8/27/2010 8:31:39 PM 196.00 240.00 3

831,180

Start Date/Time 8/30/2010 3:22:09 PM 0.00 0.00 1

Stop Date/Time 8/30/2010 4:16:48 PM 54.00 54.00 2

You'll see that I essentially have start and stop times that are manually entered into a database through an interface. Unfortunately, users don't always remember to enter their start or stop times. Each record should be a pair.

Now, for the most part I've been able to flag the records using a variety of different formulas, but always with the same results. If the last record in one group (the 6 digit number) is a Start (ie. only one record with no matching stop) and the next groups record begins properly with a 'start', it thinks the record in error is the second one that is starting properly. Not the one with the missing 'stop' record.

So, essentially I need to flag each record (indicated by the * above) that does not have it's mate.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

OK, this looks like it works for me. I actully just did this as a test and then instead of colors created a manual running total that set the value to zero if it fell within the value

if {@counttest2}=1

and {@StartORStop} = "Start Date/Time"

then White

else

if {@counttest2}=1

and {@StartORStop} = "Stop Date/Time"

then green

else

if ( {@StartORStop} = "Start Date/Time"

and next({@StartORStop}) <> "Stop Date/Time" )

or

( {@StartORStop} = "Stop Date/Time"

and previous({@StartORStop}) <> "Start Date/Time" )

then green

else white

Former Member
0 Kudos

Yeah, I messed with that to some effect and this is what I got. It works in most all cases but misses some if there is only a single start in a record (no stop) and the previous record ended with a stop. That's fine to an extent as a start will always equal zero so it's not impacting totals. My code is just sloppy though and it seems to me there should be a better way to write it.

If next({Incident.Incident #})<>previous({Incident.Incident #})

and {@StartORStop} = "Start Date/Time"

Then white

else

if {@StartORStop} = "Start Date/Time" and previous({@StartORStop}) <> "Stop Date/Time"

or

{@StartORStop} = "Stop Date/Time" and previous({@StartORStop}) <> "Start Date/Time"

then

green

else

white

Former Member
0 Kudos

Anytime you do something with next or previous, it gets sloppy.

if you put a running total in the detail that resets on the group header you have a counter that you can use in the group

to count records.

put this formula in the group header-do not repeat group header on each page. *

It must print-do not hide it or the header it is in.

You can set the font to white so you won't see it if you like:

//{@RT.record number.zero)  <formula name
   whileprintingrecords; 
   numbervar recordnumber := 0

put this formula on the detail line. It too must print-do not hide it.

//{@rt.record number}  <formula name
   whileprintingrecords;    
   numbervar recordnumber;
   recordnumber:= recordnumber + 1;

now you can test for the first record in each group. Don't forget your parenthesis around the AND statements seperated by the OR.


if {@rt.record number}=1
then
       if  {@StartORStop} = "Start Date/Time" then white else green  
             // this assumes the first record should always be a start 
else

if   (  {@StartORStop} = "Start Date/Time" 
          and previous({@StartORStop}) not equal  "Stop Date/Time"  )
   or
     (  {@StartORStop} = "Stop Date/Time" 
         and previous({@StartORStop}) not equal "Start Date/Time" )
then  green  else  white

  • there is a way to do this if you must repeat group headers. Put it in group header a and set group header a to underlay the next group header which contains the information you want to print on each page. Then conditionally format group header a to suppress with

    inrepeadedgroupheader

    and it will only print once and therefore will only reset once per group

Former Member
0 Kudos

This is what I have but for the one record where there is only a start (no stop) it flags the start in the next record:

if {@StartORStop} = "Start Date/Time" and previous({@StartORStop}) <> "Stop Date/Time"

or

{@StartORStop} = "Stop Date/Time" and previous({@StartORStop}) <> "Start Date/Time"

then

green

else

white

Switching to a next vs previous flags the detail record before the one that is incorrect.

Thinking I need to reset at the top of each group.

Former Member
0 Kudos

And you need to test that the first record for each group is a start.

Maybe a running total to count records in each group that resets in the group header. Then you can test for record #1 in each group.

Debi