8 Replies Latest reply on Jul 23, 2009 10:30 AM by conlin100

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

    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.

        • 1. Re: Inconsistent results: empty fields returned for max function in FileMaker Pro 9 Advanced.
          etripoli
             Most likely, the fields used in your relationship between tables do not match properly.  Look for spaces or return characters at the end of the text in the fields.
          • 2. Re: Inconsistent results: empty fields returned for max function in FileMaker Pro 9 Advanced.
            conlin100
              

            That doesn't seem to be it. I saved the file as a .CSV, and I did not see spaces where they shouldn't be.

             

            On the other hand, I saved the Excel file with the name A.xls, and imported again. The MAX formula now works! (Curiouser and curiouser!) Do spaces (converted to underscores) in imported file names confuse FileMaker? 

             

            I thought of a couple of other things. My database has 50 or 60 tables. Could that be a problem?

             

            This was my first post on this forum and I hit the wrong button, indicating the problem was solved. Sorry.

            • 3. Re: Inconsistent results: empty fields returned for max function in FileMaker Pro 9 Advanced.
              etripoli
                 Check the fields on both sides of the relationship, using the Replace Field Contents and Trim ( field ) function to remove whitespace from around the fields. Spaces and underscores are fine for table names; some of the other punctuation characters can cause problems.  It could also have been a problem with the index on the fields used in the relationship.  But, 9 times out of 10, when records aren't relating, it's spaces or carriage returns.
              • 4. Re: Inconsistent results: empty fields returned for max function in FileMaker Pro 9 Advanced.
                conlin100
                   <!-- [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] -->

                Two formulas that give different results:

                 

                I have forumlas in two layouts that do the same thing: they get the maximum of one field.

                The formula in the layout with the data works. (Calculation   = Max(Math scale score)).

                The formula in the "Calculations" layout doesn't work right. (Calculation   = Max(A::Math scale score)).

                 

                Do I need to make sure the fields are numerical? I have not been careful with that. If so, can I change them after I import the data to make the forumlas work? I just changed the "Math scale score" field to text, got rid of indexing, and tried again. Same problem.

                 

                I see that Excel adds a return after every field. Is that a problem? Also, is there a way to detect spaces or returns in FileMaker?

                 

                Thank you in advance.

                 

                • 5. Re: Inconsistent results: empty fields returned for max function in FileMaker Pro 9 Advanced.
                  conlin100
                     Oh, and yes, I did trim & replace the contents of the "Math scale score" field.
                  • 6. Re: Inconsistent results: empty fields returned for max function in FileMaker Pro 9 Advanced.
                    etripoli
                      

                    Most definitely set the FM fields to the appropriate data type.  Using Max on a number field will give a different result than Max on a text or date field.  You can change the data type at any time, without losing data (FM seems to store all data as text, but applies the selected data type when performing finds, calculations, joins, etc).

                     

                    And, if you have the key fields defined as text, and one side contains spaces or returns at the end, the relationship will not work correctly.  Use the Replace Field Contents command with the 'Trim (your key field)' calculation on 'your key field'.

                     

                    You can find spaces by using *" "*, in Find Mode.  Finding returns is another matter - 'Trim'ming is usually easiest.

                    • 7. Re: Inconsistent results: empty fields returned for max function in FileMaker Pro 9 Advanced.
                      conlin100
                         It's hard to detect the returns in Excel, but each cell seems to have one. Since it is standard to import from Excel, I would think that FM would delete the returns.
                      • 8. Re: Inconsistent results: empty fields returned for max function in FileMaker Pro 9 Advanced.
                        conlin100
                           Thank you for your help. I discovered my problem, and I feel foolish. My CALCULATIONS table had an incomplete set of student numbers. However, I did learn about FileMaker.