Colour coding records

[efen]

Can someone please advise me on the calculation I need?

I have a field Hits (number) which can be empty, zero or any number from 1 to 20; a calculation field HitsStatus (result Container); and a repeating container field HitsColour.

HitsStatus 'colour codes' records depending on the number entered by the user in Hits.

At present HitsStatus is defined as

Case [Hits=0, GetRepetition [HitsColour,1],

[Hits<=5, GetRepetition [HitsColour,2],

[Hits<=10, GetRepetition [HitsColour,3],

[Hits<=15, GetRepetition [HitsColour,4],

[Hits<=20, GetRepetition [HitsColour,5]]

which shows a different colour as intended depending on the number of 'Hits'--- except that there is no difference in the colour shown in records when the field is empty in one and there is a zero in another.

How does this calculation need amending to show this? I have tried using IsEmpty but can't get the syntax correct.

Thanks in advance.

efen

[Lee Smith]

Hi efen,

Change it to read

Case(

Hits = 1, GetRepetition(HitsColour, 1),

Hits = 2, GetRepetition(HitsColour, 2),

Hits = 3,GetRepetition(HitsColour, 3),

Hits = 4,GetRepetition(HitsColour, 4),

Hits = 5,GetRepetition(HitsColour, 5))

HTH

Lee

[efen]

Hi Lee

This only gives a similar result to my calculation -- it doesn't differentiate between a zero entered in the Hits field or a blank field.

efen

[BobWeaver]

Try this:

Case (IsEmpty(Hits),GetRepetition (HitsColour,1),

Hits=0, GetRepetition (HitsColour,2),

Hits<=5, GetRepetition (HitsColour,3),

Hits<=10, GetRepetition (HitsColour,4),

Hits<=15, GetRepetition (HitsColour,5),

Hits<=20, GetRepetition (HitsColour,6))

[efen]

Thanks Bob

Works great.

efen