A Very Nice Way to Format a Sorted Portal Using Conditional Formatting
By Daniel Wood, 15 December 2010
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:
- Customer ID - the primary key
- Customer Name
- 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.
Something to say? Post a comment...
Comments
Daniel Wood 16/09/2019 12:37pm (5 years ago)
hi Laura,
That's pretty odd! I know that I have had some issues with this technique working correctly when it comes to first/last rows in recent versions of FileMaker. I just took my demo file and converted it to use in 18. I managed to get it to work by taking the CF calculation and putting it on the hide condition for the line, and then removing the old CF object. Having the line at the top of the portal row seemed to make it work fine, and it wasn't calculating true for the first row. There is a condition in the calculation to say never show (thus always hide) if the record number is 1, which should be the first row.
You can download my modified version for comparison here if you wish:
https://share.teamdf.com/daniel/FormatPortalModified.zip
Laura Betz 13/09/2019 8:27am (5 years ago)
Interestingly, I tried this with the Hide feature and for some reason it was always calculating as true for the first record.
But it worked with Conditional Formatting.
Daniel Wood 01/06/2018 7:04am (7 years ago)
Thanks Damien, was good to catch up with you @ Dev Day too. Obviously this technique is a bit easier now with hide conditions instead of masking the lines using conditional formatting :)
Damien Kelly 31/05/2018 12:19pm (7 years ago)
I just stumbled on this, trying to format a portal on an Orders table, showing Invoices and Payments, works a treat, thanks heaps!
Daniel Wood 13/05/2016 9:40am (9 years ago)
hi Joshua, thanks for the comment. If you are using this calculation both on the conditional formatting to hide the line, and also on the conditional formatting to hide the region (or in your case category) then it won't work.
What you're trying to achieve is to have the conditional formatting calculation evaluate when the portal is rendered starting with the first row and evaluating for each subsequent row. This does actually work quite nicely but only if you have the calculation once in the portal, having it in 2 or more places within the portal effectively breaks it.
This is likely due to one of those calculations evaluating on all rows, before moving onto the second object at which point it breaks.
I'd suggest using different global variable names for each of the calculations for hiding the category name, and for the line, so that they do not interfere with one another.
When I tried just with your calculation hiding the line and not the name, it worked perfectly, and vice versa.
One thing to note too however with your version is that you may notice clicking into a portal row reveals both the line and the name because the calcs are re-evaluating incorrectly when clicking in a portal row. I thought that switching to hide condition would fix this, but actually while it renders fine upon first load, clicking into the portal when using hide condition, causes everything to hide and not come back!
I think you have a cool idea here removing the need for a couple of fields in your calculation, but I think for reliability reasons it might pay to use the record number as per the example file to ensure that you don't run into any unusual issues with this technique.
Joshua 13/05/2016 3:12am (9 years ago)
Hi Thanks for your article!
I am trying to achieve the same thing, but with Global Variables.
Let([
This = Category::Name ;
$$Prev = $$NextPrev ;
$$NextPrev = This
] ;
$$Prev = This
)
I can verify that "$$Prev" and "This" values are the same separately with the following hard coded values, and the field hides.
a) $$Prev = "some value"
b) This = "some value"
But it doesn't seem to "equate" once I test $$Prev = This
Any comments?
Grace Stanat 06/04/2013 4:55am (12 years ago)
okay... now i have a question. the following doesn't work, but why not?
as the condition test, simply using the following:
<pre>
Customers Filter::Region = GetNthRecord ( Customers Filter::Region ; Get (RecordNumber) - 1) )
</pre>
Grace Stanat 05/04/2013 1:58pm (12 years ago)
doink. figured it out. but if you read this, thanks for doing so. all good now. thanks for the awesome tip!!
Grace Stanat 05/04/2013 1:51pm (12 years ago)
this is EXACTLY what i'm trying to do, but i'm not having success doing it. i think my problem is that Get (RecordNumber) doesn't seem to be evaluating correctly for me. i've put my zRecordNumber field in the portal to see what it is, but it seems to be stuck on the order creation order. i.e., it doesn't change no matter what found set or sort order i use.
so... sorry for the newbie question, but how can i make Get (RecordNumber) evaluate? i'm using it as a (non-global) UNSTORED calculation field. i've tried all variations of indexing. it doesn't even work on the test layout i've created for customer::filter. so i'm convinced i'm missing something simple. any tips? :)
Priyabrata 13/02/2013 8:34pm (12 years ago)
A nice way to show a separation line in a Portal using a button.
Paul Spafford 31/12/2010 7:12am (14 years ago)
Very clever way of having a conditional line on the portal. Nice article!
Daniel A. Shockley 31/12/2010 4:12am (14 years ago)
Fields are often useful for this sort of thing (as long as you either use a field that always has a value, or know that certain behaviors change when it is empty, and intend that - e.g. sliding when printing).
In a presentation I recently gave, I had an aside showing a technique that uses a FileMaker field to draw vertical lines in a report to work around a printing issue with lines. Basically, there is a situation where FileMaker calculates that there is room enough to show a final record row on a printing layout, but throws out the vertical line(s) in that last row. Using a field that is only 1 pixel wide avoids the problem. The presentation itself was on using a dynamic repeating calculation field to create a reusable to make on-the-fly calculations for a report, rather than creating a bunch of single-purpose calculation fields.
http://danshockley.com/2010/10/presentation-dynamic-repeating-calculation-reports/
The report layout has a Layout Mode-only note that explains the vertical lines trick.
Daniel A. Shockley 31/12/2010 4:12am (14 years ago)
The comment sort order here is confusing me. Somehow our newer comments are appearing above my original comment (probably it is now #5 - begins with "Nice Article"). Is something wrong?
Daniel Wood 31/12/2010 4:12am (14 years ago)
Hi Daniel. Thank you for the comments. You are right that the button overlay can be an issue, and one that usually catches me out when I go to double click it! I have found that inserting a single space character in the button will at least get around the issue of the button being deleted when entering into it (I really should have put that in the example file).
Your technique of using a field @ 1/2 px to achieve a line effect is a good one, I like it! It would certainly cut down on the need for 2 objects as opposed to 1. Originally in the example file I tried to use just the button, but could not reduce its height below 4 px, but a 1px font sized field would resolve that issue nicely, thanks!
Daniel A. Shockley 31/12/2010 4:12am (14 years ago)
Nice article! I've found that you can often use fields where lines are hard to manage. For example, you could use a field that will always have a value for existing records (say, the zRecordNumber field here), format its background and text to your desired shade of gray, make the font size = 1, and then make the field box 1 or 2 pixels high. Then, use your conditional formatting to make the background and text white when you don't want the line. This avoids the need for a button over a FileMaker line.
Another interesting thing, bringing up a potentially destructive FileMaker behavior noted at a recent NYFMP meeting: Your formatted button has no text. So, if you then double-click on it, then leave it, it is deleted. Basically, FileMaker treats buttons as special text boxes. And, remember that if you have a text box on a layout, clear out all text, then leave that box (hit Enter or Escape, or just click away), the text box disappears (is deleted). Sadly, buttons do the same thing. And, there is no Undo, since you didn't technically edit anything. That happened to me when I was looking at your example, and confused me for a second. I double-clicked the button to check it out, left it, went to move it, and then noticed it was gone.
Thought I'd mention that FileMaker behavior, since it can be surprising when it happens.
Thanks for a good article!
No one has commented on this page yet.
RSS feed for comments on this page | RSS feed for all comments