Skip to Content
author's profile photo Former Member
Former Member

Flag records missing a matching 'next' record

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Apr 13, 2011 at 06:04 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • author's profile photo Former Member
    Former Member
    Posted on Apr 14, 2011 at 02:54 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • author's profile photo Former Member
    Former Member
    Posted on Apr 14, 2011 at 04:39 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.