AnsweredAssumed Answered

Find with Complex Relationship Triggers error message and fails

Question asked by philmodjunk on Aug 29, 2011
Latest reply on Jan 11, 2012 by philmodjunk

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

A copy of a demo file demonstrating this issue may be downloaded from:  http://www.4shared.com/file/ubfTTkhG/FindErrorExampleFile.html

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.)

Outcomes