2 Replies Latest reply on Jul 9, 2014 5:15 AM by HughKendall

    Case function returning blank in text calculation



      Case function returning blank in text calculation


           New to Filemaker, not programmer but a logical person.  I cannot get a simple Case calculation to work.  I am working with a single unrelated table at this point, to keep things simple for hypothesis testing. 

           Table "Patients" has 2 fields; 'Patient details' and 'Procedures'.  Field 'Patient details'  is a long text string comprised of Google calendar event details, exported to Excel and imported into Table"Patients".  The field mapping is correct, and after import, all the details of the text string from the Google calendar appear in the Field' Patient details'.  The text string contains the Patient name, birthdate in parenthesis, a description of the procedure that day, their family MD's name, etc.  Lets say there are only 2 kinds of procedures, and each text string will have either 'Appenectomy' or 'Gallbladder' as the procedure, some variable place within the text string.

           My second field in Table "Patients" is a calculated field called "Procedures", and I wish to extract the words "Appendectomy" or "gallbladder" from the text string in Field "patient details' that shares this unrelated table.  This is the syntax I have used:

           Procedures = Case(Patient details="Appendectomy";"Appendectomy")

           to populate the field Procedures with just the word "appendectomy" as long as  the 'Patient details' field contained it. It returns blank, as if the text string is not being evaluated.  If I add a default result, such as

           Procedures = Case(Patient details="Appendectomy";"Appendectomy";"null")  

           then all fields, even those with "Appendectomy" in the text string, return "null".

           I have tried using this imported data to execute other text and logical functions such as LeftWords, and it works.  

           PatternCount(Patient detalls;"Appendectomy")

           returns 1's in all the correct places.  But both the If function and the Case function all return blank.  I dontthink it's the data set, as other functions sen to be able to evaluate the text string.  The 'Patient details" file is a text field.  What am I doing wrong?  

        • 1. Re: Case function returning blank in text calculation

               You have described a patient Details field which you acknowledge never equals "appendectomy".

               It has much more information. It sometimes CONTAINS "appendectomy".

               Looks like what you want is:

               case( patternCount( patient Detail ; "Appendectomy" ) ; "Appendectomy")

          • 2. Re: Case function returning blank in text calculation

                 Thats it, thank you very much.  It makes perfect sense, and I had also explored other operators and functions to see if there was a method of specifying contains, rather than equals. When I looked at other text parsing functions, though, the = sign did seem to pick out one from many, such as in LEFTWORDS, etc.  My mistake.


                 I'll have to do some more reading about what I asked the program to do, and what your solution does.  Many thanks.