6 Replies Latest reply on Feb 26, 2015 4:30 AM by MorkAfur

    Finding Dups?

    MorkAfur

      Title

      Finding Dups?

      Post

      Why doesn't filemaker find duplicates in the highlighted (calculated) field below when I just enter a "!" in Find Mode?

      It says that nothing matches my find criteria.

      Huh?

      Thanks,

      -- m

      dups.jpg

        • 1. Re: Finding Dups?
          philmodjunk

          Perhaps the calculation field's result type is number when it should be text?

          • 2. Re: Finding Dups?
            MorkAfur

            Jeeez, yes, that was it! :)

            ---

            On a "related" note, I have two tables, student and student2. They both have the same structure and data except for one row, which I'm trying to display (limit) via a relationship.

            If I define a relationship where firstname <> firstname and lastname<>lastname, then regardless of which table in the layout I choose to view the data, it's not correct. If I pick the side where there are two records not in the first table, then I see both records regardless. If I choose the point of view the table with all the students, I see just one record repeated.

            I was thinking this would be sort of like an "inner join". Therefore, even in a non-portal situation, if I create a relationship between two tables, I would only see matching records and not the others.

            Clearly I'm missing something.

            Suggestions?

            Thanks Phil!!!!

            - m

            • 3. Re: Finding Dups?
              philmodjunk

              I can't quite picture the relationship that you defined. <> normally stands for "does not equal" and that makes no sense here. Did you mean:

              Table1::FirstName = Table2::FIrstName AND
              Table1::LastName = Table2::LastName

              ?

              If so, keep in mind that you would appear to have duplicates here and your are describing a layout desing where you apparently only display data from the first such matching record from the related table.

              • 4. Re: Finding Dups?
                MorkAfur

                Hey Phil,

                Thanks for your reply....Let me clarify.

                Say you have two tables, ColorA and ColorB with a single "color" field.

                ColorA has this data:

                blue
                green
                purple

                Color B has this data:
                purple

                ------

                In SQL, I think what the FMP relationship graph gives me is this:

                -- inner join
                select colorB.color from colorB, ColorA where colorB.color = ColorA.color

                (which in SQL only shows one row)

                -------

                In FileMaker...

                I set up the relationship as shown in the graph below and set the layout in ColorB to use ColorA as a reference.

                Note that FileMaker shows three record positions and then also (one of the three) the matching color?

                I was thinking it would only show the one matching record so I'm missing something.

                I guess the relationship graph is really not exactly like an "inner join" since it includes blank records for non-matches.

                Look forward to hearing back.

                Thanks,

                -- m

                P.S. If I switched the = to <>, I was thinking the relationships graph would give me something like this:

                ExecuteSQL ( "select * from ColorA where color not in (select color from ColorB)" ; "" ; "" )

                Blue
                Green

                Perhaps I really don't understand the limitations of the relationships graph.

                 

                 

                • 5. Re: Finding Dups?
                  philmodjunk

                  A relationship in the graph is more like this:

                  Select * From ColorA Left Outer Join Color ON ColorA.Color = ColorB.Color

                  No WHERE, NO Order By. Where is analogous to performing a Find. Sort Records similar to Order By.

                  • 6. Re: Finding Dups?
                    MorkAfur

                    Thanks Phil. That makes sense.

                    Appreciate the reply. Perfect as always. :)

                    - m