Field Definition Calc Review/Help?

[J. Williams]

Hello All - Does anyone here know why I would be getting the called for result in instances where there is NO accepted date? (Perhaps relative only to last section of case? Not sure.)

Many thanks for your consideration. JW

Case( ArchiveOverride = 1, "Archived",

ProjectCategoryOverride <> "", ProjectCategoryOverride,

CStatus::Accepted Date <> "", "Post CCA", "Pre CCA",

CStatus::Accepted Date <> "" and AsBsRecdinMFPRUfromDelinDate <> "" and ACStatusNewRev = "On File" and DRSStatusNewRev = "On File", "Archived",

CStatus::Accepted Date <> "" and AsBsRecdinMFPRUfromDelinDate <> "" and (ACStatusNewRev = "Pending" or DRSStatusNewRev = "Pending"), "Pending TIF Upload and/or AC Receipt")

[comment]

The Case function evaluates each test in order, and returns the result specified for the first test that evaluates as true.

In your example, the last 2 tests can be true only if the third test is true, so it never gets to them. Change the order of your statements, so that the specific comes before the general.

---

Unrelated: consider using 'not IsEmpty' instead of '<>"".

[J. Williams]

Oh Geez What an important little rule to forget about! Many thanks!

Also, I'm curious, what is the reasoning behind using 'not IsEmpty' rather than <>""

Edited December 27, 2005 by Guest

[comment]

For one thing, it's more readable.

For another, "" is an empty TEXT string, so strictly speaking a date field is never equal to "". Filemaker is rather relaxed in this aspect, but as it moves towards being a serious database, this might change - so call it future-proofing.

[LaRetta]

Hi Julia,

Since you are the curious sort, another idea worth considering is boolean logic, isn't it Michael? I believe it would also be future proof as I expect FM to always embrace boolean. If any of those fields contain number (or date) then ANY numeric value (to FM) will produce true (dates are numbers also). Field type can be text, number or date but MUST contain a number (or 0 or be empty) for boolean to translate * properly.

Example, assuming those Override fields contain a 1 (for yes):

Case( ArchiveOverride , "Archived" ,

... will work same as ...

Case ( not IsEmpty ( ArchiveOverride ) , "Archived" ,

So you calc might also look like this (note dates are listed without a test also):

Case ( ArchiveOverride , "Archived" ,

ProjectCategoryOverride , ProjectCategoryOverride ,

AcceptedDate and AsBsRecdinMFPRUfromDelinDate and ACStatusNewRev = "On File" and DRSStatusNewRev = "On File" , "Archived" ,

AcceptedDate and AsBsRecdinMFPRUfromDelinDate and ( ACStatusNewRev = "Pending" or DRSStatusNewRev = "Pending" ) , "Pending TIF Upload and/or AC Receipt" ,

AcceptedDate , "Post CCA" , "Pre CCA" )

* Certain text entries (in versions prior 7) will also return boolean true result (Y, N, Yes, No, T, F, True, False) but I suggest you steer clear of using them in this way. Since your test simply determines whether the fields are empty then these exceptions wouldn't apply anyway.

I believe boolean logic is a bit quicker (and to me it is MUCH clearer) so if you are comfortable it might be used here as well. It is easy to remember that a number (or a date) IS or it ISN'T. Even if you don't use it here, boolean logic is a powerful concept which should be taken advantage of whenever possible.

LaRetta :wink2:

[comment]

Case( ArchiveOverride , "Archived" ,

... will work same as ...

Case ( not IsEmpty ( ArchiveOverride ) , "Archived" ,

This wouldn't work for me, since I often have a value of 0 in the field. So either:

Case( ArchiveOverride , "Archived" )

or:

Case( not ArchiveOverride , "Active" )

[T-Square]

I'm going to jump in here with something of a tangent: I am not a big fan of using the Case statement to make comparisons in different fields. It is very easy for the circumstances in the separate fields to fail to be both mutually exclusive AND completely exhaustive. This is a good example of that difficulty.

Nested If statements can get quirky very quickly, but I find I am a more reliable programmer when I use them. They force me to be sure that each eventuality is clearly accounted for. (I was going to try to do this calculation as a nested If, but the last two tests made my brain hurt).

David

[LaRetta]

This is a good example of that difficulty.

Hi David,

I think you might be misinterpreting here...

Case() is quite valid for all comparisons and works wonderfully in this calc, regardless. Our discussion revolves around the not IsEmpty() test itself which would fail if numeric result is 0 (a boolean test would not fail in either case), pun intended.

I like the clarity of If() because my mind easily understands there are two possible options and Case() offers more than two ... that is the ONLY difference between them (in versions 7/8). In versions less than 7, If() also requires a default result and Case() does not. But I feel nested If()s are more confusing and (I think) also require more cycles to process. Miss one proper default result on an If() test and the calc will fail just as easily. Case() is the winner if more than two results are required. That is its purpose after all ...

But, as with all things in FM, there are many ways to write a calculation.

LaRetta

[comment]

I think what you're saying, IOW, is that using Case() requires you to think out all possibilities in advance. And to test thoroughly. That, IMHO, is GOOD thing. A VERY good thing. And it applies to ALL calculations (and scripts, and relationships...), not just the Case() function.