Relational filters don't work past one table?

[cobra]

Hi Everyone,

There seems to be a bug in FileMaker, where <> (a not equals filter...and probably others as well) does not work for parent > child > grandchild relationships.

To reproduce the problem do the following:

1. Create the following 3 Tables:

Parent (1 field)

ParentID AutoEnter Serial Number, Unique

Child (3 fields)

ParentID Number

ChildID AutoEnter Serial Number, Unique

GrandChildFilter Number

GrandChild (2 Fields)

GrandChildID AutoEnter Serial Number, Unique

ChildID Number

2. Then, create relationships between the three tables as follows:

Parent <-> Child

Parent::ParentID = Child::ParentID

Child <->GrandChild

Child::ChildID = GrandChild::ChildID

Child::GrandChildFilter <> GrandChild::GrandChildID (that's the not equals sign for Mac users)

3. Click ok to exit Define Database and go to the Parent layout. Create a portal to Grandchild.

4. Create the following records:

a) Create a record for Parent

Create a record for Child and enter the ParentID created in step a).

c) Create two records on GrandChild, entering the ChildID (created in step for each.

5. Go back to the Child Layout and enter one of the GrandChildIDs into Child::GrandChildFilter.

6. Go to the Parent Layout and view your portal. You'll note that the filter is working as expected.

THE BUG:

Now, create a second Child record with the same ParentID as the first. Give it some GrandChildren and set the Child record to filter one of them. If you view the portal on the Parent Layout, you'll note that the filter has stopped working.

Now, because the filter works when only one Child record exists, I'm assuming that this is a FileMaker bug. It'd be great if someone can confirm this for me.

The work-around here is to bypass the Child Table (by creating a ParentID field and setting its value in the Grandchild table). However, this is not a great work-around, as you have to avoid putting the ParentID into records that you want to filter out of your portal.

Hope this helps someone!

[Ender]

Thanks cobra,

From your steps I was able to reproduce this problem in OS X. It's an unusual combination of relationships, but I can see that it could eventually be a stumbling block (I haven't thought about where to use <> in relationships yet.)

I hope you report this to FMI so they can work it out.

[cobra]

Thanks very much for confirming that for me, Ender! Yup, I've reported the problem to FileMaker.

The need for the filter is purely for ease of use (edit: oops...and data integrity as well!). There is one record in the Grandchild table (for each particular Child record) that users should never see (or modify).

Essentially, I wanted to present the user with all the Grandchildren for a Parent without showing those that needed to be excluded. The relational filter (had it worked) would have been the standard way of achieving this.

Thanks again!