Alternating Color Based on Alternating Data

An interesting question popped up on the TechNet discussion list this evening:

>I have a list of records with dates, which often repeat from one record to the next. I want all the same dates to be one colour. Then the next date would be another color. The background color of the date field would alternate from one color to the other as the date changes.

I’ve never run in to this need before, but it sounds like something that would come in handy. It also proves to be just enough FileMaker challenge to be interesting. Here’s how to make it work.

>Updated June 6, 2008: Based on feedback from David Zakary, I have added a second example that supports more than two alternating colors.

## The Problem

First, here’s a sample of the kind of data we’re talking about:

Sample data with ranges of dates.

Notice that some dates are repeated from record to record. Here’s how we want the data to display:

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

As you can see, this technique makes it very easy to see how different sets of records go together.

## 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*.

If you’re not familiar with this function, it’s a special one. It lets you fetch the value of a field from any record in the found set, which is an unusual sort of thing to do. Using it is a breeze:

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:

A calculation field that shows if a field value is the same as or different from the previous record.

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.

So you need a way to refer to the color of the previous record, compare the dates, and decide if the color should change or not. But there’s no way to determine, from a calculation, the background color of a field in the previous record. Instead, we’ll modify our calculation field so it tells us which color to use. This way, we can look at the value in this field to detect which color is in use. For simplicity, the field will have either a `1` or a `0`, indicating one or the other color is appropriate.

(At this point I should take a break to reiterate that this technique is a bit mind-bending. Sorry.)

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

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

If(GetNthRecord ( DATE ; previous_record ) = DATE;
previous_color;
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.

The formula first sets a couple of variables just to improve readability:

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

The values of the calculation field, alternating between one and zero.

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:

Conditional formatting rules to format fields based on the _COLOR field’s value.


## More Colors

If you want to cycle between more than two colors, you can make two small modifications to the formula. Here’s an example that cycles through four color options (0, 1, 2, and 3):

Let(
[previous_record = Get(RecordNumber) – 1;
previous_color = If (previous_record = 0; 0; GetNthRecord(_COLOR; previous_record))];

If(GetNthRecord ( DATE ; previous_record ) = DATE;
previous_color;
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.

Once you have this formula in place, you just need to add more conditional formatting rules for 2, 3, and so forth.

Smokin’.

Leave a Comment

13 thoughts on “Alternating Color Based on Alternating Data

  1. Excellent lateral thinking and a very cool solution.

    Yet another reason why I love conditional formatting.

    Now if you had a scripted sort such that you stored the sort field name, you could make this work based on a variety of sort fields. Hmmm.

  2. An interesting solution. I had something similar come up before but didn’t have a good solution as it was pre-conditional formatting.

    This only works with two colours. What if you wanted to have each unique date (or other key data) to show up as a different colour? For in the above example you have 4 unique dates, that would mean 4 colours.

    Any thoughts on how to accomplish that?

    My first thought is find some way to count the number of unique dates in the found set, code them as you have and then colour them.

  3. @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)

    That will give you results that go 0, 1, 2, 3, 0, 1, 2, 3, etc… If you change the 4 as the second parameter to something else, you’ll get a different number of options in your cycle. Then you just need conditional formatting rules for each possible value.

    Geoff

  4. Tried the ‘mod’ version as you suggested. I’m getting question marks in the field as soon as I do.

  5. @david:

    Sorry. I should have tested first. The problem is that I was playing pretty foot-loose with my error checking because the “not” operator cleaned up any messes all by itself.

    When I check the color of the previous record, I don’t bother to make sure there is a previous record. This works fine in the 1/0/1/0 case, but the mod function isn’t happy with this.

    So here is a revised version that works properly:

    
    Let(
      [previous_record = Get(RecordNumber) - 1;
      previous_color = If (previous_record = 0, 0, GetNthRecord(_COLOR; previous_record))];
    
      If(GetNthRecord ( DATE ; previous_record ) = DATE;
         previous_color;
         Mod(previous_color + 1; 4)
      )
    )

    Let me know if that works for you.

    Geoff

  6. That works better. Works perfectly if the data is sorted by the Date field. Works ok if unsorted.

    Ideally (this is based on what my client wanted) is that if there were duplicated, but non-contiguous, dates they would show up the same colour. In this version they take on the next value in the custom formatting series.

    Thanks for your work on this

  7. @david:

    Yes, if the data isn’t sorted it “works” in that contiguous ranges are still colored properly, but it doesn’t do what you describe. If you want that behavior, you might consider using a conditional formatting approach based on the date itself. For instance, imagine these 5 conditional formatting rules:

    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

    This would have the problem that two side-by-side dates that are an even multiple of 5 days apart would be the same color, which might be problematic. But I’m not sure how possible it would be to fix that problem and still keep same-color-for-same-date behavior.

    You could also color code based on the number of days in the past, which might be cool. One color for 0 to 3 days, another for 4 to 10, etc… Something like that.

    Anyway, thanks to Todd for inspiring this. It is a very cool idea to color code rows based on data similarities.

    Geoff

  8. For 2 colours why not just do a calc on odd or even day numbers
    and go straight to Conditional formatting from there.

  9. @dick:

    That will work well if you’re certain every sequential day will be represented in the list. But it breaks down if you have gaps. For instance, suppose you have 3 records on January 3rd, none for the 4th, and then a few more for the 5th. In that case, the two sets would be side-by-side and of the same color.

    Geoff

  10. You can also use the date day names to display corresponding colors either through an unstored calc field that evaluates global graphic repeating field holding different colors, or if using FileMaker 9 or above with Conditional Formatting and a background color for each different date’s day.

  11. Cesar:

    On using a global and calc in versions before 9, excellent idea. That would work great.

    As for coloring simply based on the day name, in some cases that would work, but it is a slightly different result. See my previous (6-19-2008) comment.

    Geoff

  12. Geoff:

    I used your powerful formula in many situations but now I discovered that if the found set is in the order of around 400 records, then starting from the record number 178 the field result became a “?” instead of 0 or 1, and starting from the record 180 you can’t even enter the tab into the field. Any idea?