A Very Nice Way to Format a Sorted Portal Using Conditional Formatting

In this article I will demonstrate a very nice way to format a portal using conditional formatting. This method is suitable for portals whose underlying relationship is sorted by a grouping/category field - similar to if it were like a sub-summary report. The easiest way to explain it is to just get stuck in with an example, so here we go....

In this example I will be using a very simple Customers table. In this table, there are 3 main fields:

1. Customer ID - the primary key
2. Customer Name
3. €Region

Each customer has a specific region, so it stands that a region may contain multiple customers. On our layout, we have a portal showing all customers. The relationship this portal is based upon, is sorted by Region, so that in our portal, all customers who belong to the same region, will appear together as shown:

Now, this could be the end of the story - the records are sorted as you wish, and you can clearly see the region for each customer, but is it really that nice visually? The region name is appearing multiple times in each row, which is quite distracting, and also makes the portal feel cluttered. Also, is there any reason for the region to appear in each row? What if we could have the region appear in the first row for those customers only? Indeed, this is possible with a little conditional formatting.

The Conditional Formatting

The conditional formatting calculation I am going to attach to the Region field in the portal is as follows:

Let ([
This = Customers Filter::Region ;
Record = Customers Filter::zRecordNumber ;
Previous = GetNthRecord ( Customers Filter::Region ; Record - 1 )
];
This = Previous
)

So, what is going on here? The calculation is broken down into simpler chunks using a Let statement to make it easier to follow:

This = Customers Filter::Region

The variable "this" is simply set to the region of the portal row this conditional formatting is evaluating in. Note that each portal row calculates it's conditional formatting independently of the others, so when I reference the Region, it is evaluating the region for its own portal row only.

Record = Customers Filter::zRecordNumber

Perhaps the most complex step in the calculation. zRecordNumber is a special unstored calculation field that is required on the Customers table to achieve this desired effect. The calculation is set to Get ( RecordNumber ). Why? The reason will hopefully become clear soon.

Previous = GetNthRecord ( Customers Filter::Region ; Record - 1 )

So, this is the step that ties everything together. This variable will be set to the Region of the previous portal row to the one currently being evaluated, or in other words, the portal row directly above the one evaluating. The GetNthRecord function is able to return the value from a record through a relationship, where you define the exact record number to obtain.

Going back to the second step now - zRecordNumber is the way in which our conditional formatting calculation is able to obtain it's own record number in the relationship. So, the 10th record in the relationship (ie the 10th portal row) is able to know that it is #10 by referencing this calculation, the second row knows it is #2, and so on. This is the only way I could find to obtain this information for each portal row. Sadly, there is no Get ( PortalRowNumber ) function, and the closest one - Get ( ActivePortalRowNumber ) - only works if a portal row is active (funny that!).

When Get ( RecordNumber ) is evaluated through a relationship, the result is the record number of the portal row being evaluated. The function has to be in an unstored calculation on the table - simply referencing "Get ( RecordNumber )" in the conditional formatting calculation will always return 1 for each row - because in this situation it is obtaining the record number of the parent record, which is the same for all portal rows.

Back to Step:

So, with that known, the conditional formatting calculation now knows it's own record number. This is then used in conjunction with GetNthRecord to obtain the previous records Region.

The Final Step

The final step in the calculation is the actual conditional test to determine whether the formatting is applied or not. Now, remember that our aim here is to show the region on the first portal row for that regional group. Taking the opposite of this - we wish to HIDE the region on all rows which are not the first row for that region.

We know the region of the current row, and we know the region of the previous row. Thus, if the current rows region is the same as the previous, then the current row cannot possibly be the first row for that region, and as a result we wish to hide it. Hiding it is done by setting the text colour of the conditional formatting to white.

If in fact it was the first portal row for a given region, then the previous portal row will be different, thus the conditional formatting test returns false, and no formatting is applied, leaving the region visible.

What About the First Portal Row

The first portal row is handled gracefully, as there is no previous portal row, so the region in the first portal row will always be different from the previous. GetNthRecord of the 0 record will return blank.

Some Limitations:

It should be noted that there are some limitations to this method. First, it is the relationship that must be sorted here, not the portal itself. The reason is that both the zRecordNumber calculation, and GetNthRecord function, all work at the relationship level, not the portal level.

Second, this method will not work with portal filtering in FileMaker 11. The reason again is that filtering occurs at the portal level, while the conditional formatting is working at the relationship level. If you wished to use filtering, you would have to filter the relationship instead.

The End Result

Putting it all together you end up with something like this:

Some Further Formatting to Make it Even Nicer

The above is nice, but it is still lacking something. What it is lacking is some kind of separation between the regions. One region just follows on to the next without any kind of dividing line. Fortunately, we can make a slight adaptation of the conditional formatting we already have used, to produce a separating line.

First things first - you cannot conditionally format a line object. For this reason, we need to somehow hide the line under some circumstances. This is exactly the same as what we did for the region field.

In this case, we hide the line by overlaying a conditionally formatted object (a button/text label) , which fills itself the portal background colour:

The picture has been zoomed & exaggerated to illustrate the effect. The green box is a button with no action attached. It sits in front of a grey line. Both objects will be in the portal. The best place to put them is at the top of the first row as close to the top as you can get. Make sure the button has 0 line width (it has one here just so you can see it). The minimum height of the button you can achieve is 4 pixels so make it this.

The conditional formatting calculation on this is very similar to that of the region field, with one slight modification:

Let ([
This = Customers Filter::Region ;
Record = Customers Filter::zRecordNumber ;
Last = GetNthRecord ( Customers Filter::Region ; Record - 1 )
];
This = Last or Customers Filter::zRecordNumber = 1
)

The difference here is the last line. We wish to hide the line on the first portal row as it is not required there, so we test if the record number is 1, and if so, we hide the line.

Putting it all Together

Here is what you end up with when all the pieces are put together:

Looks very tidy and nice if I do say so myself! Each regional grouping is separated by a line. No need for region to appear on every line here, and it is clearly obvious what customers belong to what region.

Taking it Even Further, More Possibilities

This concludes the article, but if you were wanting to take it further, there are more possibilities to explore:

Modifying the conditional formatting, so that the regional name is shown every Xth row for that region. This would be useful for example if a region had a large number of customers. If this was the case, then as the user scrolled the portal, they may no longer be able to see the region name, and thus not know what region the customer belongs to. If this were the case, then putting the region name on every 10th row of that region would solve this problem.

Adapting for use in List-view on a layout. This could be achieved fairly easily given the fact both GetNthRecord, and Get ( RecordNumber ) will operate quite nicely on a found set of records as well as through relationships.

Example File

Please find attached an example file. This file was used for all the screenshots in this article, and is fully commented to help you fully understand what is going on in the file.

Download Example File