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

    Finding Dups?



      Finding Dups?


      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.



      -- m


        • 1. Re: Finding Dups?

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

          • 2. Re: Finding Dups?

            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.


            Thanks Phil!!!!

            - m

            • 3. Re: Finding Dups?

              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?

                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:


                Color B has this data:


                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.


                -- 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)" ; "" ; "" )


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



                • 5. Re: Finding Dups?

                  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?

                    Thanks Phil. That makes sense.

                    Appreciate the reply. Perfect as always. :)

                    - m