Summary

Find with Complex Relationship Triggers error message and fails

Product

FileMaker Pro

Version

Advanced 11.03

Operating system version

Windows 7, Windows XP, SP3

Description of the issue

Even though the following relationship works to display the corrected related data, performing a find, manually or via script, with criteria in the related table's field triggers an error message and the find fails. Set Error Capture fails to suppress this error dialog also.

Steps to reproduce the problem

I started with the classic many to many relationship:
People---------Groups
People::PeopleID = People_Group::PeopleID
Groups::GroupID = People_Group::GroupID

Groups also has these text fields:
GroupCategory
GroupName

I need to list all people in group Alpha, with the group name: George's Alpha Group listed on a layout based on People.

I thus added the following calculation field, cGroupList to People: List ( People_Group::GroupID )
I also added constAlpha defined as "Alpha" //literal text in quotes.

And set up this relationship to an added occurrence of Groups:
People::cGroupList = AlphaGroups::GroupID AND
People::constAlpha = AlphaGroups::GroupCategory

Adding the AlphaGroups::GroupName field to the People layout correctly displays the name of a people record's assigned alpha group.

Expected result

Entering the above stated criteria should produce a found set of 2 records, both of people records that link via the join table to "George's Alpha Group".

Actual result

The find Fails and an error message is displayed.

Exact text of any error message(s) that appear

This operation cannot be performed because one or more of the relationships between these tables is invalid.

This error message cannot be suppressed by Set Error capture.

Configuration information

Workaround

Define a calculation field in People to copy the group name from AlphaGroups. Enter the find criteria into this field instead of the field from the related table occurrence. (I also found that in my actual project, the "alpha" group names are sufficiently unique that I do not have to use a special relationship that filters out groups from other categories but can just specify a group name in the Groups table occurrence.)

I am able to replicate the problem on Windows XP, Mac OS X 10.6.8 and Mac OS X 10.7.2.

This issue appears to have been already reported (the notes are not clear), but I have created a new report using a sample file along with my findings. At this time, continue to use the workaround. I will post again when more information becomes available to me.

According to our Testing department, this is not an issue. In essence, you are performing a query from an indexed field to an unsotred calculation field (no index), and that will fail. FileMaker can yield data when the query is turned around. That is, start with the unstored calculation and move to the indexed field. The vertical bar beside cGroupList in the Relationships graph means it's uni-directional and it's the start point.

it's not simply a matter of the field being unstored/unindexed. Yes, it does use an unstored field in the relationship and yet unstored/unindexed fields are used all the time from the "one" or "Parent" side of a relationship in fileMaker without trouble even when used to query the table via a find request.

If I place FilteredChild::NameField on my layout and perform a find by entering criteria in this field, I do not get an error, even though it would seem to be, in slightly simpler form, a relationship based on the same pattern. (One field unstored, one field stored.)

The difference lies in whether the field is or is not of type calculation that is also unstored.

In the same file, I added a calculation field, cFilter2 as: GlobalField. Since it references a global filed, by definition it will be unstored.

Now I can trip the error if I add FilteredChild2::NameField to my layout and try to enter find criteria into it instead of FilteredChild::NameField.

Thus, an relationship based on an unstored Calculation field cannot be used, but an relationship based on an unstored global data field can be used.

To complete the comparisons, if I use a stored, indexed calculation field for my relationship, find criteria entered into an occurrence of this field from that occurrence will not trigger an error.

Thus, this only fails when the field is both a calculation field AND also unstored. If either detail is not the case, no error occurs.

On a slightly different tack, can you point to any documentation that indicates that this won't work? I don't think this is documented and if I am right, at the very least, we have a documentation issue.

@Gianandrea Gattinoni

Try entering find mode and then enter some criteria in the field labeled Alpha Group and see what happens. If you enter criteria in the lower field, "groupname" it works, but trips an error when criteria is entered in the upper field.