AnsweredAssumed Answered

Case function returning blank in text calculation

Question asked by HughKendall on Jul 8, 2014
Latest reply on Jul 9, 2014 by HughKendall


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?