8 Replies Latest reply on Aug 16, 2016 10:13 AM by David Moyer

    Conditional Formatting on Repeating Field



      Conditional Formatting on Repeating Field


           I have a repeating calculation (number) field with 7 repetitions in a table (Timesheet::Daily Total), and a single number field in another table (Insured::Budget).

           Using conditional formatting, I want any one of the Daily Total repetitions to display red when that repetition exceeds the value in Budget (but not all repetitions, only the one that exceeds).

           Conditional Format on Timesheet::Daily Total
      Value isgreater than Insured::Budget

           It doesn't work.

           If I set Value isless than, ALL repetitions turn red no matter what the value of Timesheet::Daily Total.


        • 1. Re: Conditional Formatting on Repeating Field

               Looks like another reason not to use repeating fields--they lost most reasons for being used when FileMaker 3 was released and the system went from flat file to relational.

               A related table of records with one record in place of each repetition would not have this issue.

          • 2. Re: Conditional Formatting on Repeating Field

                 I figured that might be your answer after seeing your other posts on similar topics. But the fact remains, sometimes (even often) repeating fields are a MUCH easier way to do things. In this case, a relational portal would be far more complicated than necessary.

                 Is there no way to accomplish this conditional formatting?


            • 3. Re: Conditional Formatting on Repeating Field

                   I created one solution, using an additional calculation field. (Daily Total is a Summary field).

                   If ( Daily Total < Extend ( Insured::Budget );
                   Daily Total;
                   TextColor ( Daily Total ; RGB ( 255;0;0 ) ))


              • 4. Re: Conditional Formatting on Repeating Field

                     I would argue that while repeating fields can be easier to set up on a layout, in every other aspect, such as this, they become much more difficult to work with and the savings you gain in your simple layout is eaten up by the special handling (such as your calculation field) needed in order to get what you need out of your repeating fields.

                • 5. Re: Conditional Formatting on Repeating Field

                       In this case it was a relatively very small price to pay. The gains were greater than the detriment.

                       I'll agree to disagree!


                  • 6. Re: Conditional Formatting on Repeating Field


                         Thank you for your post, and I apologize for the late reply.

                         There has been a previous report of specifying the repeating field in the formula where the first repeating value is used for the entire set of entries.  Instead, when setting up the Conditional Formatting, change the formula to:

                         Self < Insured::Budget

                         The correct values will then be highlighted.

                         Let me know if you need additional clarification.

                         FileMaker, Inc.

                    2 of 2 people found this helpful
                    • 7. Re: Conditional Formatting on Repeating Field

                      Hi LambdaEnt,


                      it is a late solution and I guess this post is mostly for other people finding this thread.


                      As TSGal pointed out, Self is very helpful here. I had the situation that I needed conditional formatting in a repeating field based on the corresponding repetition in another repeating field. For that to work i had to somehow figure out, which repetition the calc is using. the solution is




                      Using that in the formatting calc will give you the number of the repetition the calculation is being called from. This allows me to compare corresponding repetitions in 2 (or more) fields.

                      so lets say I have RepField1 and RepField2 and want to compare repetitions:


                      let (

                      ~currentRep = Get(CalculationRepetitionNumber);               //Get the Repetition number currently being evaluated.

                      Self > RepField2[~currentRep]                                               //Compare to RepField2 on the same repetition



                      So no workaround needed, can all be done!

                      • 8. Re: Conditional Formatting on Repeating Field
                        David Moyer

                        Talk about learning something new every day ...

                        I've been using FM for about 100 years, and I didn't know that I could use Self in that context (i.e. in place of Get(CalculationRepetitionNumber) ).  Sweet.