I've just installed my new FM 8.5 adv. I've been previously using
version 5.5. As I can see from this breif encounter they've added many
nice features, but I've also found some inconsistencies with the older
versions (I have several apps designed in 5.5 which do not work in new
version). Here is my bigest problem:

I have three related files (three tables in different files as it was
the only possibility in older versions).
File A is related to file B and file C is related to file A.
File A calculates points in it's own records based on data in file B.
It has a claculation similar to this:
If(B::data1 = A::data2;3;0)
Relationship to B is conducted using A::code. Now if I leave all of
the fields in the new record (file A) empty (including the code and
data2), I get the result 3 instead of 0!
What actually happens? Since A::code (the relationship field) is empty
it does not have any related records in B, and hence B::data1 is empty
(or esencially NULL). Since I've left the A::data2 field also empty,
we have the comparasion NULL=NULL and FileMaker calculates this to
TRUE! How can this be? Previously it worked just fine.

The next stage of the problem is the relation to file C. File C is
related to file A using the field, let's say C::code. I actually need
the result of the above calculation from file A in file C. Now if
C::code does not have a match in file A, what do you think the result
would be? Empty? 0? NO! It will be 3!!! Looks like if there is no
match in A, FM creates a virtual empty record, and then (because of
the NULL=NULL problem) it returns a value of 3 for the non existent
record from A!

I've fixed this using an IsEmpty function, but still...It shouldn't
work like that.

I hope somebody understood what I was trying to say. The fundamental
problem is the comaprison NULL=NULL. How can this be TRUE?

BTW Do not calculate if all referenced fields are empty does not have
any effect.

D.

On Apr 14, 5:12 am, Dalibor Kusic <dalibor.kusicSPAMBUS... (AT) zg (DOT) t-
com.hr> wrote:

Hmmm... I'd swear I already posted a response this, but I'm not seeing
it... so its possible I managed not to send it.

If it showed up for you great, and I'll spare myself writing the
lengthy explanation.

If not, let me know, and I'll write more.

Bottom line, filemaker doesn't match NULLs in relationships. No worry
there.

However, when you directly evaluate comparisons on null fields, or on
fields in relationships that have no related records you are not
actually comparing NULL values. Filemaker calculations automatically
convert field references to the data type it needs. Thus if you define
a calculation

If(B::data1 = A::data2;3;0)

It will convert the value in B::data1 to its type (e.g. number or
text), and then convert a:data2 to the same type. So...

if B::data1 is a number, and it is empty ("NULL") filemaker will
convert it to the value 0.
Then it will evaluate a::data2, which if there are no related records
comes back as "NULL", which will also be converted to 0.

Then it compares if ( 0=0) which of course is true.

If b::data1 is a text field, and equivalent process occurs, except
that this time it converts NULL to the empty string, and finds that ""
= "" is true.

---
Hope that helps; If you need more help let us know.

cheers,
Dave
The solution is as you determined to use isempty (or some other method
like count() ) to determine that there is in fact at least one related
record before you reference the external field.

On Apr 15, 8:02 pm, "d-42" <db.pors... (AT) gmail (DOT) com> wrote:

Count() isn't so good due to wasteful overhead. isValid() is better.

On Apr 16, 7:09 am, "Paul Bruneau" <p... (AT) ethicalpaul (DOT) com> wrote:
Yes, count has the potential for overhead.

IsValid is ...unreliable? There's another thread about this right
now.

The FM documentation doesn't explicitly support this use case for
isValid; it only talks about data type mismatches, references through
broken relationship definitions, or to deleted fields.

I noted that I have direct experience with isValid returning "1" even
when there are no related records. I couldn't demo this in a whipped
up 'test database', but I've seen it happen a number of times in real
world systems.

Howard weighed in that FM has weighed in directly on the subject,
advising against using isValid for that.

It seems the current consensus is to use:
not IsEmpty(relatedTableOccurrence::PrimaryKey)

cheers,
Dave