1 2 Previous Next 17 Replies Latest reply on Dec 13, 2013 10:46 AM by philmodjunk

    Aggregate functions forcing calculation field to evaluate

    comment_1

      Summary

      Aggregate functions forcing calculation field to evaluate

      Description of the issue

      A calculation field defined as = Min ( InputField ; 100 ) returns 100 when InputField is empty - even though the option 'Do not evaluate if al referenced fields are empty' is turned on.  I have no problem with the actual result returned by the function itself: Min ( "" ; 100 ) should return 100.But I don't understand why the calculation field evaluates, when the only referenced field is empty.  Workarounds: Min ( InputField + 0 ; 100 ) or: Case ( InputField ; Min ( InputField ; 100 ) ) 

        • 1. Re: Aggregate functions forcing calculation field to evaluate
          philmodjunk
            

          I agree that the behavior is inconsistent with filemaker's general pattern on how we would expect calculations to evaluate, but at least the behavior IS documented:

           

          From the Help file: "Returns the smallest valid non-blank value in field."

           

          As noted, this is not a problem in terms of the Value returned. It is a point to keep in mind when using other aggregate functions:

           

          Average ( emptyfield ; 100 ) will return 100 where Average ( 0 ; 100 ) will return 50.

           

          And this is the expected result per the help file as well:  "Returns a value that is the average of all valid, non-blank values in field."

           

          All the aggregate functions carry this caveat in their documentation.

           

          It's just that this exception to how all other calculations are controlled by the "do not evaluate if all referenced fields are empty" check box can be an unpleasant suprise for the developer if they haven't recently reviewed the documentation.

          • 2. Re: Aggregate functions forcing calculation field to evaluate
            comment_1
              

            Be so kind and read what I wrote.

            • 3. Re: Aggregate functions forcing calculation field to evaluate
              philmodjunk
                

              comment wrote:

              Be so kind and read what I wrote.


               

              I did. Note that I started my post with "I agree..."

               

              On reflection, however, I can see your point. "...valid, non-blank values..." and "do not evaluate if all the referencing fields are blank" aren't exactly the same thing are they?

               

              Min ( emptyfield ; nonempty field ) and Min ( emptyfield ; 100 ) both fit the "valid, non-blank" description in the help file, but we would expect "Min ( emptyfield ; 100 )" to return null if the "do not evalute..." box were checked and 100 if it were cleared. Whereas Min ( emptyfield ; nonempty field ) would return the contents of "nonempty field" in both cases.


              • 4. Re: Aggregate functions forcing calculation field to evaluate
                LaRetta_1
                  

                PhilModJunk wrote:
                On reflection, however, I can see your point. "...valid, non-blank values..." and "do not evaluate if all the referencing fields are blank" aren't exactly the same thing are they?

                 

                Min ( emptyfield ; nonempty field ) and Min ( emptyfield ; 100 ) both fit the "valid, non-blank" description in the help file

                 


                Huh?  If you create a calculation with 100 * Field1, it will NOT evaluate if Field1 is empty.  The 100 is NOT considered a field!!  And the same is true for Get ( CurrentDate ) etc ... they are NOT FIELDS.  The only way 100 * Field1 will evaluate is if you uncheck 'Do not evaluate if all referenced fields are empty.' 

                 

                I don't care WHAT the calculation should produce when it DOES evaluate ... the point is ... it should NOT EVALUATE AT ALL (using Comment's example) and that's the point.


                • 5. Re: Aggregate functions forcing calculation field to evaluate
                  philmodjunk
                    

                  LaRetta said

                   

                    "the point is ... it should NOT EVALUATE AT ALL (using Comment's example) and that's the point."

                   

                  If you read my post again, you'll find that's what I was saying. I was attempting to correct my earlier misstatement. To quote the part of my post you didn't put in yours:

                   

                  "min ( emptyfield, 100) should return null if the "do not evaluate..." check box is selected..."

                  • 6. Re: Aggregate functions forcing calculation field to evaluate
                    comment_1
                       If you don't mind, I would prefer this thread to be about the original issue - rather than about what you said, understood, misunderstood, and so on.
                    • 7. Re: Aggregate functions forcing calculation field to evaluate
                      TSGal

                      comment:

                       

                      Thank you for your post.

                       

                      Knowledge Base Article #5450 touches a little bit on this:

                       

                      http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=5450

                       

                      I know the article doesn't mention anything about "Do not evaluate if all reference fields are empty", so I did some digging to find out what was the reasoning for this Knowledge Base article.  I found several entries (all seem to be related to one another), and one mentions that originally, non-numeric data was being treated as zero.  Therefore, if "a" is put into a number field, and you used the Average function to evaluate "a" and 1, the result would be 0.5 (when it should be 1).  The same occurred with the Min and Max functions.  That is why it was changed.

                       

                      I know that still doesn't answer the empty field scenario, but I did see in one of the related notes "A record with empty value is correctly excluded."

                       

                      That gets us closer, but it still doesn't answer why the calculation is being referenced even though all referenced fields are empty.  Therefore, I have sent a request to our Development and Software Quality Assurance (Testing) departments specifically about the evaluation.  When I receive more information, I will let you know.

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: Aggregate functions forcing calculation field to evaluate
                        TSGal

                        comment (and all):

                         

                        After some further testing, I discovered the following:

                         

                        Min ( InputField ; 100 )

                         

                        evaluates to 100 for existing records (when InputField is blank).  For newly created records, the calculation does not evaluate.

                         

                        This has been confirmed by Testing and forwarded to Development.

                         

                        TSGal

                        FileMaker, Inc. 

                        • 9. Re: Aggregate functions forcing calculation field to evaluate
                          LaRetta_1

                               This is still broken in 13.  Min ( inputField ; 100 ) still produces 100 when inputField is empty even if 'do not evaluate if all referenced fields are empty' is checked.  

                               TSGal said, "evaluates to 100 for existing records (when InputField is blank).  For newly created records, the calculation does not evaluate."

                               Hi TSGal, in 13 it evaluates in either case at least  still breaks on FMPA13.0v1 on OSX 10.9.  Could you just bring this up again?  Thank you. :-)

                               ... corrected my sentence

                          • 10. Re: Aggregate functions forcing calculation field to evaluate
                            Fred(CH)

                                 Hi all,

                                 Thank your for your findings and explanations : i never realized this bug. Weird indeed.

                                 

                            in 13 it evaluates in either case

                                 Hi LaRetta,

                                 Could you please confirm you followed these steps :

                                   
                            1.           Create a new database with one field
                            2.      
                            3.           Create few new records
                            4.      
                            5.           Then create a stored calc with the formula and the "do not eval …empty " checked
                            6.      
                            7.           Then create few new record

                                  

                                 On my testings, after the step 3. i saw the result (the bug). But in step 4. when i created new records AFTER the stored calc was defined, the stored calc was not evaluated anymore.

                                 I tested also the UNstored calc and it always evaluated the formula, even it should not because the box was checked too.

                                 Conclusion, to avoid this bug you must :

                                   
                            1.           Define your calculation before creating the first record on your table.
                            2.      
                            3.           Store you calculation.
                            4.      
                            5.           Check the box "do not eval …empty" clin d'oeil.

                                  

                                 Too bad...
                            qui pleure
                                 Bye, Fred
                            • 11. Re: Aggregate functions forcing calculation field to evaluate
                              LaRetta_1

                                   Hi Fred,

                                   I replicated your steps (rMBP FMPA13.0v1) and it shows the issue perfectly.  The insidious nature of this bug is obvious with your walk-through and it was well worth the time to see it in action.  The bug is going on four years now.  FMI has become a dynamic, responsible, and energised organisation.  Even though it has slipped by in the past, I have confidence that (if they see it again) they will understand better particularly with your post as the example.

                                   Thanks again.  :-)

                              • 12. Re: Aggregate functions forcing calculation field to evaluate
                                LaRetta_1

                                     Oh I wanted to mention ... did you see Comment's suggestion in his opening post?  I've been using Min ( number  + 0 ; 100 ).  I just tested it again and it works in 13 as well.

                                • 13. Re: Aggregate functions forcing calculation field to evaluate
                                  LaRetta_1

                                       Also I misspoke, "... in 13 it evaluates in either case."  I meant the behaviour was same as before.

                                  • 14. Re: Aggregate functions forcing calculation field to evaluate
                                    Fred(CH)
                                         

                                    did you see Comment's suggestion in his opening post?  I've been using Min ( number  + 0 ; 100 ).  I just tested it again and it works in 13 as well.

                                         Yes i initially saw it and you are right to remind it !

                                         I was also wondering if it would be better to post a new thread for better visibility (and clarity too).

                                         Often Phil provide this consult because add a new comment in an old thread does not show the thread on first part of forum's main page.

                                         But i also saw that TSGal contributed on this previously so i think it is unnecessary.

                                         Bye, Fred

                                    1 2 Previous Next