multiple clauses

[MacSig]

Hello guys,

I feel quite silly to ask that but I can't figure out what is wrong with my script.

I'm defining a field and the calculation looks like:

Case (

Procedure::Stato = "E" and Procedure::Data Scadenza ≤ Get(DataCorrente) + 30; "In scadenza";

Procedure::Stato= "E" and Procedure::Data Scadenza < Get(DataCorrente); "Scadute";

Procedure::Stato= "E"; "Effettive";

Procedure::Stato= "O"; "Obsolete";

)

The first 2 cases don't work. How can I define multiple clauses?

MANY THANKS

[Vaughan]

The case statement is not logically consistent.

If Stato = "E" then should the result be "Effective" or "Scadute"?

[MacSig]

Thanks for your reply

When stato = "E" the value depends from the date: if date is in the past, if it within next 30 days or longer.

How can I handle those different situations?

[comment]

I don't see anything obviously wrong with your calculation. However, the reference to related fields is not clear, and that might be the source of the problem.

[MacSig]

Thanks for your reply.

What exactly is not clear?

[comment]

The context. We don't know where the calculation field is, and where the data is. Obviously they are not in the same table - but we know nothing about their relationship.

We also don't know what "the first 2 cases don't work" means. My guess would be that you're not getting the result you expect because the data being used is not what you think.

[MacSig]

The fields are in the same table.

The table has several fields and among them a status field that can be "E" or "O" and a date field. I have just added an other field called scope. It is a calculated field and I would like it gets its values as below:

IF STATUS IS "O" SCOPE MUST BE "OBSOLETE"

IF STATUS IS "E" AND DATE IS IN THE PAST SCOPE MUST BE "SCADUTE"

IF STATUS IS "E" AND DATE IS WITHIN NEXT 30 DAYS SCOPE MUST BE "IN SCADENZA"

IF STATUS IS "E" AND DATE IS AFTER NEXT 30 DAYS SCOPE MUST BE "EFFETTIVE"

When I say that they don't work I mean no value is calculated.

Hope now it is more clear.

Moreover I'm using the same calculation within a script in order to define the value for the records already present and they don't know there neither.

Thanks for your help, I appreciate it

[MacSig]

they have the table name because I have copied the code from the script instead the calculation (but it is exactly the same except the table name)

[comment]

First, you cannot set a calculation field in a script. You probably have a Text field set to auto-enter a calculated result. I suggest you change it into a real calculation field - then it will calculate automatically for all records.

You also have a problem in the formula itself. The Case() function returns the result of the FIRST test that evaluates as true. This means that the order of the tests matters. In your formula, you test first for:

Date ≤ Get (CurrentDate) + 30

Any date that is in the past also satisfies this condition. Your order of testing should be:

Case (

Status = "E" and Date < Get (CurrentDate) ; "Scadute" ;

Status = "E" and Date ≤ Get (CurrentDate) + 30 ; "In scadenza" ;

Status = "E" ; "Effettive" ;

Status = "O"; "Obsolete"

)

---

BTW, the calculation field - like any field that uses a Get() function, must be unstored.

Edited October 20, 2008 by Guest

[MacSig]

Thanks for your reply.

Right now I have a doubt about my calculation field.

Defining it as you say does it updated any time I open the DB?

I mean, let as say a record has status "E" and date "1/1/2009",

so the value is effettive. Does something change on January second?

thanks

[comment]

If the field is unstored, it will evaluate every time the window is refreshed. If it's stored, it will evaluate every time one of the referenced fields is modified.

[MacSig]

thanks

how can I make it unstored?

[comment]

In the field's Storage Options, select "Do not store calculation results..."