Alternating Color Based on Alternating Data

Leave a comment

6-5-2008 by Geoff Coffey





The Problem


Sample data with ranges of dates.


As dates change, the background color of the fields alternates.


Formulating a Solution

The goal is simple, but it turns out this is a tricky thing to accomplish in FileMaker. After a little thought, it becomes pretty clear you’re going to be using the GetNthRecord function. It’s just about the only way FileMaker calculations can make decisions based on data in other records in the found set.


GetNthRecord(My Table::My Field, 7)

The above formula gets the value of the My Field field from the 7th record in the found set. Simple.

The first step in solving out problem is to identify the boundaries. In other words, find the places where the DATE field changes values. You can do just that with a formula like this:

GetNthRecord(My Table::DATE, Get(RecordNumber) - 1) = My Table::DATE

This formula compares the value of the DATE field in the previous record (Get(RecordNumber) - 1) to its value in the current record. If you put this in a calculation field, you get results like this:


As you can see, the new field shows a 1 if the date is the same as the previous record, and a 0 otherwise. This is useful information, but it doesn’t go far enough. There’s no way to decide which color each record should use.

The Solution

Let’s break it down. The color for any given record will either be the same as the previous record (if the date is the same) or different from the previous record. The formula above tells you when the color should change, and when it should be the same.



It is a little known fact that FileMaker calculation fields can refer to themselves. In this case, our field will refer to its value from a different record, but in fact, a calculation field can even refer to itself in the same record, and FileMaker will just use its current value. But I digress. At any rate, here’s what our calculation will do:

  • Check to see if the date in this record matches the previous record.
  • If it does, set itself to the same value it has in the previous record.
  • If it does not, set itself to a different value from the previous record.

Here’s the formula that does the trick. Put this in a field called _COLOR (or use a different name and modify the formula accordingly).

    [previous_record = Get(RecordNumber) - 1;
    previous_color = GetNthRecord(_COLOR; previous_record)];

    If(GetNthRecord ( DATE ; previous_record ) = DATE;
        not previous_color

You need to Make this field unstored. Otherwise, it won’t recalculate properly as you change your found set or sort order.

This formula might give you trouble when you try to create the calculation field. After all, you’re creating the _COLOR field, and the formula uses the _COLOR field. FileMaker might complain because, until you’ve OK’d the dialog box, the field doesn’t exist, and you can’t refer to a field that doesn’t exist. The trick is to make the field first, then set its formula. When you first create the field, just OK the calculation dialog box with an empty formula. Then click Options again and add the correct formula.


  • previous_record is the record number of the previous record.
  • previous_color is the value of the _COLOR field from the previous record.

It then checks to see if the date has changed. If not, it uses the previous_color value. Otherwise it switches the value.

In this case, I use the expression not previous_color to make the switch. The not operator has the power to turn a 1 to a 0 and a 0 to a 1, which happens to be just what I want. If that makes your head hurt, you can do it like this instead: if ( previous_color = 1, 0, 1 )

Here’s how this field shakes out in our database:


Notice that it perfectly alternates between 1 and 0 along with the dates, which is just what we need. To finish up, just add conditional formatting to your fields using the _COLOR field to help:


More Colors


    [previous_record = Get(RecordNumber) - 1;

    If(GetNthRecord ( DATE ; previous_record ) = DATE;
        Mod(previous_color + 1; 4)

The meat of the change is this line:

Mod(previous_color + 1; 4)

In other words, when the date has changed, we take the previous color value and add one to it. To keep if from getting too big, we use the Mod function, which has the useful power of “rolling it over” to zero when the value reaches 4. If you want five colors, or three, or 2,4601, just modify the second parameter to the Mod function here.

To make this change work, though, we had to get a little smarter with the previous_color variable as well. In the original example, this variable would be ? (or “undefined”) for the first record since there is no previous color value to fetch. That doesn’t cause a problem because the not previous_color line turns ‘?’ in to zero for us. But the Mod function isn’t so forgiving. So the above formula also makes sure previous_color always has a valid number value.




  1. David Head

    Excellent lateral thinking and a very cool solution.

    Yet another reason why I love conditional formatting.


  2. David Zakary



    Any thoughts on how to accomplish that?


  3. Geoff Coffey

    @david zakary:

    I thought about addressing that in the article, but I was getting tired ;)

    I think it should be fairly easy. Instead of:

    not previous_color

    You would do something along these lines:

    mod(previous_color + 1, 4)



  4. David Zakary


  5. Geoff Coffey




    So here is a revised version that works properly:

      [previous_record = Get(RecordNumber) - 1;
      If(GetNthRecord ( DATE ; previous_record ) = DATE;
         Mod(previous_color + 1; 4)

    Let me know if that works for you.


  6. David Zakary



    Thanks for your work on this

  7. Geoff Coffey



    Mod(GetAsNumber(DATE_FIELD), 5) = 0
    Mod(GetAsNumber(DATE_FIELD), 5) = 1
    Mod(GetAsNumber(DATE_FIELD), 5) = 2
    Mod(GetAsNumber(DATE_FIELD), 5) = 3
    Mod(GetAsNumber(DATE_FIELD), 5) = 4





  8. Dick

    and go straight to Conditional formatting from there.

  9. Dick

    err.. with an additional proviso for months ending odd

  10. Geoff Coffey




  11. Cesar Morales


  12. Geoff Coffey





  13. daniele



  14. Jean-Claude





Tell Us What You Think

* (will not be published)