if column C3 in your example is always going to be represented by an incremental grouped number, then you can just conditionally format your field with the following test condition equation:
MOD ( table::C3 ; 2 ) = 0 //number is even - change fill color to Color 2
Then just have your regular fill color be Color 1
If there is a common value in a field, you can use conditional formatting to change the fill color on the fields being displayed.
For this the formula might be table::field = "something" set the fill color.
You can have as many formulas in the list you want. Keep in mind, that all formula's in the list for the field will be evaluated and that the last true formula will control the fill color. So the order that the formula's are listed can make a difference.
Unfortunately, I have abstracted the data. C1 is a date, C2 text that will be static per "group" and C3 is another date...
My current thought is:
1) reflexive relationship with a calc field to equal one only on the first occurence of a group member
2) a summary field of "running total" to caclulate a MOD/2 to base conditional formating on
3) a summary field returning MAX of the summary field above to catch the non-related.
Seems like a lot of work, and may have performance issues IF I can get my head around it to make it work...
Any other ideas?
You could use a calc field, mynumber, with GetNthRecord to compare the current record's value with the previous record's value:
//first record always returns a 1
Get(RecordNumber) = 1 ; 1 ;
//subsequent records compare themselves to the previous record
ColA = GetNthRecord ( ColA ; Get(RecordNumber) - 1 ) ; GetNthRecord ( mynumber ; Get(RecordNumber) - 1 ) ;
not (GetNthRecord ( mynumber ; Get(RecordNumber) - 1 ) )
This returns a zero or a 1, and you can then use that in a conditional formatting calc to color a container field
This is for a list view, but can easily be modified for a portal by using Get(ActivePortalRowNumber) instead of Get(RecordNumber)
alt rows.fmp12.zip 10.0 K
Beware that inside portals GetNthRecord() goes pear-shaped. And Get ( ActivePortalRowNumber ) only works when there's an active portal row. Get (RecordNumber ) would still be the function to use in a portal, I think, if it weren't for the GetNth issue.
The OP could probably brew something up using a global variable that grabs the unique list of related group values then compares the current value to the position of all related ones. Check out custom functions with names like UniqueValues() and ValuePosition().
Do you have more than one portal? Is it possible to just use list view, and use the header and footer as the base of the layout...and the body as the portal-like list of records?
Thanks everyone for your help! I managed to acomplish this without going "reflexive" using some of the input from above. I took advantage of the fact that C1=C2 only once per group. I set up a calculated field to C1=C2 and a summary field (running total) for Count of the calculated field. A final calculation used Mod/2 on the summary and this tied to the conditional format. This works in the case I have it sorted properly (which was already a pre-requesite).
A universal refeinment of that method ( that does not rely on C1=C2 once per group) is to add a calc field "one" -- =1 in the raleted table and a summary running total of that field, resarting on sort group value changes based on the same sort, and mod /2 of that.
That will result in a field that alternates 1 and 0 values - but the first row in each sort group will be 1