12 Replies Latest reply on Feb 11, 2015 12:58 PM by jobemay

# What have I overlooked in my "neither-nor" Case-Function?

What have I overlooked in my „neither-nor“ calculation?

I have 4 fields in my dictionary:  FIELD1FIELD2, FIELD3, FIELD4.

Some records have values for all 4 fields, some only for some fields.

The empty fields display a symbol denoting „empty“: „ø“

Example:

Record1: FIELD1: abc,   FIELD2: def,   FIELD3: ghi,   FIELD4: jkl

Record2: FIELD1: aaa,   FIELD2: bbb,   FIELD3: ccc,   FIELD4: ddd

Record3: FIELD1: ø,     FIELD2: fff,   FIELD3: ggg,   FIELD4: hhh

Record4: FIELD1: mma,   FIELD2: ø,     FIELD3: mmc,   FIELD4: mmd

Record5: FIELD1: ø,     FIELD2: ø,     FIELD3: xzz,   FIELD4: zzz

Now I want to sort the records alphabetically using a sort field SORTFIELD.

a) If FIELD1 contains a value it should be used in the SORTFIELD.

b) If it does not then FIELD2 should be used  in the SORTFIELD.

c) If neither FIELD1 nor FIELD2 contain a value then FIELD3 should be used in the SORTFIELD.

So the result should be:

Record1: FIELD1: abc,   FIELD2: def,   FIELD3: ghi,   FIELD4: jkl   SORTFIELD: abc

Record2: FIELD1: aaa,   FIELD2: bbb,   FIELD3: ccc,   FIELD4: ddd   SORTFIELD: aaa

Record3: FIELD1: ø,     FIELD2: fff,   FIELD3: ggg,   FIELD4: hhh   SORTFIELD: fff

Record4: FIELD1: mma,   FIELD2: ø,     FIELD3: mmc,   FIELD4: mmd   SORTFIELD: mma

Record5: FIELD1: ø,     FIELD2: ø,     FIELD3: cba,   FIELD4: cbb   SORTFIELD: cba

(And of course the records sorted should be:

Record2: FIELD1: aaa,   FIELD2: bbb,   FIELD3: ccc,   FIELD4: ddd   SORTFIELD: aaa

Record1: FIELD1: abc,   FIELD2: def,   FIELD3: ghi,   FIELD4: jkl   SORTFIELD: abc

Record5: FIELD1: ø,     FIELD2: ø,     FIELD3: cba,   FIELD4: cbb   SORTFIELD: cba

Record3: FIELD1: ø,     FIELD2: fff,   FIELD3: ggg,   FIELD4: hhh   SORTFIELD: fff

Record4: FIELD1: mma,   FIELD2: ø,     FIELD3: mmc,   FIELD4: mmd   SORTFIELD: mma)

It was easy to obtain a) and b) with the calculation: Case ( FIELD1 = "ø"; FIELD2 ; FIELD1),

but whatever I tried with „and“ or „&“ did not work to get c). I suppose I have overlooked something very basic, but what?

j

• ###### 1. Re: What have I overlooked in my "neither-nor" Case-Function?

Are you actually sorting records, or trying to get a calc to return values in a specific order within the calc's result string?

• ###### 2. Re: What have I overlooked in my "neither-nor" Case-Function?

jobemay wrote:

I have 4 fields in my dictionary:  FIELD1FIELD2, FIELD3, FIELD4.

Some records have values for all 4 fields, some only for some fields.

The empty fields display a symbol denoting „empty“: „ø“

Your description is somewhat confusing; are those fields actually empty, or do they hold the value ø?

If they are, then Case ( FIELD1 = "ø"; FIELD2 ; FIELD2 ) cannot work.

Try

Case (

not IsEmpty ( FIELD1 ) ; FIELD1 ; // otherwise: FIELD1 ≠ "ø" ; FIELD1 ;

not IsEmpty ( FIELD2 ) ; FIELD2 ; // otherwise: FIELD2 ≠ "ø" ; FIELD2 ;

FIELD3

)

or

GetValue ( List ( Field1 ; Field2 ; Field3 ) ; 1 ) // if the fields are empty

jobemay wrote:

whatever I tried with „and“ or „&“ did not work

'&' is not a Boolean operator in FileMaker.

• ###### 3. Re: What have I overlooked in my "neither-nor" Case-Function?

It looks to me like you're not trying to find the non-empty field, but rather, the earliest field in the sort order. Have you taken a look at the Min ( ) function? I think it accomplishes what you're trying to do, thus:

Min ( FIELD1 ; FIELD2 ; FIELD3 ; FIELD4 )

Mike

• ###### 4. Re: What have I overlooked in my "neither-nor" Case-Function?

I am actually sorting the records.

