AnsweredAssumed Answered

Inconsistent results: empty fields returned for max function in FileMaker Pro 9 Advanced.

Question asked by conlin100 on Jul 20, 2009
Latest reply on Jul 23, 2009 by conlin100

Title

Inconsistent results: empty fields returned for max function in FileMaker Pro 9 Advanced.

Post

<!-- [if gte mso 9]><xml> <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif] --><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif] --><!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:"";      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:"Times New Roman";      mso-fareast-font-family:"Times New Roman";} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;} --><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable      {mso-style-name:"Table Normal";      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:"";      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:"Times New Roman";      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} </style> <![endif] -->The MAX function is inconsistent when applied to my database of high-school test results.

 

In order to simplify it, I reduced the function to this:

 

MM5=        //outside the function dialog box

             Max(CHSE5_09::Math scale score)

 

Many of the result fields are blank; that is, even though there is a score for "Math scale score", the MM5 field is blank for over half of the records.

 

Imported from Excel

I created the table by importing an Excel spreadsheet. I thought maybe the data types used in Excel were wrong, so I

(1) Changed the "Math scale score" data type to NUMBER in FileMaker and

(2) Changed the scores to NUMBER in Excel, and imported again. Still inconsistent. (I also tried with the MIN function, and the problem is there, too.)

(I even created a new spreadsheet with the scores as numbers, to make sure there wasn't some vestage of a text field. Same problem.)

 

Note: the function returns the correct results when it works.

 

Copying from the Manage Database window

            MM5   Calculation       Unstored, = Max (CHSE5_09:: Math Scale Score)

 

I am using FileMaker Pro 9 Advanced. The calculation fields are in a separate table.

 

Thank you in advance.

Outcomes