What is wrong with this formula?

[innodat]

Aside from the fact that it is not very elegant... it's not working. There must be a major syntax error which I'm not seeing. Any ideas?

Status =

Case (

not (IsEmpty(Invitation Email Sent Date)); "Invitation Sent";

not (IsEmpty(Invitation Email Sent Date)) and (Invitation Response = "Received"); "Invitation Accepted";

not (IsEmpty(Invitation Email Sent Date)) and (Invitation Response = "No Response"); "Invitation Declined";

not (IsEmpty(Invitation Email Sent Date)) and (Invitation Response = "Not Received"); "No Invitation Response";

"Pending"

)

[comment]

It's hard to tell what's not working without knowing what the intended result is.

However, this point stands out: when Invitation Email Sent Date is NOT empty, the first test evaluates as true, and the result "Invitation Sent" is returned - without checking the other tests.

[Raybaudi]

Try:

Case (

Invitation Email Sent Date ;

Case (

Invitation Response = "Received" ; "Invitation Accepted" ;

Invitation Response = "No Response" ; "Invitation Declined" ;

Invitation Response = "Not Received" ; "No Invitation Response" ;

"Invitation Sent"

);

"Pending"

)

[innodat]

Yes, that works. Didn't know you couldn't simply "stack" conditions, but you have to repeat the "case(" string.

Thank you!

[innodat]

Of course, I only posted the beginning of the entire formula... there's about 20 conditions which need to aggregate. Isn't there a better way of doing this than with simple "case" statements???

Case (

not (IsEmpty(Invitation Email Sent Date)); "Invitation Sent";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received"; "Invitation Accepted";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "No Response"; "Invitation Declined";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Not Received"; "No Invitation Response";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)); "Conversing";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received"; "Application Received";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Not Received"; "Application Not Received";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "No Response"; "No Application Response";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)); "Synopsis Created";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes"; "Granted";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "no"; "Grant Declined";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Confirmation Email Sent Date)); "Confirmation Email Sent";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Confirmation Letter Container)); "Confirmation Letter Sent";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Checkform Container)); "Check Form Received";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Checkform Container)) and Check Sent = "yes"; "Check Sent";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Checkform Container)) and Check Sent = "yes" and not (IsEmpty(Ongoing Project Discussion)); "Project Discussion";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Checkform Container)) and Check Sent = "yes" and not (IsEmpty(Ongoing Project Discussion)) and not (IsEmpty(Final Report Reminder 1 Date)); "FN Report Not Received";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Checkform Container)) and Check Sent = "yes" and not (IsEmpty(Ongoing Project Discussion)) and Final Report Received = "Received"; "FN Report No Response";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Checkform Container)) and Check Sent = "yes" and not (IsEmpty(Ongoing Project Discussion)) and Final Report Received = "Not Received"; "FN Report Not Received";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Checkform Container)) and Check Sent = "yes" and not (IsEmpty(Ongoing Project Discussion)) and not(IsEmpty(Extension 1 New Deadline)); "Extension 1";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Checkform Container)) and Check Sent = "yes" and not (IsEmpty(Ongoing Project Discussion)) and not(IsEmpty(Extension 1 New Deadline)) and not(IsEmpty(Extension 2 New Deadline)); "Extension 2";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Checkform Container)) and Check Sent = "yes" and not (IsEmpty(Ongoing Project Discussion)) and not(IsEmpty(Extension 1 New Deadline)) and not(IsEmpty(Extension 2 New Deadline)) and not(IsEmpty(Extension 3 New Deadline)); "Extension 3";

not (IsEmpty(Invitation Email Sent Date)) and Invitation Response = "Received" and not (IsEmpty(Application Conversation Notes)) and Application Received = "Received" and not (IsEmpty(Project Synopsis File Container)) and Panel Decision = "yes" and not (IsEmpty(Checkform Container)) and Check Sent = "yes" and not (IsEmpty(Ongoing Project Discussion)) and not(IsEmpty(Extension 1 New Deadline)) and not(IsEmpty(Extension 2 New Deadline)) and not(IsEmpty(Extension 3 New Deadline)) and not(IsEmpty(Extension 4 New Deadline)); "Extension 4";

)

[comment]

Didn't know you couldn't simply "stack" conditions

You can - but the order is important:

Case (

not Invitation Email Sent Date ; "Pending" ;

Invitation Response = "Received" ; "Invitation Accepted" ;

Invitation Response = "No Response" ; "Invitation Declined" ;

Invitation Response = "Not Received" ; "No Invitation Response" ;

"Invitation Sent"

)

[innodat]

Comment, it's about one record with say 20 fields which, as they become populated in certain way (sometimes "not empty" is sufficient, sometimes a criteria such as "yes" or "no" is required) update a status field:

