Max() and hyphen

[Oldfogey]

I have a table with a text field ('CF Year') containing yyyy-YYYY values. These are year ranges such as 2004-2006, 2003-2004, etc. Can anyone explain to me why the expression Max(History::CF Year)returns 20042006, ie no hyphen?

And also why (text) '20042006' is greater than text '2008'?

I've read all I can find on hyphens and words, etc but this looks as though FMP refuses to believe my fields are text.

[Genx]

Because, Max is an aggregate function - aggregate functions deal with numbers. 20,042,006 is a larger number than 2,008.

What do you want to know - if 2004-2006 has more days in the period than 2008, if 2004-2006 is more recent than 2008, if 2004-2006 has more characters than 2008?

[comment]

I am also not too clear what result you expect to get when using Max(History::CF Year), but since you are clearly violating the 'one fact per field' rule, I would suggest you use separate fields for start and end. Then everything will become easier.

[Oldfogey]

First, Genx, Max() applies to text fields as well as number fields. I just want to get the latest year.

Second, Comment, I would love to do what you suggest, Unfortunately I'm dealing with someone who has been using (misusing?) all kinds of fields in this way. (Perhaps my favourite is Graduation Year, which can be 'St' for Student.) I'm using single years now but I have to deal with history. Also, I really cannot see anything inherently wrong with a field containing a range even though I'd rather not use it.

I can use a workaround but I'm still curious as to why the hyphen is 'disappeared' and why the comparison appears to ignore the field type.

[Lee Smith]

My guess is that the Calculation engine strips out operators.

Lee

[comment]

Max() applies to text fields as well as number fields.

Not exactly. That is, it does work on text fields or text values, but the action is purely numerical. Examples:

Max ( "19" ; "2" ) = 19

Max ( "a9" ; "z1" ) = 9

Max ( "a" ; "z" ) = ""

I believe that also covers your last question.

I really cannot see anything inherently wrong with a field containing a range

One thing wrong with it is that it's d*mn hard to "get the latest year" (whatever that means within the context of a range*). Can't you extract the starting year of the range by Left ( Range ; 4 ) and use the Max() function on the result?

---

(*) What's the latest year among these ranges:

2001-2010

2006

2005-2007

Edited February 20, 2008 by Guest
********, if it's good enough for 'Gone with the Wind', it oughta be good enough for this forum!

[Oldfogey]

Not exactly. That is, it does work on text fields or text values, but the action is purely numerical. Examples:

Max ( "19" ; "2" ) = 19

Max ( "a9" ; "z1" ) = 9

Max ( "a" ; "z" ) = ""

I believe that also covers your last question.

Sure does! Thanks for that. I don't think I've ever tried to use Max() on text fields before.

One thing wrong with it is that it's d*mn hard to "get the latest year" (whatever that means within the context of a range*). Can't you extract the starting year of the range by Left ( Range ; 4 ) and use the Max() function on the result?

---

(*) What's the latest year among these ranges:

2001-2010

2006

2005-2007

You examples are far more complex than my data. There are only 4 or 5 ranges which don't quite overlap. I just want to get rid of them historically because once I get the 2008 mob in, there'll be no problem. All I needed in your list is that 2008 is bigger than any of them. The 2001-2010 is not relevant; cannot/doesn't exist. (Actually, the ranges do overlap. These are lengths of time for accreditation and they've used 1998-2000 followed by 2000-2004, which made my hair curl. I tried just using the last year in the range, which is all that really matters for historical purposes, and all hell broke loose.)

For obvious reason, I agree wholeheartedly on the difficulty. I was just baulking at the word 'rule'. Ta muchly, anyway. And I also think this forum should allow the language I have used on numerous occasions when dealing with this @%!%$!% DB. Is 'Oh, bother!' OK?

[Genx]

First, Genx, Max() applies to text fields as well as number fields.

To be fair, I didn't say it couldn't be used on text fields - i said it was an aggregate function that deals with numbers.

