11 Replies Latest reply on Aug 9, 2013 11:58 AM by Ween

    Find duplicate between two fields

    Ween

      Title

      Find duplicate between two fields

      Post

      Hello all and thank you in advance.

      I am by no means a filemaker guru but I need some help. I have a database for a part number cross reference. The problem I run into is where a part number says to use (example) "F". But when I go to "F" this data says to use "L". I want to be able to find out where this happens in my database. I am using Filemaker Pro 7.0

      Picture_1.png

        • 1. Re: Find duplicate between two fields
          philmodjunk

          I'm afraid I can't quite follow what you want to do here.

          Do you want to find duplicate values in field 2? If so, enter find mode and enter an exclamation point in field 2, then perform the find. Sort the records by field 2 so that duplicate values are clustered and you're done.

          If that's not what you want here, please explain further...

          • 2. Re: Find duplicate between two fields
            Ween

            Thanks Phil

            No, I'm trying to find if there are duplicates between field 1 and 2. as in the example see how "B" says to use "F" but down in the chart "F"say t use something else. I hope I'm making sense.

            • 3. Re: Find duplicate between two fields
              philmodjunk

              What I can't follow is the criteria that determines which records you want to find. I see B says to "use F" and F says to "Use L". What is F supposed to say?

              I suspect you've generalized your actual situation into an abstract example. Real examples are much easier for others to analyze and sometimes we see a way to do what you want with your real situation that's completely outside the "box" of your original description.

              • 4. Re: Find duplicate between two fields
                philmodjunk

                After further thought, I think you are trying to identify interchangeable parts, in which case F should have "Use B" in field 2. If so, you can set up a self join relationship to find cases where this error has occurred.

                Please confirm and if I have guessed right, I'll expand on that concept tomorrow unless another forum member responds first.

                Follow up question: Assuming that I'm right, what do you do if you have 3 or more interchangeable parts?

                • 5. Re: Find duplicate between two fields
                  Ween

                  Almost, B says to use F But F says to use L, so basically B should use L. I am trying to find where this happens within the databse. I basically have to drill down two times to find the correct part number.

                  • 6. Re: Find duplicate between two fields
                    Ween

                    Alright, I think I can explain this better. As seen below Part#0-1013 crosses to MD972748. I just want to make sure that MD972748 doesn't exist anywhere else in the database other than the "Cross#" colum because you can see it is in the "Cross" colum twice and that's okay. It cannot exist in the "Part#" colum. I want Filemaker to look at the "cross#" and serach all of field "Part#" to make sure it is not in there as well. Please keep in mind I have over 56,000 entries in this database.

                    PART# CROSS#
                    0-1013 MD972748
                    0-105603-2 CT-941
                    0-1066 MD972748
                    0-14-4 W16S-U
                    0-147 W14-U
                    0-149 W14-U
                    0-14G52 W16EX-U
                    0-14R8 W16S-U
                    0-18-4 L14-U
                    • 7. Re: Find duplicate between two fields
                      philmodjunk

                      OK, a "self" relationship can do this.

                      1. Open Manage | Database | Relationships
                      2. Select the table box for this table by clicking it
                      3. Click the button with two plus signs to make a 2nd table box ( called a table occurrence ) for this same table.
                      4. Link them in this fashion: Parts 1::Cross# = Parts 2::Part#
                      5. Place the Parts 2::Part# field on a layout that's based on Parts 1.
                      6. Enter find mode
                      7. Enter an * in the Parts 2::Part# field
                      8. Perform the find

                      All listed records will have a Cross# entry that also occurs in the Parts# field of another record.

                      • 8. Re: Find duplicate between two fields
                        Ween

                        WOW, YOU ROCK PHIL.

                        It worked. You saved me years of work. Thank you so much.

                        • 9. Re: Find duplicate between two fields
                          Ween

                               Hi Phil

                               I hope all is well with you. This formula helped me out greatly. I am comoing across a problem now. I have below a senareo were I need help.

                               As stated in this thread I need to find part numbers athat have double cross references in them. The formula you gave me works but when I have multiple numbers pointing to the same number I only get one of the multiple numbers in my result. I hope I am making sense.

                               Using the example above, when I get the results of the data it only shows me the first number (1500127-52). I need to see all the other ones as well.

                               Can you help me tweat the formula to resolve this problem? Thank You in advance.

                                

                               Paul

                          • 10. Re: Find duplicate between two fields
                            Ween

                                 You see I need to know all the numbers that should be pointing to 9181396-00-NEW.

                                  

                            • 11. Re: Find duplicate between two fields
                              Ween

                                   Nevermind. I exported the results to excel and it showed everything. I amn ot too sure why Filemaker only showed 91 records but meanwhile there were over 760 in my excel file.