Invitation Email Sent => Invitation Sent

Invitation Response "yes" => Invitation Accepted

Invitation Response "no" => Invitation Declined

Invitation Response "no response" => No Response

Application Conversation Notes => Conversing

Application Received "yes" => Application Received

Application Received "no" Application Not Received

Application Received "no response" => No Response

Project Synopsis (attached) => Synopsis Created

Award Panel Desicion "yes" => Granted

Award Panel Decision "no" => Declined

Email informing application of decision sent => Confirmation Email Sent

Letter informing application of decision sent (attached) => Confirmation Letter Sent

Check form received from artist (attached) => Check Form Received

Check sent to Artist => Check Sent

Project Discussion => Project Discussion

First Final Report Reminder sent => FN Report Not Received

Final Report Received "yes" => FN Report Received

Final Report Received "no" => FN Report Not Received

New Deadline Date 1 => Extension 1

New Deadline Date 2 => Extension 2

New Deadline Date 3=> Extension 3

New Deadline Date 4 => Extension 4

[comment]

Isn't there a better way of doing this than with simple "case" statements?

Yes, there is: combine the test results into a "binary number" using something like -

Test1 + 2*Test2 + 4*Test3 + 8*Test4 ...

Then use Choose() or GetValue() to pick the correct response for each possible combination. Preparing a truth table can be really helpful here.

---

NOTE:

The example above refers to tests that are independent of each other; with some tests applying only if another test returns true or false, the calculation may be different.

Edited April 8, 2010 by Guest

[innodat]

I've been reading a lot about truth tables, but I'm not following all the way. Specially the translation of the table into a workable calculation using Choose() and GetValue().

Could you maybe post an example for the small part of the formula we've been working with?

Case (

not Invitation Email Sent Date ; "Pending" ;

Invitation Response = "Received" ; "Invitation Accepted" ;

Invitation Response = "No Response" ; "Invitation Declined" ;

Invitation Response = "Not Received" ; "No Invitation Response" ;

"Invitation Sent"

)

Many thanks!!

[LaRetta]

I would be concerned about using a calculation which combines so many fields (20) in the mix. You appear to have a house of cards whereas all prior fields must be complete before testing the next. In fact, your existing calculation has no contingency in case it fails along the way.

So possibly, if you 1) use validations on your fields so that a User can't complete a field without making sure the prior sequence fields are completed (which you should have anyway), then you can reverse the order and your Status field will be easier. Also, if you change any of this (and your calculation is complex like you have it, even if improved, you will get back into a twist.

Case (

Extension 4 New Deadline ; “Extension 4” ;

Extension 3 New Deadline ; “Extension 3” ;

Extension 2 New Deadline ; “Extension 2” ;

Extension 1 New Deadline ; “Extension 1” ;

Final Report Received = "Not Received"; "FN Report Not Received";

Final Report Received = "Received"; "FN Report No Response";

... and so forth.

Otherwise, I would tend to use an Actions table with an ActionDate and look at the last record in the relationship and determine the text result from that. Just a different approach, because your current calculation would drive me absolutely nuts if it needed to be changed at all.

UPDATE: This appears to be a schedule where Item 1 must be done before Item 2 and so forth. If so, there is no need to check prior fields, only the last one with a value. A truth table would help a lot!

Edited April 11, 2010 by Guest
Added update

[innodat]

I'm not sure if the user wouldn't want to skip steps under certain occasions... but assuming this is not a requirement, your suggestion is interesting.

I could use the same structure to validate field entry in the sequence, correct?

Example: A script trigger (OnObjectEnter) for Extension 1 would verify

Case(

IsValid(Final Report Received) and

... and so forth

)

For Extension 2 it would be

Case(

IsValid(Extension 1) and

IsValid(Final Report Received) and

... and so forth

)

The problem with an actions able (if I understand your suggestion), would be that if the user goes back and "fixes" something on the record, that would be the last action recorded and the field would revert back to that - despite the fact that fields succeeding it in the sequence have already been filled out.

[comment]

Could you maybe post an example for the small part of the formula we've been working with?

I am not sure this is the best showcase, because (a) the first test is overriding, and (: the other three tests are mutually exclusive. But for the sake of example:

Let ( [

a = not Invitation Email Sent Date ;

b = Invitation Response = "Received" ;

c = Invitation Response = "No Response" ;

d = Invitation Response = "Not Received" ;

index = a * ( 1 + b + 2 * c + 3 * d )

] ;

Choose ( index ;

"Pending" ;

"Invitation Sent" ;

"Invitation Accepted" ;

"Invitation Declined" ;

"No Invitation Response"

)

)

[innodat]

Go it - thank you. I will work on a truth table, and see how far it can take me. I might have to simplify the conditions also.