[Oldfogey]

Because, Max is an aggregate function - aggregate functions deal with numbers. 20,042,006 is a larger number than 2,008.

Sorry, Genx, but you did say that aggregate functions deal with numbers. Not so, I'm afraid. Try List() and Count().

This (Max) is just one more case of FMP's inconsistency or poor help. My fifth step in any problem is to refer to the FMP Help and all too often it is badly named!

There is nothing that I can find in the FMP documentation to suggest that Max() cannot be used with text - quite the contrary.

Edited February 22, 2008 by Guest

[Genx]

To be fair, I didn't say it couldn't be used on text fields - i said it was an aggregate function that deals with numbers.

Read Carefully. You can also use "+" with text e.g. "abc1" + "defahhg1hhah2" = 13 ... note the operator here deal's with numbers, just like the max function

Edit: Hmm, I guess I did write that "Aggregate functions deal with numbers". Mind you i'm still uncertain how you could expect a function called "Max" to be applied to a non numerical value.

Edited February 24, 2008 by Guest

[Oldfogey]

Mind you i'm still uncertain how you could expect a function called "Max" to be applied to a non numerical value.

Come on, Genx! You sort text fields. It is perfectly logical to make comparisons between text values and, by the same token, perfectly logical to expect to be able to find a maximum text value.

[comment]

It's not so simple. The rules for finding the maximum text value are not the same as the ones for finding the maximum numerical value (that's what started this thread). What would you have the Max() function do when there are both text and numerical values to aggregate, e.g.:

Max ( 20 ; "100" ; "a3" )

[Oldfogey]

I think it is very simple. It would simply require that Max() arguments must be of the same type.

Your example is quite unrealistic; can you suggest a situation where you would want to compare Number and Text fields?

Max("20"; "100"; "a3") seems quite legitimate to me. The result would depend on the sort sequence defined between numerics and text characters; the result is either "a3" or "20".

Although I've never used it, I can imagine situations where you might want to find Mex([a set of phone numbers]). Phone numbers are usually in text fields - unless you're a masochist.

I have no hang up about needing a Max() function that works on text fields. My beef is that FMP should not say it returns text.

[comment]

The example is unrealistic on purpose, to show that at some point a decision needs to be taken which rules are enforced. Basically, you are speaking of a different function, IN ADDITION to the current Max().

BTW, field types have very little to do with this - what matters is the type of values being compared, regardless of where they're coming from. Requiring all values to be of the same type may seem like a good idea - but what if they are not? Say I have 5,000 records with numeric entries, except one of them has a trailing space - what then?

[Oldfogey]

Basically, you are speaking of a different function, IN ADDITION to the current Max().

I agree but that is not what FMP says.

In fact, FMP has no stated rule about field or data types.

Say I have 5,000 records with numeric entries, except one of them has a trailing space - what then?

According to FMP the field with invalid data will be ignored. I quote -

[Max()] "Returns the highest valid value ....".

Would you expect to get a sensible result from Max(1/4/2008; 345678234)? I wouldn't. But there is nothing in the FMP documentation to say you can't or shouldn't do this.

[comment]

Well, now we are into semantics. One could argue that "highest" cannot refer to text.

Would you expect to get a sensible result from Max(1/4/2008; 345678234)?

Yes, I would - since both are numbers:

Max ( 1/4/2008 ; 345678234 ) = 345678234

and

Max ( 1/4/2008 ; 0 ) = 0.00199203187251

However:

Max ( "1/4/2008" ; 345678234 ) = 345678234

and

Max ( GetAsDate ( "1/4/2008" ) ; 345678234 ) = 345678234

Same result - but for different reasons.

In contrast:

Max ( "1/4/2008" ; 0 ) = 142008

Max ( GetAsDate ( "1/4/2008" ) ; 0 ) = 733133 (if using d/m/y format)

[David Jondreau]

“Why is the alphabet in that order? Is it because of that song?"

