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

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

    jobemay

      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?
          Stephen Huston

          How is your field indexed?

           

          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?
            erolst

            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?
              Mike_Mitchell

              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?
                jobemay

                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?
                  erolst

                  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 ?

                   

                  This had already been answered:

                   

                  Case (

                    FIELD1 ≠ "ø" ; FIELD1 ;

                    FIELD2 ≠ "ø" ; FIELD2 ;

                    FIELD3

                  )

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

                    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.

                    Your calculation did the trick:

                    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?
                      jobemay

                      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?
                        erolst

                        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?
                            Mike_Mitchell

                            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?
                              jobemay

                              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?
                                jobemay

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

                                Anyway he had a perfect solution for the problem.

                                j