Coloring portal records

[David Jondreau]

I have a calendar layout with a portal that shows all Jobs for a Day. There are multiple Jobs per Order. The portal is sorted by start time, but different Jobs on the same order may start at different times. It would be unusual to have more than 10 different Orders (and 30 Jobs) per day.

I'm trying to come up with a way to assign a highlight color to each Job of the same Order. Having the same color to an Order over multiple days would be ideal, maybe required. An Order would rarely have Jobs that cover a span longer than 2 weeks. It's unlikely more than 20 Orders would ever overlap.

I imagine it would require a deconstruction of the Order id and a custom function involving RGB(), but I'm stumped on where to begin.

Has anyone tried something similar?

Edited June 11, 2008 by Guest

[comment]

You could assign a color to each order in the Orders table, and use it to calculate the background color in the Jobs table. I don't see where a custom function would be needed for this - except perhaps to determine the color?

Another option would be to use conditional formating, but this would repeat the color every n orders - n being the number of conditions in the formating.

[David Jondreau]

I'm trying to figure out how to assign a color to an Order ID. There's x number of useful colors. How do I parse an Order ID (5 digit, currently about 2000 records) into a color? How do I parse it into a color that is not (or is unlikely to be) the same as the color of another Order that may have Jobs on the same day? The number of combinations is bound to be greater than x. What is x?

Conditional formatting may work, since prescience isn't needed, but I don't think it'll keep colors consistent over multiple days and I'm not sure what that conditional formatting would look like.

[comment]

Let's say you have defined n colors and numbered them from 1 to n.

Mod ( OrderID - 1 ; n ) + 1

will assign the colors to orders cyclically.

The same thing, albeit with more work, can be done with conditional formatting directly in a portal to Jobs:

Formula is: Mod ( Jobs::OrderID - 1 ; n ) = 0 | Fill Color 1

Formula is: Mod ( Jobs::OrderID - 1 ; n ) = 1 | Fill Color 2

Formula is: Mod ( Jobs::OrderID - 1 ; n ) = 2 | Fill Color 3

...

Formula is: Mod ( Jobs::OrderID - 1 ; n ) = n-1 | Fill Color n

In both methods, the colors are assigned to orders permanently, therefore the number of required colors is the number of new orders that can be created during the "useful lifetime" of any order.

[comment]

I know I have done this before, but I can't find it - someone asked about coloring every n-th row.

Anyway, one way is to type "@@" into the portal and format it conditionally using Mod ( Self ; 2 ). I didn't get the part about hiding some text.

[comment]

I swear there was a question here... - now what?

[mr_vodka]

Sorry I had deleted my post... (I thought in time.)

The orig question was I wanted to see how you would handle conditional formatting with portals with alternating colors... I have a work around method but I was curious to see how you would handle it.

[comment]

I knew I had done this before:

http://www.fmforums.com/forum/showpost.php?post/274645/

Keywords for next time:

conditional formatting alternate portal row

[mr_vodka]

Yes but what about other objects such as fields etc? How to conditionally format them. There are certain object that I make disappear/appear with conditional formatting usually by matching the background color... However, with alternating portal rows, this woouldnt work.

[comment]

OK, so you want the big gun:

AlternatePortal2.fp7.zip

[mr_vodka]

Yes tank guns!

Nice.