[Oldfogey]

D J, enough frivolity! The alphabet is in that order because that is the way the ASCII codes were found on the Rosetta Stone.

[Oldfogey]

Firstly, my reference to what Max() returns was in response to your example with invalid data, which a number followed by a space is (in a number field) and which Max() rightly ignores. Nothing to do with semantics.

One could argue that "highest" cannot refer to text.

So we cannot have the higher of two names? Or the highest of three?

Regarding the use of Max(1/4/2008; 345678234), yes, you would get a result but what would it mean? Perhaps if you were doing some extremely abstruse date calculations it might be relevant, especially as 345678234 is outside FMP's date range.

I really cannot understand what you are on about. My main beef is that FMP implies that Max() can take Text arguments but in fact it doesn't, or doesn't do it properly. All you have done is provide a string of examples using Max() with arguments of different data types. I have never suggested that Max() should handle different data types. As you yourself pointed out, it would be rather tricky to define such a function. (I generally try to avoid making use of FMP's weak typecasting because it does provide answers but sometimes not what you might expect.)

Incidentally, SQL, LISP and DB2 all cater for Max() with text/string arguments.

[comment]

The alphabet is in that order because that is the way the ASCII codes were found on the Rosetta Stone.

Actually, that's not too far from truth:

http://en.wikipedia.org/wiki/Alphabet#Alphabetic_order

Edited February 29, 2008 by Guest

[comment]

I really cannot understand what you are on about.

Let me try and make it a bit clearer. This is how the Max() function works: it implicitly converts all its arguments into numbers. Therefore, when you write:

Max ( a ; b ; c )

Filemaker actually reads it as:

Max ( GetAsNumber ( a ) ; GetAsNumber ( b ) ; GetAsNumber ( c ) )

Arguments that cannot be converted into numbers are ignored. Or, if you like: empty arguments are ignored - and arguments that cannot be converted into numbers ARE empty.

A number followed by a space (which is text) is NOT ignored. It is converted into a number (by dropping the space).

A date is converted into a number (the serial number of the day starting with 1/1/0001). Therefore, the Max() function CAN work with dates - and it can also work on a mixture of dates and numbers. The example:

Max ( GetAsDate ( "1/4/2008" ) ; 345678234 ) = 345678234

shows just that. Of course, you cannot convert the result to a valid date, but that's no concern to the Max() function. It ONLY deals with numbers.

I agree that the help on the function could be clearer. At least the part that says that the function returns data types "text, number, date, time, timestamp" is incorrect, IMHO. I am quite convinced the function ALWAYS returns a number.

And no, I don't think you can have the higher of two names. At least I wouldn't think that "Betty is higher than Adam" refers to their alphabetical order. Filemaker is forgiving in this aspect, and lets you compare two text values by using < and other comparison operators. Alas, this forgiveness does not extend to the Max() function.

[Genx]

Why not just sort using a cartesion product relationship with you're field descending and then take the first value? I haven't tried it but i assume it would work...

I haven't really read the FM doc from cover to cover, so I can't comment on it's quality, but if you have an issue... Go talk to FM.

-- sorry Michael, didn't see your post.

Edited February 29, 2008 by Guest

[David Jondreau]

And no, I don't think you can have the higher of two names. At least I wouldn't think that "Betty is higher than Adam" refers to their alphabetical order. Filemaker is forgiving in this aspect, and lets you compare two text values by using < and other comparison operators. Alas, this forgiveness does not extend to the Max() function.

Right! Adam sorts before Betty (because of that song?), but if you asked someone what is more or higher Betty or Adam, you'd get a quizzical look, because the question makes no sense.

Man, you're good.

I still can't get over you pulling out the Last() function out on another thread. Never seen that before.

[Oldfogey]

Filemaker actually reads it as:

Max ( GetAsNumber ( a ) ; GetAsNumber ( b ) ; GetAsNumber ( c ) )

Arguments that cannot be converted into numbers are ignored. Or, if you like: empty arguments are ignored - and arguments that cannot be converted into numbers ARE empty.

A number followed by a space (which is text) is NOT ignored. It is converted into a number (by dropping the space).

Sorry, Comment, but that is pretty obvious but that is not what FMP says. Furthermore, it makes the FMP reference to invalid values rather redundant, doesn't it?

I would not, nor have I even implied, that one would say 'Betty is greater than Adam'. However, it is absolute nonsense to say you cannot compare "Adam" and "Betty". If that were the case,you could not sort text fields. Furthermore, it is quite valid to have a statement including ("Adam" > "Betty"), whose value is 0.

I am quite convinced the function ALWAYS returns a number.

At last we agree. I just wasn't aware of it and I think it is an oversight.

Edited March 2, 2008 by Guest

[Oldfogey]

Sorry, Genx, for not responding.

I solved the DB problem ages ago by just using conversions to Number - much simpler than adding yet another relationship. The current discussion is about FMP and the Max() function.

[comment]

it is absolute nonsense to say you cannot compare "Adam" and "Betty". If that were the case,you could not sort text fields.

We are rehashing the same thing over and over. I thought we already agreed that sorting/comparing text is a different operation.

Furthermore, it is quite valid to have a statement including ("Adam" > "Betty"), whose value is 0.

You might want to examine how this works when you mix numbers and text.

The bottom line is that the Max() function works the way it works, and the help says what it says. There's not much we can do about either.

[Oldfogey]

The bottom line is that the Max() function works the way it works, and the help says what it says. There's not much we can do about either.

Agreed.

Comment, I would never dream of arguing with you about the way FMP operates.

Ever since I raised the question of why Max() did not operate the way FMP implies it does, I've been besieged with implications that I am some kind of nut for thinking that you can find the maximum of a set of text values (even though a few well-respected packages do just that!) or that I want to compare, or find the maximum of, different data types.

[comment]

Ever since I raised the question of why Max() did not operate the way FMP implies it does, I've been besieged with implications that I am some kind of nut for thinking that you can find the maximum of a set of text values (even though a few well-respected packages do just that!)

I don't think anyone meant to imply that, least of all me. I believe it's a legitimate request - it just cannot be achieved using Max().

I do think your criticism of Filemaker in this regard is a bit harsh. There are prices to pay for ease of use, and this seems to be one of them. Other databases, I think, enforce data types much more strictly. I am not sure they would let you type text into a "number" field, for example.

Another example is text sorting and indexing. Filemaker lets you define a language for each text field, and then uses it to sort the field (see example here). This would be much harder to do, if text was always considered as nothing more than a series of the underlying ASCII/Unicode values - as I believe other packages do. But it also means that cryptographic functions are difficult to implement in Filemaker, since most algorithms are based on those numeric values.

So ultimately, it's a question of balance - win some, lose some.

or that I want to compare, or find the maximum of, different data types.

Now that one I am not too sure of. :

[Oldfogey]

or that I want to compare, or find the maximum of, different data types.

Now that one I am not too sure of.

I am; it's nuts. My last post was ambiguous. I have never suggested such a thing but most of your (and others') comments refer to just that.

I have no quarrel at all with FMP not providing a Max() that handles text. My beef is that they imply the current one does.

The issue with weak vs strong typecasting is very simple. If you are working with a strong typecasting 'language' it should be a bit weaker and if you are working with a weak one, it should be a bit stronger. I would not want to change FMP in that area.

Edited March 20, 2008 by Guest

[comment]

I think by now this has become an autopsy of a dead horse, but just to put your mind at ease:

I believe your complaint that the help does not describe correctly what Max() does is a legitimate one. My only issue with it was where you implied (or seemed to imply) that the actual fault was with the function itself, not with its description. Because we are all agreed that the function cannot be made to do what the help describes, without making radical changes to the application's approach to data typecasting.