Change in Max() function in v8 ??

[IanC]

Hi

Opening an existing solution in FM8 I came across the following problem.

In FM7...

My table has a number calculation field 'Max(lines::value)' - it returns the maximum value of a field in the related table. The calculation is unchecked for 'do not evaluate if all referenced fields are empty'. Therefore if there are no related records it returns a 0.

My script searches this calculation field with the criteria '<1' which therefore returns all records without related values

in FM8...

Opening this file in v8 the zero's have disappeared from the calculated field, thus I get no results with my '<1' search and have to do an '=' search to get my found set.

I've created a test file and attached it to this message so you can test this for yourself

I wasn't aware of any structural changes in v8, it all seemed quite cosmetic - is there a comprehensive list of changes anywhere?

Thanks

Ian

p.s. I realise that perhaps the Max calculation should read 'Max(0 ; lines::value) to force a default value, and in fact this is a workaround, but it's the change in the result between the two versions that I am highlighting.

test.zip

Edited September 4, 2005 by Guest
p.s. added

[IanC]

I'm also finding this problem with the MIN and SUM functions. Attatched to this message is another test file - open it in v7 and you will see 3 columns of mainly 0's. Open it in v8 and they have all been replaced by blanks.

Does anyone else have a problem with this or is it just something on my machine??? (OS X 10.4.2)

Ian

test02.zip

[Osman]

I download and try, result is same as yours.

[Michael Reade]

Here are a couple of screen shots of what I'm seeing. It all seems to be working OK.

BTW, I'm on 10.4.2 also.

Hope this helps,

Michael Reade

[IanC]

Is that a v8 or v7 screenshot?

I get that result in v8, but in v7 all the blanks are replaced by 0's

[Fenton]

It appears the FileMaker 8 is returning the more correct value, that if no records match the relationship, the result is null; not 0 as in 7. But, as you say, it makes certain syntax of Finds not work as (perhaps) expected. You might expect that Find <1 would return the blank records. But it doesn't. This is actually correct however, if you consider that the blank records are null, which is no value at all, so they can't be compared to a number value.

You can Find them with "=", which is the special character for finding null values. Or you can Find for >0, then Omit.

This is actually the way that it works for regular fields, and has for some time. You cannot Find for 0 in a blank number field in 7 either. So now calculations follow the same rule.

Similar logic for a function like Min(). If there is a 1 in one related record, and another is blank, the Min() is 1. But if there is a 0, then it should show 0. In 8 it correctly shows 0 as the Min(), and blanks in records with no related, or related blank values.

But in 7 it shows 0 in that record, and all the other null or blank records. Or, if you set the field number format to not show zeros, both the 0 and all the blank records are blank. Neither of these results reflects the real data. Perhaps it fits the current business logic, but it is technically incorrect, as it cannot show you which data was entered.

[Mark Scott]

I agree with Fenton that the behavior in 8 is more correct. It seems that FileMaker may be moving closer to proper (perhaps I should say doctrinaire) handling of NULLS (unknown values). A tenet of the relational model is that NULLS (which may be represented in a database as an empty field) are handled as a unique situation which is taken to mean "unknown." As such they should not be treated as zero in any operations. FileMaker has long handled this correctly in some situations (e.g., empty fields are correctly excluded from averages, etc.), but not in other situations, (For example, a calculation field, A, which is defined a B + C + D, should remain empty if ANY of the three fields B, C, or D is empty. Instead, if B=3, C=4, and D is empty, FileMaker returns 7 for A. Note that checking "Do not evaulate if all referenced fields are empty" doesn't correct this situation. This behavior would drive relational purists nuts.

I couldn't remember which of Codd's rules deals with this, but Google to the rescue and I came up with this (copied from www.frick-cpa.com/ss7/Theory_RelationalDB.asp):

Codd's Rule #3. Nulls are Treated Uniformly As Unknown

- Null must always be interpreted as an unknown value

- Null means no value has been entered; the value is not known

- 'Unknown' is not the same thing as an empty string ("") or zero

- EXAMPLE. If you pick up an item in a store and the price is not marked, the price is unknown (NULL); it is not free 

- If not handled properly, nulls can cause confusion in your database

- EXAMPLE. If you search for all of the authors whose home state is not California, the results will not include any authors with NULL in the 'state' column. You asked for authors where the state was NOT California and NULL means 'unknown.' A NULL value for 'state' may mean that the state is California and it may mean that it is not California; you just don't know.  Because the database engine can't tell for sure whether the state is not California, a record with NULL will not be returned

- Nulls propagate through arithmetic expressions (e.g., 2 + NULL = NULL)

- Comparing a null to any value, including itself, returns NULL  

Just a couple additional comments to put this into FileMaker perspective: It is regarded as OK (by most, but not all) for a DBMS to use an empty field to represent a NULL (FileMaker's approach), so long as the database engine handles those correctly (a goal not completely attained by FileMaker). Secondly, there is a distinct entity that is refered to as an "empty string," (e.g., "") that is supposed to be distinct from a NULL. An example would be a Middle Name field. For some records, a middle name may be unknown (e.g., if you forgot to ask), and would be correctly represented as a NULL or empty field, whereas in other cases you may have remembered to ask and been told "I don't have a middle name." The latter should properly be entered as an empty string and is not a true NULL/unknown. FileMaker does not handle this distinction and does not really support the empty string concept, but it is not alone, as even some SQL-based database management systems also fail on this.

In general, FileMaker is not too bad in handling NULLS, although a "do not evaluate if ANY referenced field is empty" option for calculation fields would be a fantastic addition (and should probably be checked by default). Note that the whole issue of handling NULLS continues to be a controversial issue in relational theory (see, for example, www.firstsql.com/inulls.htm and www.firstsql.com/idefend.htm, both thanks again to a quick Google search).