4 Replies Latest reply on Jan 11, 2012 4:24 PM by philmodjunk

    Find with Complex Relationship Triggers error message and fails

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

        • 1. Re: Find with Complex Relationship Triggers error message and fails
          TSGal

          PhilModJunk:

          Thank you for your post.

          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.

          TSGal
          FileMaker, Inc.

          • 2. Re: Find with Complex Relationship Triggers error message and fails
            TSGal

            PhilModJunk:

            I apologize for the late reply.

            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.

            TSGal
            FileMaker, Inc.

            • 3. Re: Find with Complex Relationship Triggers error message and fails
              GianandreaGattinoni

              Hi,

              I have downloded the file and it seems working without any problem.

              Windows 7 and FM 11.03

              attached is the composite image of the results

              Gianandrea

              • 4. Re: Find with Complex Relationship Triggers error message and fails
                philmodjunk

                @TSGal,

                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. 

                Consider this example relationship:

                Parent::ID = FilteredChild::ID AND
                Parent::GlobalField = FilteredChild::NameField

                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 add a relationship using a new occurrence of Child:

                Parent::ID = FilteredChild2::ID AND
                Parent::cFilter2 = FilteredChild2::NameField

                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.