case calculation

[Dutchy]

I have two fields (status and score) that are connected together by a case calculation.

Meaning if a score is filled in of 100 the status will be O (order), is the score between 1-99 status will automatically be P (proforma). That I can get done. But when a score is zero that can mean three things; either status, M (missed), C (cancelled) or NO (No offer). Would it be possible to create a pop-up menu in which one can select one of the three statuses when a score of zero is filled in the score field? Hope someone can point me in the right direction.

Thanks, Dutchy

[Vaughan]

There must be some other criteria that decides what the status should be... include it in the database.

Otherwise, put a field in from which the zero status can be selected, and change the Case calc to include the zero-status if the score = 0.

[MarkWilson]

Attached is a quick sample file.

Conditional_Value_List.zip

[Dutchy]

Thanks. This conditional Value list works great. But now I am making it a bit more complicated by adding more steps to the case calculation. And then things don't seem to work proper anymore. Here's what I have:

Case(

Score < 0 or Score > 100 ; "Invalid Entry" ;

Score = 100 ; "O/F" ;

Score ≥ 1 and Score ≤ 99 ; " P/L" ;

Score =0 ; "M/C/NO";

IsEmpty (Score); "ALL")

But now I would like to add a second step that will overrule parts of the above case calculation:

Case (

Payment received < 100 and Score = 100; "O";

Payment received = 100 and Score = 100; "F";

${Total in Euro - Sales} > 0; "P";

${Total in Euro - Sales} ≤ 0; "E"; "")

How can I put these two cases together so that it works?

Maybe I am thinking to complicated. In short what I would like to do is to make sure that when case two is active that nobody can change a status to E while a score of 100 is filled in. I hope I am clear on what I would like to do???

Thanks for the help

[mr_vodka]

Keep in mind that the Case statement will evaluate in the order that you test for.

for example, if you have the following:

Case ( number > 10; "Over Ten";

number > 20; "Over Twenty"

)

will return "Over Ten" if number is lets say 75.

Case ( number > 20; "Over Twenty";

number > 10; "Over Ten"

)

will return "Over Twenty" if number is 75.

So if you want something to override another test then just make sure you have your order of test evaluation correct.