Case Statement?

[aball65]

Hello,

I need to set the value of a form field based on the value of two database table fields ([IMPACT].[value] and [PROBABILITY].[value]). Both value fields can contain number from 1 thru 5 so there are a total of 25 different conditions. Here's pseudo code for the first 5 conditions:

if [IMPACT].[value]=1 and [PROBABILITY].[value]=1 then form field ="GREEN"
if [IMPACT].[value]=1 and [PROBABILITY].[value]=2 then form field ="GREEN"
if [IMPACT].[value]=1 and [PROBABILITY].[value]=3 then form field="YELLOW"
if [IMPACT].[value]=1 and [PROBABILITY].[value]=4 then form field="YELLOW"
if [IMPACT].[value]=1 and [PROBABILITY].[value]=5 then form field = "RED"

Any suggestions on how best to implement this in a manner that is easily maintained (my client may change the color scheme on me)?

Thank You.

 

[pbaldy]

I would have a table with the values in it, which enables you to get the value any number of ways.

 

[gemma-the-husky]

as an alternative,

you could even find a way of taking a product of the two fields, and using that to get a graduated colour
eg red=0 green=0 blue=0 to 250 in 25 steps of 10

so high impact, high probablitiy is a darker blue, but low impact, low probability is a light blue.

so the density of the colour immediately indicates the answer without having to resort to a complex list of colors.

 

[aball65]

The product approach is a great idea. However, in some instances the same product maps to different colors, e.g.:

if [IMPACT].[value]=1 and [PROBABILITY].[value]=3 then form field="YELLOW"
if [IMPACT].[value]=3 and [PROBABILITY].[value]=1 then form field="GREEN"

Is there anyway to do this with an expression within the text box's control source attribute? I realize this will not be as easy to maintain as pbaldy's table approach. Just asking.

Thanks

 

[gemma-the-husky]

not within the control source, but within the current event

this wont be as effective with continuous forms though (as every row will change) , just single forms

 

[DCrake]

Have you considered using the RGB() function to generate a colour. as you need three elements you may need to muddle one but the Impact and probability factors could be applied to generate a colour.

David