Help with Case Calculation to determine Turnaround Time?

[Dana G]

I have a database where we document 4 specific dates realated to each request that we receive from a contractor.

The fields are:

Date Received - (this is the date we receive it)

Date to Consultant - (this is the date that we send it to the consultant)

Date rec'd consultant - (this is the date that the consultant returned the request to us)

Date sent - (this is the date that we sent the completed request back to the contractor.

Below is my calculation. The problem that is occuring has to do with the line in red. There are times when the person entering the information mistakenly leaves out the "date rec'd consultant" but fills in the "date sent". This is OK in the company's eyes by is causing me problems with my calculation. It causes a 731,000+ number to appear.

I know that I have the line written right because I can move the line up in the case statement and it works fine, however, the others begin calculating incorrectly.

Help is apprecieated.

------------------------------

Case (

IsValid (date_received & date_to_consultant & date_recd_consultant & date_sent); (date_to_consultant - date_received) + (date_sent - date_recd_consultant); //ALL VALID

IsValid (date_received & date_to_consultant & date_recd_consultant) & IsEmpty (date_sent) ; (date_to_consultant - date_received) + (Get ( CurrentDate ) - date_recd_consultant); //SENT EMPTY

[color:red]IsValid (date_received & date_to_consultant & date_sent) & IsEmpty (date_recd_consultant); date_to_consultant - date_received; //FROM CONSULTANT IS EMPTY_SENT TO GC

IsValid (date_received & date_to_consultant) & IsEmpty (date_recd_consultant & date_sent); date_to_consultant - date_received; //SENT TO CONSULTANT

IsValid (date_received & date_sent); date_sent-date_received; //RECEIVED AND ANSWERED BY SGA

IsValid (date_received) & IsEmpty (date_to_consultant & date_recd_consultant & date_sent); Get ( CurrentDate ) - date_received) //RECEIVED BUT NOT SENT ANYWHERE AND NOT ANSWERED

[Ender]

I see a couple problems with your calc, though I don't know if these fixes alone will resolve your current error.

IsValid() is not meant to be used for testing whether a local field contains data; it's for testing the validity of relationships and data types. Instead use 'not isempty()' for your data entry tests.

The second problem is the syntax you are using for a logical 'and'. The amperstand outside the not isempty()s should be replaced with 'and' operators. The '&' is used only for contatonating data. In fact, the '&' signs you are using within the isValid() function would give you an 'or' result if you replaced the 'IsValid' with 'not IsEmpty' (it's not clear if this is the intent or not.) As it is now, those '&' inside the IsValid() have no meaning, and make the isValid() return a true result.

In any case, I'd suggest rewriting those functions using logical 'and' and 'or' operators instead of '&'. This should make the function easier to read when you see it again in the future. :)

[Dana G]

OK I'm getting frustrated and a little embarassed. I have been messing with this for so long and I keep doing the same things expecting different results.

I am focusing on getting the sga_tt field to calculate correctly.

Attached is a sample file that shows 6 ways that the dates can be entered. I need the sga_tt field to always show the number of days it took to send the request back. The individual records have some explaination that should help understand. I am getting incorrect numbers and -700,000 numbers. At one point or another I have gotten each record to show the correct calculation results but I can't get them to all work at the same time.

Please take a look at the calc for sga_tt and see if you can help me with something.

There is something wrong with my Case statment but I can't figure out where. I can move the lines in the calc to different orders and it fixes one but messes up another.

Thanks in advance!

test.zip

[Dana G]

I know there is someone out there who can point me in the right direction! I love this forum and would be forever thankful to anyone who could mention some other way for me to write this calc. Even if you don't know if it works - at this point I'll try anything. Throw your idea my way and I'll test it. If it doesn't work - at least I got some practice!!!

Thank you all!!

P.S. I am also working on getting us on FM8, so if you know a solution that will work there...that would be awesome too!

[Ender]

Each IsEmpty() should only have one field:

IsEmpty (date_to_consultant and date_recd_consultant)

should be

IsEmpty (date_to_consultant) and isempty (date_recd_consultant)

[Dana G]

Thank you SO much Ender.

[Dana G]

P E R F E C T !

I can't believe it was something so simple. Again, I am forever thankful!!!

:woohoo:

[-Queue-]

Just an equivalency note here.

IsEmpty(field1) and IsEmpty(field2) = IsEmpty(field1 & field2)

IsEmpty(field1 and field2) was close, but the 'and' forces the inner result to be a boolean instead of a concatenated value.