8 Replies Latest reply on Aug 4, 2012 2:16 AM by Sorbsbuster

    'Max' of text field doesn't work in related records?

    barefootguru

      Title

      'Max' of text field doesn't work in related records?

      Post

      The manual for the 'max' function states 1. it works for text fields, and 2. it works over related records.

      However from my testing I've found 'max' works for text fields within the same record, it works for numeric fields in related records, but not text fields in related records.

      This would be consistent with the 'max' summary field which also can't process text fields.

      Sounds like the manual needs updating?

      Edit:  FMPA 11 and 12.

        • 1. Re: 'Max' of text field doesn't work in related records?
          schamblee

          I did some test and got it to work.  I have a calculation field with the calcualation Max(field1;field2;test::field3)  and then click on the storage options and clicked do not store - recalculate as needed.  Maybe this will help.  field 1,2,and 3 were text fields.  field4 was my calculation field.

          • 2. Re: 'Max' of text field doesn't work in related records?
            barefootguru

            Thanks for the response, but I think you'll find the max function is returning values for field1 and field2 but not test::field3

            (Also you shouldn't need to turn storage off--FM won't be able to store the result of related records.)

            • 3. Re: 'Max' of text field doesn't work in related records?
              schamblee

              Yes, it did return the correct results. (I tested before I answered)  Filemaker doesn't need to store this calculation field because it calculates it as needed.  I'm using filemaker 12.   

              • 4. Re: 'Max' of text field doesn't work in related records?
                philmodjunk

                Which use of Max fits what you are trying to do?

                Max ( relatedTable::textField )

                or

                Max(field1;field2;test::field3)

                With the second expression, the results can be confusing. You may have any number of related records in test, but the expression will only refer to Field 3 of the "first" such related record. Additional related records in the test table will be ignored.

                In the first expression, the maximum value in field3 of all records in RelatedTable that are related to your current parent record will be returned.

                • 5. Re: 'Max' of text field doesn't work in related records?
                  barefootguru

                  OK, apparently a picture's worth a 1000 words, so here we go:

                  • 6. Re: 'Max' of text field doesn't work in related records?
                    philmodjunk

                    At first I thought maybe field 2 was a number field, but my tests in Fmp 11 also match yours. This should be reported in Report An Issue--see tab at top of this screen. That's were bugs should be reported. You can include a link to this thread to save typing.

                    I do know of two work arounds that can be used to return the max text value from a related table:

                    1. Use a sorted relationship to sort by field2 in descending order, then table2::Field2 will return the maximum value without use of the max function.
                    2. A single row sorted portal can also display the max text value.
                    • 7. Re: 'Max' of text field doesn't work in related records?
                      barefootguru

                      Thanks PhilModJunk, issue submitted.  I was actually writing a script to combine duplicate records using a self-join, so ended up taking a completely different approach.

                      • 8. Re: 'Max' of text field doesn't work in related records?
                        Sorbsbuster

                        Triggered by your previous post, Phil, I thought this may work (all fields defined as text or text result, with text values):

                        Max ( Max ( Field1 ; Field2 ) ; Max (ChildTable::Field3 ) )

                        But it doesn't - it always ignores the related field, even if sorted on the relationship.
                        Showing the sub-calculation Max (ChildTable::Field3 ) on the parent table always shows blank.

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

                        Leaving the fields defined as text and simply putting single digit numeric values in all fields, including the related records, makes all calculations work (apparently) correctly.

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

                        Showing the calculation Max (ChildTable::Field3 ) on the parent table and entering 30 , 40, and 200 in the related records, I would expect 40 to be returned as the Max text value.  But it isn't - the calculation works as if they were all true numeric fields, and returns 200 as the max value.

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

                        Yet if you make the 'numeric' values in all the text fields:

                        Parent Field1 = 6
                        Parent Field2 = 7

                        Related Child1 Field = 30
                        Related Child2 Field = 40
                        Related Child3 Field = 200

                        The calculation of Max ( Max ( Field1 ; Field2 ) ; Max (ChildTable::Field3 ) ) returns 7, not 200.

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

                        It looks like:

                        Max ( RelatedTextField ) does not handle text in that field at all, and when you enter numeric values it treats them as numbers.

                        Max (TextField1 ; TextField2 ) works correctly.

                        Max (TextField1 ; TextField2 ; Max ( RelatedTextField ) ) treats the value returned from the related calculation as Text.  (Yet it will only evaluate the related fields if the values are numbers!)