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:

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

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:

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:

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

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