Case Problem

[Cass]

I have field MF=Case() where case is:

Case(SNF =1 and FNF = 0 and TF = 0 ,1,SNF = 0 and FNF=0 and TF = 1,2,SNF = 1 and FNF = 1 and TF = 0,3)

This case returns 1 and 2 as it should, but where it should return 3, MF is blank. All fields are number and fields SNF, FNF, and TF all have either a 1 or 0. None of those three fields are blank.

Thanks for your help.

[LaRetta]

Hi Cass,

It looks to me like it should work. But you don't need the third criteria at all if all remaining records that aren't 1 or 2 should be a 3. In other words, it could be:

Case(SNF =1 and FNF = 0 and TF = 0 ,1,SNF = 0 and FNF=0 and TF = 1,2,3)

I would check your field data types again. Is this calc you listed a direct copy/paste? And are you sure there are records which meet the third criteria? Also, is the calc field indexed?

LaRetta

[Lee Smith]

Hi Cass,

I'm either confused with what your problem is, or I'm miss reading it. I think you are saying that when you enter SNF = 1 and FMF = 1 and TF = 0, that it isn't returning 3 in your calculation. It that is the case, something is wrong with your file, because it returned 3 in my test file? If it is something else, or I missed something, please comment.

Lee

[-Queue-]

You can simplify it a bit too.

Case(SNF and not (FNF or TF), 1, not (SNF or FNF) and TF, 2, SNF and FNF and not TF, 3)

Yes, I have a major boolean fetish.

I'm curious as to what results you desire if (SNF and FNF and TF) or not (SNF or FNF or TF). Right now it just returns null.

[Cass]

First, many thanks to you who responded.

To LaRetta,

Yes, all fields are indexed and yes, the post was a direct copy/paste and yes, all records that are not either 1 or 2 are a 3.

To Lee,

Yes, you are correct, when SNF=1 and FNF=1 and TF=0 it does not return a 3.

To Queue,

My over-65 brain is to simple minded. No offence, please.

Now to all,

This question is related to a previous question I posted (which Lee answered) about concatination. It gets complicated (see below) but the short answer is I'v worked around the problem so this and my previous questions are now moot. I really apologize for taking up your time (though as a matter of curiosity, I'd still like to know why the case didn't work).

Here is my final solution (Remember, I'm getting old):

In my database, I have records with fields TITLE, LAST NAME, FIRST NAME, and SPOUSE NAME. All records have a LAST NAME entry, and MANY have a FIRST NAME entry but the occurance of SPOUSE NAME and TITLE are random. In addition the occurance of SPOUSE NAME and TITLE are mutually exclusive. This leads to the following three possible concatinations (with spaces inserted when needed)

TITLE & FIRST NAME & LAST NAME (e.g., Mr. and Mrs. John Doe)

FIRST NAME & SPOUSE NAME & LAST NAME (e.g., John and Jane Doe)

FIRST NAME & LAST NAME (e.g., John Doe)

I created three calculation fields using IsEmpty() to "flag" whether there are entries in TITLE (TF), SPOUSE NAME (SNF), and FIRST NAME (FNF) then calculated the value of a "master flag" field, MF using the case shown above. Then I created another case field, CONCAT. It looks like this: Case(MF = 1,GetField("Title")&" "&GetField("First Name")&" "&GetField("Last Name"),MF = 2,GetField("Spouse Name")&" "&"&"&" "&GetField("First Name")&" "&GetField("Last Name"),IsEmpty(MF),GetField("First Name")&" "&GetField("Last Name")) Amazing to me, it works! See that IsEmpty(MF)? If the case above would have returned a 3, I would have used it there instead.

[-Queue-]

No offense taken. Sorry, I didn't mean to confuse. I just have a major efficiency bug in my system, and it causes me to NEED to factor all calculations down to their minimal counterparts. It might be something left over from my mathematics degree training.

Basically, a boolean is something that results in either true or false, sero or one, similar to binary. Many of FileMaker's functions are boolean in nature. IsEmpty(), for example, returns either a zero (not empty) or a one (empty). So saying If [isEmpty() = 1, 1, 0] can be shortened to IsEmpty(). Number fields that are either one or zero behave in the same way. If [Field = 1] is the same as saying 'Field'. And If [Field = 0] is the same as saying 'not Field'. 'Not' merely negates the statement that follows it. So 'not 0' = 1 and 'not 1' = 0.

Since it appeared that SNF, FNF, and TF can only equal 0 or 1, I just applied this logic to shorten 'Case(SNF = 1 and FNF = 0 and TF = 0 ,1,SNF = 0 and FNF=0 and TF = 1,2,SNF = 1 and FNF = 1 and TF = 0,3)' to 'Case(SNF and not FNF and not TF, 1, not SNF and not FNF and TF, 2, SNF and FNF and not TF, 3 )'. Then I used a minor amount of boolean logic to combine the not portions, and deduced the result I posted above.

The only stipulation with this is that the referenced fields must contain either a zero or one. If one or more of the fields is empty, then the logic fails.

Hope this helps clarify it a little.

[Cass]

Hi Queue,

Thanks again for your interest. Yeah, I'd guess you are a mathematician! I do understand Boolean logic; indeed my solution of setting up flag fields then using them in my final case calculation is based on Boolean (see my last paragraph above). But my mind works in a step-by-step linear fashion. An anecdote: Waaayy back in 1968 (ancient times) while working in the lab as a chemist, I wrote a Fortran program to do I don't now remember what. Used punch cards (don't drop them!). Well, the program did what it was supposed to do but took, as I remember, a half hour or so. Then a properly trained programmer got ahold of it, revised it and the program then ran in only one or two minutes! Anyway, thanks again to you, LaRetta and Lee for your help. It's nice to know such helpful people.