Find with Complex Relationship Triggers error message and fails
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::PeopleID = People_Group::PeopleID
Groups::GroupID = People_Group::GroupID
Groups also has these text fields:
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.
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".
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.
A copy of a demo file demonstrating this issue may be downloaded from: http://www.4shared.com/file/ubfTTkhG/FindErrorExampleFile.html
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.)