6 Replies Latest reply on May 15, 2017 2:41 AM by piaccounting

    MIN / MAX on DATES


      I created a solution based on the Filemaker Solutions Expense Report. In it I recreated the presentation of Start Date and End Date based upon the minimum and maximum dates entered in the two portals.


      What I was finding was the dates being returned appeared to relate only to the first portal. Now I am not sure that the calculation within the Filemaker Solution is actually correct, as I experimented with some dates and found that the results were incorrect; but nonetheless had to solve my problem.


      Initially I made the Start and End Date fields Calculation type fields and example unstored = Min( childA::Date ; childB::Date) but appeared to be getting results return from childA only.


      After some playing around I changed the calculation to:
      unstored, = Min ( Min (childA::Date) ; Min (childB::Date)) and it seemed to work. Not sure why - but wonder whether the specific Min (/Max) calculation on each portal is forcing the desired results whereas the single calculation for some reason does not. Any thought or observations welcomed.



        • 1. Re: MIN / MAX on DATES

          As I see it—

          Your initial calc:  Min ( childA::Date ; childB::Date ) is looking only at the first related record in each portal and giving you the lesser of A or B.

          The second calc:  Min ( Min ( childA::Date ) ; Min ( childB::Date ) )  looks for the earliest of all the childA dates and the earliest of all the childB dates (the inner Min expressions) and then the outer Min returns the earliest of those two.


          • 2. Re: MIN / MAX on DATES

            Hi keywords

            Thanks for your comments. I checked out the initial calculation referring to only the first record and this was borne out by the results I got.


            That then prompted me to think why should Min ( childA::Date ) as used in the second longer calculation look at all records but if you add an additional field as in Min ( childA::Date ; childB::Date ), as in initial calculation, then that restricts the search to just the first record. So I tested out the Min ( childA::Date ) and that does indeed search all the records in the table. Not sure I understand the logic behind this but nevertheless your help has put an explanation around my second calculation; thus removing its serendipitous nature.

            • 3. Re: MIN / MAX on DATES

              When there are 2 or more parameters in the function, it calculate in current record. So the value of related field is got from first related record.

              If there is only one parameter, it try to calculate on repeated field or related records.

              1 of 1 people found this helpful
              • 4. Re: MIN / MAX on DATES

                My understanding of the logic is that your initial cal is looking at fields from two different related sets, and when you pass two fields in the same Min ( ) function FM has no reason to look beyond the first record in each set, giving it two fields to compare.

                However, Min ( childA::date ) with just the single field is directing FM to compare that single field in all related records. Min ( childB::date ) is doing the same in the other related set. Note that each expression is returning a single date. When you the encase these two in a further Min ( ) function, FM is being directed to compare the two results found by the enclosed expressions: Min ( Min ( childA::date ) ; Min ( childB::date ) ).

                • 5. Re: MIN / MAX on DATES

                  Understood. Thanks

                  • 6. Re: MIN / MAX on DATES

                    Thank you.