Final result should be a list:

SORTFIELD: aaa

SORTFIELD: abc

SORTFIELD: cba

SORTFIELD: fff

SORTFIELD: mma

The dictionary deals with verbs having 4 different stems. So it would be natural to be able to sort them alphabetically according to the first form (let us say the INFINITIVE_active). Some verbs are defective though and do not have an INFINITIVE_active. So we use the second form (let us say the PAST_active) instead. Some verbs do not have either, so we use the third form (let us say the INFINITIVE_passive).

If we used just the first form to sort the verbs, all verbs not possessing an INFINITIVE_active (they contain the symbol "ø", denoting "no form existing") would appear before the non-defective verbs. This is of course not desirable.

My calc Case(FIELD1 = "ø"; FIELD2 ; FIELD1) covers the first two forms, but how can I write a calculation denoting: If FIELD1 has the value "ø" and FIELD2 has the value "ø" then put the value of FIELD3 into the SORTFIELD ? (Thank God there are no verbs which lack 3 forms!)

• ###### 5. Re: What have I overlooked in my "neither-nor" Case-Function?

jobemay wrote:

My calc Case(FIELD1 = "ø"; FIELD2 ; FIELD1) covers the first two forms, but how can I write a calculation denoting: If FIELD1 has the value "ø" and FIELD2 has the value "ø" then put the value of FIELD3 into the SORTFIELD ?

Case (

FIELD1 ≠ "ø" ; FIELD1 ;

FIELD2 ≠ "ø" ; FIELD2 ;

FIELD3

)

• ###### 6. Re: What have I overlooked in my "neither-nor" Case-Function?

Sorry to all for the late answer. Was ripped away from the Mac by family last night!

And sorry for the ambiguity. Yes the empty fields (no linguistic value) are set to display the value "ø" denoting this.

Case (

FIELD1 ≠ "ø" ; FIELD1 ;

FIELD2 ≠ "ø" ; FIELD2 ;

FIELD3

)

What I do not understand though, is why my version does not work:

Case (

FIELD1 = "ø" ; FIELD2 ;

FIELD2 = "ø" ; FIELD3 ;

FIELD1

)

Thanks

j

• ###### 7. Re: What have I overlooked in my "neither-nor" Case-Function?

Thanks Mike, but no, I wanted to put the first not-empty field of each record into the SORTFIELD and then use the SORTFIELD to sort the records. So the Min ( ) does not apply.

erolst supplied:

Case (

FIELD1 ≠ "ø" ; FIELD1 ;

FIELD2 ≠ "ø" ; FIELD2 ;

FIELD3

)

which worked.

j

• ###### 8. Re: What have I overlooked in my "neither-nor" Case-Function?

jobemay wrote:

What I do not understand though, is why my version does not work:

Case (

FIELD1 = "ø" ; FIELD2 ;

FIELD2 = "ø" ; FIELD3 ;

FIELD1

)

Case() returns the results for the first test that evaluates to True; your first test (field1 = ø) doesn't check if field2 is ø, too, and so may return the wrong result (field2) for the case where field1 = ø AND field2 = ø.

jobemay wrote:

And sorry for the ambiguity. Yes the empty fields (no linguistic value) are set to display the value "ø" denoting this.

Well, that's as ambiguous as before

I assume you mean “a field holds the character 'ø' in order to denote a non-existing linguistic value in this category” – and thus the field is not empty.

• ###### 9. Re: What have I overlooked in my "neither-nor" Case-Function?

The calculated field calculation:

Case(

field one ; field one ;

field two ; field two ;

field three ; field three ;

field four ; field four ;

""   <-- you can insert some value here to sort the empty records at the top of the list

end case

if you are using mixed values such as numbers, letters, etc. your sort may be a bit unusual and you'll have to deal with that.

Don't forget to set the calulcated field type as TEXT, it defaults to NUMBER.

You might get better perform using a text field and clicking the button to make it a calculation. Uncheck the box show it will replace. This will update whenever you change the value in any of the above fields.

• ###### 10. Re: What have I overlooked in my "neither-nor" Case-Function?

I see. I misread your "ø" symbol as an "o". (Might want to use "" instead if you want to indicate "empty". Some of us have old eyes.)

Mike

Edit: Scratch that. I'm having a bad day. Ugh.  <rolleyes>

• ###### 11. Re: What have I overlooked in my "neither-nor" Case-Function?

That is exactly what I wanted to say! My English is not the best and my logical thinking is even worse.

And thanks a lot for explaining why my calculation did not work. I see now why.

j

• ###### 12. Re: What have I overlooked in my "neither-nor" Case-Function?

As erolst correctly mentioned I was a bit ambiguous. Read what he wrote me.

Anyway he had a perfect solution for the problem.

j