5 Replies Latest reply on Jan 26, 2013 3:56 PM by Sorbsbuster

    Return the last value that is NOT equal to another value

    Dave_2

      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>>

        • 1. Re: Return the last value that is NOT equal to another value
          Sorbsbuster

               I think it can be simpler than that.  Sort the records first, of course.  Attach a script trigger to the Light Colour field:

               Enter Browse Mode
               Set Variable [ $LastColour ; LightColour1 ]
               Loop
               If [ LightColour1 = "[whatever ditto character you are using]"]
               Set Field [ LightColour1 ; $LastColour
               Go to Record [Next ; Exit after last]
               Else
               Halt Script
               End Loop

               That would all appear to work automatically.  However I would worry that you would trigger the script without the records being sorted into the right order, because it would totally mess up your records, and the changes would be un-doable.  I think at least in the short-term I would have an 'Update' button, with a 'Mis-click...?' warning dialogue until I was absolutely certain I had the automatic process bug-free.

               It might be safer to prepare the whole record set up with ditto marks as you have it, then sort it, edit it, refine it, and make absolutely certain that it looks the way you want.  Then hit one Big Button that runs that script down every record (in other words does not stop when it comes to a non-ditto mark, but resets the $LastColour variable and carries on to the end of the list) and replaces all the ditto marks.

          • 2. Re: Return the last value that is NOT equal to another value
            philmodjunk

                 Did you know that if you hold down the control key and press the quote key, FileMaker copies data from the same field of the most recently edited record?

            • 3. Re: Return the last value that is NOT equal to another value
              Dave_2

                   I'm looking for a way to do this WITHOUT actually changing the stored value in the field.

              • 4. Re: Return the last value that is NOT equal to another value
                Sorbsbuster

                     Does this: "So far [...] I've been able to [...] GetNthRecord in conditional formatting to display the previous value if the current value is a ditto mark"

                     and this:

                "I'm looking for a way to do this WITHOUT actually changing the stored value in the field"

                     not mean that you have already solved the problem that way?

                      

                • 5. Re: Return the last value that is NOT equal to another value
                  Sorbsbuster

                       For display purposes have another unstored calculation field, ColourCalculated, defined as:

                       Case (

                       ColourText ≠ "x" ; ColourText ;


                       GetNthRecord ( ColourCalculated ; Get ( RecordNumber ) - 1)

                       )

                       (I used 'x' as the ditto character to avoid confusion with too many "s.)