Solved

# FM - How to color every other row in a Summary Report with Totals Only

Posted on 2011-10-24
754 Views
I can change the color of the background for every other row in a complete list or in a summary report with a Body easily enough, but how would you change the row background color for a summary report with a Trailing Summary only and reflects a found set, not the entire set of records?

Since it is a found set, would I have to use a script to number the records and then do a conditonal formatting formula on a field that is behind the entire row of fields in the Trailing Summary?  Thanks.
0
Question by:rvfowler2

LVL 24

Expert Comment

Conditional formatting is probably simplest. You just use a simple calculation that checks to see if the record number is odd:

mod( Get ( RecordNumber ) ; 2 ) = 1
0

LVL 24

Expert Comment

Just to be clear, the Mod function returns the remainder after number is divided by divisor.

Mod( number ; divisor )

In this case, the divisor is simply 2, so if the record number is even the result is zero, if odd, then it's 1.
0

LVL 2

Author Comment

Great idea, unfortumately, the report is not sorted in the same order as record numbers.  Since we only add properties a few times a year, I'm going to write a script that renumbers them in order of PropCode in a field called "BackgroundColor".
0

LVL 24

Expert Comment

Record number is independent of any sort order. It's simply the number of the record in current found set regardless of how it's sorted.
0

LVL 2

Author Comment

Didn't see the obvious.  Didn't work because it is a found set and doesn't include all the prop records.  Going to add a script that numbers fields off the the side of the report.
0

LVL 2

Author Comment

The above didn't work either because I'm doing totals, and how do I enter a new rec# for each total row?
0

LVL 24

Expert Comment

Randy - can you clarify a bit more? It sound like you've got a report that has sub-summaries and you're saying the alternate background is somehow not working because of the sub-summaries?
0

LVL 2

Author Comment

Yes, should have used the more precise term, Subsummaries.  It is a report without a Body; so it only shows totals/rows for each Prop Code and thus of course it is sorted by PropCode.  My problem is that FM does not allow a setting for alternate background color for the Subsummary Part, only the Body Part.
0

LVL 24

Accepted Solution

So, the clarification is this instance is that you are counting sub-summaries, not records, and need a way to determine whether a sub-summary is odd or even. One way would be a looping scripts. Sort all records by Property, run a loop that captures the first Property value and marks the first record in that sub-group with 1, the first record in the next sub-group with 2, etc. Then reference the flag field in the conditional formula. Since calculations in a sub-summary always reference the first record in the sub-summary group, it should work.

Alternately, if ALL Properties will always appear in the report and you have the Properties table that we talked about earlier, you could just number the properties in that Properties table and then reference that for the Conditional formatting. However, if Properties may or may not appear then you need to loop through. There may be a way to calculate it but I can't think if off the top of my head...
0

LVL 2

Author Comment

Will, almost worked, but I found that the totals did not seem to reference the first record consistently, thus I didn't have consistent coloring of every other record.  However, an easy modification is to number every record in the found set as you see in the script below, AND IT WORKED!  Thanks so much.
0

LVL 2

Assisted Solution

Though just to clarify, I numbered the records on my Detail report which did have a Body, and, of course, the script below numbers the records for the same property with the same number.  Also, for ease, I created two fields:  1) BackColorNum where the numbers get filled in, and 2) BackColor which is the field I set the conditional formatting on and place it behind all the other fields on the row.  This way the number in the BackColorNum doesn't show up on your row, though I'm sure there is some way to not show it via conditional formatting, but I took the easy route using the empty field, BackColor.
--RowCount.JPG
0

LVL 2

Author Closing Comment

Need to number every row and probably less room for error that way anyway.
0

## Featured Post

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…