2 Replies Latest reply on Jun 24, 2010 3:40 PM by philmodjunk

    Has Relationship Filtering Changed in V11?

    Gazzer

      Title

      Has Relationship Filtering Changed in V11?

      Post

      I have a table with all the usual fields and one which contains a boolean value (1=true, 0=false). In another table I have a single field, gTrue, which is global and always contains the number 1. There is a relationship from the TrueFalse field to the gTrue field in the other table.

       

      When I did this in FM8.5 the resulting recordset would only contain records where both tables had a 1 in the joined field (an INNER JOIN).

       

      When I do the same thing in FM11, I get everything from the main table with only values from the second table (gTrue) where the TF field in the main table contains a 1 (an OUTER JOIN, or LEFT JOIN).

       

      I used to use this technique a lot for filtering data (without a portal) and I have not been able to find a way round it. Actually, that's not quite true - I can use a trigger script to do a find, but finds are generally slower and the user can "sholl all records" which defeats the object of filtering.

       

      Any suggestions?

       

      If no suggestions, can anyone confirm that relationships have changed in the manner I have described?

        • 1. Re: Has Relationship Filtering Changed in V11?
          davidhead

          It is not very clear what you are doing here.I can understand the relationship you have set up but not how you are using it.

           

          When you say "When I did this in FM8.5" - did what? You are apparently "filtering data (without a portal)" does that mean that you are using a Go to Related Records using the relationship? If so, it is still confusing when "in FM11, I get everything from the main table with only values from the second table...". So you get all records from the main table? And some records from the second table? How can this be?

           

          All I can suggest at this stage is that you cannot traverse a relationship from a number field to a global field. The field on the right hand end of the relationship has to be indexable. So any GTRR from the main table (with the boolean value) to the second table (with the gTrue) will fail.

           

          As I said, I don't think you have clearly explained how you are using the relationship.

          • 2. Re: Has Relationship Filtering Changed in V11?
            philmodjunk

            In filemaker, relationships do not affect what records are in a  foundset except in one specific script step: Go To Related Records or if you specify criteria in a related record's field while setting up a find.

             

            Thus, I am at a bit of a loss as to how you ever got:

             

            "only ... records where both tables had a 1 in the joined field"

             

            without performing either a find or a GTRR--both of which can be overridden with a show all records command.

             

            If your main table has gTrue and it "always contains the number 1" then the whole issue of outer and inner joins is meaningless here as you have just defined all records in your Main table to have the same value since gTrue is a global field.

             

            This has worked this way since filemaker 3--the first relational version of filemaker.

             

            I'd think you were describing a portal but you said you using this technique without a portal....

             

            Hence my confusion at your post...