14 Replies Latest reply on Aug 9, 2010 2:42 PM by philmodjunk

    Unique Field Script

    user14040

      Title

      Unique Field Script

      Post

      is their a way to create a button that will search for duplicate data if so how do i create it.

      thanks

        • 1. Re: Unique Field Script
          LaRetta_1

          A few questions please:

          1) What do you want to do when you find the duplicates?

          2) What does duplicate mean?  Does it mean that the case sensitivity must be the same as well (one field has BROWN and a different record has Brown ... should they be counted as duplicates?

          3) Is this just to find duplicates in a single field or duplicate RECORDS where ALL fields match exactly?

          If you wish to simply find duplicate records in one field regardless of case sensitivity and no matter what to do with them, they search for ! in the field then sort by that field. 

          • 2. Re: Unique Field Script
            user14040

            Thank you for replying

            I have two fields I want to check before anyone start to enter data

            Field:Serial#  , Field:FSR#   with two separate buttons to check.

            I want it to just list the records just like if I entered a serial number in a search request. So I can edit the records if needed.

            So far  I have 765 serial number we have to track not as many FSR# but it will get up in number.

            If I can put a buton on the layout that I can use before anyone starts to enter data that would detect duplicates that would help out a lot.

            Thanks

            • 3. Re: Unique Field Script
              user14040

              The serial# and FSR# are combination of numbers and letters does not have to be case sensative.  Just want to detect the same data in the field.

              thanks

              • 4. Re: Unique Field Script
                LaRetta_1

                So are you saying that the serial# should be unique in the table?  And so should the FSR#?  If so, I might suggest stopping the duplicates before they happen by using field-level validation.  Would that work for you? 

                It is best to keep an issue from happening before it does than to fix it aftwards.  I'm not sure it will solve your current issue but it's the best approach.  If not, then just attach a find to your first button which searches for ! in the Serial# field and do the same with your FSR# button.

                BTW, what version of FM are you using?

                • 5. Re: Unique Field Script
                  user14040

                  yes that would be great but i am using FMTouch for the Ipad and it does not detect duplicates even though the app manual says it does, so until they come up with a fix i need some other kind of protection from duplicates.

                  Thanks

                  • 6. Re: Unique Field Script
                    user14040

                    What I need is a script that will check for duplicate data, in my case two fields serial# and FSR# both are not case sensitive and they have a combination of letters and number.

                    Can anyone help out or stir me in the right direction.

                    Using FilemakerPro 11 & FMtouch on Ipad.

                    Thanks

                    • 7. Re: Unique Field Script
                      LaRetta_1

                      Did you try my suggestion of search for ! in the field?  What didn't work about this suggestion?

                      • 8. Re: Unique Field Script
                        LaRetta_1

                        Okay, a script to search for both:

                        Enter Find Mode [ uncheck pause ]

                        Set Field [ serial # ; "!" ]

                        New Record/Request

                        Set Field [ FSR# ; "!" ]

                        Perform Find

                        ... is this what you want?

                        • 9. Re: Unique Field Script
                          user14040

                          Thanks

                          The statement you made earlier is really what I need.

                          [

                          So are you saying that the serial# should be unique in the table?  And so should the FSR#?  If so, I might suggest stopping the duplicates before they happen by using field-level validation.  Would that work for you? 

                          ]

                          This is more accurate of what i am looking for since FMtouch does not prevent duplicates in fields.

                          Usually what happens on desktop version if a field is entered and the same data is in the database it will not let you go any further until you change the data, this works on my desktop but not on my Ipad, so If I had a Save button that will look at FSR# in one table and a Save button that will look in the Serial # in another table that is what I need. [ not at the same time] two separate buttons for two separate tables.

                          FieldReport table

                          Unique Field: FSR#

                          Save Button to check FSR# to make sure it is unique before going to the new record. If same pop up custom dialog " FSR# you enter is Already In Database Please Use Another Number"

                          Equipment table

                          Unique Field: Serial#

                          Same as above.

                          • 10. Re: Unique Field Script
                            user14040

                            was hoping to get pointed in the right direction i am new at script writing so any help would be appreciated.

                            • 11. Re: Unique Field Script
                              philmodjunk

                              Have you looked at Filemaker GO? If you can set up your mobile device as a guest of a filemaker database on your network (which can be done with filemaker GO), you could use filemaker's field validation and auto-enter features to avoid the creation of duplicates in the first place.

                              If you have to search for duplicates after synching with FMTouch, LaRetta's method can be adapted to do that.

                              Enter find  mode[]
                              Set Field [FieldReport::FSR# ; "!"]
                              Set Error capture [on]
                              Perform Find []
                              If [get (FoundCount) > 1 /* you have duplicates*/]
                                 Sort Records [no dialog, Restore] // sort by FSR# to group by duplicate values
                                 //put your code here to do what you want to handle the duplicate value
                              End If

                              You can also establish a relationship that joins a table with itself by the field in question:
                              FieldReport::FSR# = FieldReport 2::FSR# (FieldReport 2 is a 2nd table occurrence of FieldReport)

                              To create this relations ship, just drag from FSR# to outside the FieldReport box and then back to FSR# before releasing the mouse button. Then you can put Count ( FieldReport 2::FSR# ) in a calculation field in FieldReport. If the value of this field is greater than 1, you have duplicates.

                              You can use the same methods with your equipment table.

                              • 12. Re: Unique Field Script
                                user14040

                                yes i have FMGo as well, but I need Mobility as well as sync I have two other users using the database and I need to be able to sync data as well as create PDF of form. 

                                yes it does work on FMGO, but i cant use it for what i need to do.

                                • 13. Re: Unique Field Script
                                  user14040

                                  When i tried the LaRetta script it put a ! in field and it just showed a hour glass didnt do anything except go into find mode.  but i will try again maybe a missed something.

                                  thanks

                                  • 14. Re: Unique Field Script
                                    philmodjunk

                                    Make sure you run the script on a layout that references the table where you are searching for duplicates. That means the "table" listed in Show records from in Layout Setup should refer to your FieldReport table when searching it for duplicates and your equipment table when searching it. If there's a lot of data in the table, this type of find will take a while to perform.