3 Replies Latest reply on Oct 7, 2012 5:00 PM by philmodjunk

    Conditional format/Display Alternate Value

    Dave_2

      Title

      Conditional format/Display Alternate Value

      Post

           I am trying work out some conditional formatting in my layouts.  The key of this particular database it to track changes over time from one record to the next (always sorted by a "cue number").  So far I have been able to change the color of the text of a record if it is = its previous value.  

           My Question:  Can I also use conditional formatting to display an alternate value if the contents of a field has a particular value in it?

           When a field contains a '' (ditto mark) I would like it to display the last value that is not a ditto mark in parenthises (when sorted by "cue number).

           EX here is a table displaying actual values:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                          CUE                     Fix1                     Fix2                     Fix3                     Fix4
                          1                     CP3                     CP1                     CP4                     CP7
                          2                     ''                     ''                     ''                     CP7
                          3                     ''                     ''                     CP9                     ''
                          3.3                     ''                     ''                     ''                     CP5
                          3.7                     ''                     ''                     ''                     ''
                          5                     CP1                     CP1                     CP1                     CP1
                          9                     ''                     ''                     ''                     CP2
                                                                                                                   

            

      This is how the Table would need to actually display:

            

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                          CUE                     Fix1                     Fix2                     Fix3                     Fix4
                          1                     CP3                     CP1                     CP4                     CP7
                          2                     (CP3)                     (CP1)                     (CP4)                     CP7
                          3                     (CP3)                     (CP1)                     CP9                     (CP7)
                          3.3                     (CP3)                     (CP1)                     (CP9)                     CP5
                          3.7                     (CP3)                     (CP1)                     (CP9)                     (CP5)
                          5                     CP1                     CP1                     CP1                     CP1
                          9                     (CP1)                     (CP1)                     (CP1)                     CP2

            

           Any help would be appreaciated!

        • 1. Re: Conditional format/Display Alternate Value
          philmodjunk

               I can think of a way to make this work, but it may be simpler just to define a calculation field that uses GetNthRecord to extract the value from the previous record whenever Get ( RecordNumber ) is greater than 1 and the current record's value is your ditto mark.

               The alternative (works only in FIleMaker 11 and newer) is to add a merge variable to your layout that is loaded with a value via a Let function inside a conditional format expression. The variable would be loaded with a value with the same expression that I just outlined for the calculation field.

          • 2. Re: Conditional format/Display Alternate Value
            Dave_2

                 Hi Phil,

                 Thanks for the help.  I think I'm partway there.  I have a Let function inside a conditional format expression that will create a merge variable.  It works to display the previous value in ()'s when the field is a ditto mark using GetNthRecord.  The problem comes when multple records sequentially in the sort are all ditto marks.  The first ditto mark displays the previous record like I want, but the subsequent records do the same displaying a ditto mark.

                 Is there a way to have the conditional format formula keep going back until it finds a field that is not a ditto mark?

                  

                 Thanks!

            • 3. Re: Conditional format/Display Alternate Value
              philmodjunk

                   Not that I can think of without using a recursive custom function. The calculation fields on the other hand, can reference the previous calculation field to form a chaing that recurses back to the most recent record not to show the ditto marks.

                   It might be simpler to structure your data entry layout so that entering ditto marks automatically enter the value from the previous record. If you hold down the control key and press the quote key, this will happen as a built in short cut without any additional scripting required.