AnsweredAssumed Answered

Return the last value that is NOT equal to another value

Question asked by Dave_2 on Jan 24, 2013
Latest reply on Jan 26, 2013 by Sorbsbuster

Title

Return the last value that is NOT equal to another value

Post

Quick Summary:

     I would like a script or function that will return a previous value.  If a field has a '' (ditto mark) value, I want it to display what that fields last non ditto mark value was base on the current sort.  This could be the previous record, it could be 15 records ago.  So if a previous record changes its value, all the following ditto marks seem to change as well.

      

Long Version:

     I am using file maker to track a lighting instruments color throughout an event/concert/show.  I need to keep track of when a lighting instrument has changed its color (either through a device known as a "color scroller", which has a string of different colored filters on a roll that it can scroll between or a fancy moving light like you see on TV that uses other methods.)

     I am trying to get filemaker to keep track of and display this in a way that makes it easy to make changes. However to each light and lighting controller, there is a difference bwteen going to red, and just staying in red.  If I later change the light to go to green, it stays in green until it is told to change again.

     I would like to find a way to display what color a light is in, at any moment during a show, but only have to enter when the light changes to a different color.  Otherwise I can have a default value of '' (ditto mark) entered telling me that the light is not being told to change colros.  Here's what I mean:

What I Enter

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                    CUE                     LIGHT 1                     LIGHT 2                     LIGHT 3                     LIGHT 4
                    SONG 1                     RED                     RED                     BLUE                     BLUE
                    cue 1                     

                         ''

               
                    ''                     ''                     ''
                    cue 2                     BLUE                     ''                     ''                     ''
                    cue 3                     ''                     ''                     ''                     ''
                    SONG 2                     GREEN                     GREEN                     ORANGE                     RED
                    cue 1                     ''                     ''                     GREEN                     ''
                    cue 2                     ''                     ''                     ''                     ''
                    cue 3                     GREEN                     ''                     ''                     ''
                    cue 4                     ''                     ''                     ''                     ''

   

WHAT IS DISPLAYED

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                    CUE                     LIGHT 1                     LIGHT 2                     LIGHT 3                     LIGHT 4
                    SONG 1                     RED                     RED                     BLUE                     BLUE
                    cue 1                     

(RED)

               
                    (RED)                     (BLUE)                     (BLUE)
                    cue 2                     BLUE                     (RED)                     (BLUE)                     (BLUE)
                    cue 3                     (BLUE)                     (RED)                     (BLUE)                     (BLUE)
                    SONG 2                     GREEN                     GREEN                     ORANGE                     RED
                    cue 1                     (GREEN)                     (GREEN)                     GREEN                     (RED)
                    cue 2                     (GREEN)                     (GREEN)                     (GREEN)                     (RED)
                    cue 3                     GREEN                     (GREEN)                     (GREEN)                     (RED)
                    cue 4                     (GREEN)                     (GREEN)                     (GREEN)                     (RED)

      

     Can this be done?

     So far all I've been able to do is use a GetNthRecord in conditional formatting to display the previous value if the current value is a ditto mark and change its color.

      

     Let ( [ 
     R = Get ( RecordNumber ) ;
     $this = Scroller_Tracking::${Scroll_1_c} ;
     $prev = GetNthRecord ( Scroller_Tracking::${Scroll_1_c} ; R - 1 ) ;
     $$trackedcolor1 = $prev
     ] ;
     If ( $this = $prev ; 1 ; 0 ))

     And then I conditionally grey out the '' marks and display <<$$trackedcolor1>>

Outcomes