1 2 Previous Next 21 Replies Latest reply on Jun 15, 2012 12:55 PM by philmodjunk

    Summary field with type Count of does not always work

    smaros

      Summary

      Summary field with type Count of does not always work

      Description of the issue

      FileMaker Product(s) involved:FileMaker Pro 8, FileMaker Pro 9, FileMaker Pro 10 (The problem does not exist in FileMaker Pro 7) Operating System(s) involved:Mac OS X 10.5 and Windows XP SP3Maybe others too. Detailed description of the issue:The summary field Count of does not correctly count non-empty fields if the field contains specific texts. Example: "zxcvbnm,.asdfghjklqwertyuiop12345678"  Exact steps to reproduce the issue:1. Create a database2. Create a text field3. Create a Summary field, with option Count of text 4. Exit the Manage Database dialog 5. Create a record and enter any text in the text field6. Commit the record and the summary field should show the current count of records with non-empty text fields (1)7. Create a record and enter "0e800" in the text field8. Commit the record and the summary field does not show the correct result. (still 1) Expected Result: In a database with 6 non-empty records, the result shoud be 6. Actual Result:In some cases, the result is less than 6. Exact text of any error message(s) that appeared:N/AAny additional configuration information/troubleshooting that is relevant to the issue:The bug shows only when the text contains these three parts at once.1. The first part must contain the number zero (0) or a decimal separator (. or , depending on the operating system's international settings)2. The second must contain the letter E (upper or lower case)3. The third part must contain numbers that makes an integer >799 Any number of other characters may be used as filling, and the bug still appears. Examples of values of the text field that confirms the bug:0e800Visual Studio .Net Reference, page 10-25 (if you use American decimal point)Orwell, George - 1984 paperback (if you use European decimal comma)   Any workarounds that you have found: None. 

        • 1. Re: Summary field with type Count of does not always work
          comment_1
            

          OMG. Not only it's true - also

           

          Count ( "a" ; "0e800" ; "c" )

           

          returns 2.

           

           

          Did I say OMG?

          • 2. Re: Summary field with type Count of does not always work
            mrvodka
              

            Ouch. Nice find. 

            • 3. Re: Summary field with type Count of does not always work
              LaRetta_1
                

              Ya know, it's one thing when a bug means a drop-down doesn't jump to the proper number for us.  It's another when a bug erases data (import auto-map, Modify Table column shift, etc) or produces incorrect mathematics (timestamp, random and now this).

               

              And FileMaker wonders why some of us have attitudes - particularly when many of these bugs have been long-standing, FM has known about them (and many were not PUBLISHED).

               

              There is simply no excuse ... okay,  okay, this bug is a bit obscure but let's watch this thread and see how long it takes to get it fixed!!  I'm watching those other threads (in fact many bugs) the same way. 

              • 4. Re: Summary field with type Count of does not always work
                TSGal

                smaros:

                 

                Thanks for your post and detailed information.

                 

                This was first reported at the end of 2006, and it was marked as not a problem.  Here is an explanation from a third-party developer, Steven Blackwell, from July, 2008.

                 

                "The Count function is likely working correctly here, since it is supposed to count ONLY valid, non-blank values.

                 

                In a text field, FileMaker Pro is interpreting the 'e' as scientific notation depicting a number that falls outside the valid range, provided the syntax is something along the lines of xennn, where x is an integer, and the valu eof nnn does not exceed 799.  Thus 99e799 is valid and is counted, whereas 99e800 is NOT valid and is not counted.  As far as I know, this behavior persisted through FileMaker Pro 9.  This explains the difference you are seeing between '05E01508" and '05E00453.'

                 

                This behavior was discussed extensively at a meeting of some of the Authorized Trainers in October of last year and during the Phase 2 testing cycles of the FileMaker Pro 9 family in early 2007.

                 

                For what it's worth, I believe an argument can be made, and was made, that a text field ought not to be interpreted as having scientific notation for numbers.  But the Count function itself works correctly I believe.  And we should be very careful about changing its behavior and breaking countless solutions in the process."

                 

                ---------------

                 

                In essence, our current calculation engine, even though the field is of type Text, evaluates the contents as a Number.

                 

                One workaround is to enter a letter before or after the number.  That is, <letter><number>e<number greater than 799>.

                 

                I have forwarded this information to our Technical Support contact so that a Knowledge Base Article can/should be written.

                 

                TSGal

                FileMaker, Inc.

                • 5. Re: Summary field with type Count of does not always work
                  comment_1
                     With all due respect to Mr. Blackwell and others, the argument simply doesn't hold water. In a text field, the entry "0e800" is a valid, non-blank value. The fact that evaluating the text as a number or as a formula would produce an error is completely irrelevant in this context.

                  Moreover, if every text that represents an out-of-range numeric value shouldn't be counted, then writing out 800 zeros in the following formula =

                  Count ( "a" ; "100000000...00000" ; "c" )

                  should also return 2. Does it?


                  • 6. Re: Summary field with type Count of does not always work
                    TSGal

                    comment:

                     

                    Yes, your example with 800+ zeros does indeed return 2.

                     

                    TSGal

                    FileMaker, Inc.

                    • 7. Re: Summary field with type Count of does not always work
                      comment_1
                         Well, I tested this with exactly 800 zeroes - which is way beyond Filemaker's stated limit of 10^400. I don't see why there would be another (undocumented) limit at 10^800, but I don't intend to delve on this, because - as I said - it is completely irrelevant.


                      The more I contemplate the response you quoted, the more infuriating I find it. It contradicts the basic concept of data having a type. Text is text is text; it's not the business of the count algorithm to look at the contents of the text and speculate what would happen if the text were converted to another data type.

                      Otherwise, where is the limit? Why stop at number only? If the string "10e805" is not valid text, then why would "1/1/4001" be a valid one, when it's a date outside of Filemaker's range?

                      This is something so obvious it shouldn't even needed to be said. I cannot see how anyone in their right mind could claim this to be the correct behavior. Look at the example in the original post - it's a reference to a book, for crying out loud.


                      The proper response for Filemaker to take here would be to acknowledge that this is a mistake and to correct it ASAP. As for the warning that:

                      "we should be very careful about changing its behavior and breaking countless solutions in the process"

                      I dare anyone to name one (not "countless", just one) solution that actually relies on this "behavior". Let me make it even easier: just give me an example of a solution where this behavior would be useful and intended by the developer.






                      • 8. Re: Summary field with type Count of does not always work
                        LaRetta_1
                          

                        TSGal wrote:

                        In essence, our current calculation engine, even though the field is of type Text, evaluates the contents as a Number. I believe an argument can be made, and was made, that a text field ought not to be interpreted as having scientific notation for numbers. 


                        This is ridiculous!!  And, if it was so thoroughly discussed and known about SINCE 2006, then why wasn't a THING written about it - not even a notation as exception in FM Help, explaining the unexpected behavior?  I am shaking angry right now because FM has known FOR THREE YEARS and not one word about it is in print, not even in knowledge base - knowing full well that, if you all disagreed in the interpretation then might it cause problems for us!??
                        Another example of MAJOR poor support from FileMaker.  Text is text!!!  This one takes the cake.

                        • 9. Re: Summary field with type Count of does not always work
                          philmodjunk
                            

                          I agree with LaRetta and Comment,

                           

                          This is both ridiculous and flat out dangerous to the data integrity of our solutions.

                           

                          "And we should be very careful about changing its behavior and breaking countless solutions in the process."

                           

                          I realize that Stephen Blackwell said this, not filemaker inc., but still have to say:

                          Funny how that's never stopped filemaker in countless other areas where they've made changes--take the infernal auto-sort issue that was making a newly created record disappear from sight on one of my users just today.

                          • 10. Re: Summary field with type Count of does not always work
                            TSGal

                            All:

                             

                            I hate being the messenger.

                             

                            I knew full well before posting, especially after reading LaRetta's statement "...let's watch this thread and see how long it takes to get it fixed!!" that my post would cause some upset.

                             

                            I wasn't trying to insinuate any position on this issue.  It's my responsibility to remain unbiased.  However, I felt it was necessary to give you as much information possible.

                             

                            The quote from Steven Blackwell was attached to the original problem, and I probably should have not included it.  However, I have not seen any other internal cases where a person was quoted.  Why it was added?  I don't know.  When I originally posted, I thought it was relevant so you'd have as much information possible.  I think I was wrong to include it.

                             

                            The point is, at this time, our calculation engine evaluates numbers in a text field, and if the number falls outside that range, you do get incorrect results.

                             

                            I've added this entire thread to the case.

                             

                            comment - I don't want to beat a dead horse, but "1/1/4001" is not being evaluated as a date, but as 1 divided by 1 divided by 4001 which is a valid number.  If you entered "1/1/.00000........1" (800 zeros), then it would be invalid.

                             

                            I have sent each of you a private message.

                             

                            TSGal

                            FileMaker, Inc.

                            • 11. Re: Summary field with type Count of does not always work
                              mrvodka
                                  Well personally, I liked the fact that you added his quote in. Not to be offensive, but it is a lot more than the usual PC answer that is given. If you had not posted that, then we would have never seen the reasoning of why it was evaluting the way it was.
                              • 12. Re: Summary field with type Count of does not always work
                                comment_1
                                  

                                TSGal wrote:

                                The quote from Steven Blackwell was attached to the original problem, and I probably should have not included it.


                                I don't see why not. As I understand, it represents FMI's position that the described behavior is intended.

                                 

                                I am perfectly willing to ignore the "reasons" stated in the quoted document, even pretend I never saw it. It's not necessary to refute Mr. Blackwell's arguments in order to make the case here, since no possible argument justifying the current behavior can be made.

                                 

                                 


                                TSGal wrote:
                                The point is, at this time, our calculation engine evaluates numbers in a text field, and if the number falls outside that range, you do get incorrect results.

                                No, I think at this time the point is that FMI will not acknowledge this is a bug.



                                • 13. Re: Summary field with type Count of does not always work
                                  mrvodka
                                    

                                  Seems like a KB article has been written about this BUG; although it does not acknowledge that it is an understood bug.

                                   

                                  http://thefmkb.com/7320

                                   

                                   

                                  • 14. Re: Summary field with type Count of does not always work
                                    comment_1
                                      

                                    The article does not describe the full extent of the issue. As the OP shows, if the string before <number 2> contains a decimal separator, it will be interpreted as a number (presumably "0.0") and cause the entire value not to be counted - even if you "enter a letter before or after the field contents".

                                    None of these are counted as valid, non-blank text values:

                                    ".E801"
                                    ".E801x"
                                    "x.E801"
                                    "x.xE801"
                                    "x.xE801x"
                                    "x.xE8x01x"
                                    "x.xEx8x0x1x"
                                    "xxx.xxxExxx8xxx0xxx1xxx"


                                    As for the suggestion to modify the data to suit the bug ... well, really.

                                     

                                    If you are using the Count() function in a calculation, you can get the correct result by using:

                                    ValueCount ( List ( related::value ) )


                                    If you are counting using a summary field, may God help you. FMI won't.









                                    1 2 Previous Next