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.
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.)
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.
Which use of Max fits what you are trying to do?
Max ( relatedTable::textField )
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.
OK, apparently a picture's worth a 1000 words, so here we go:
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:
- 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.
- A single row sorted portal can also display the max text value.
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.
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!)