1 2 Previous Next 21 Replies Latest reply on Oct 28, 2015 7:27 PM by LaRettaK

    Preventing Duplicates

    scarruth33

      I have my database built, and now I am to begin entering sports cards into the database. I my question is how do I go about making sure that I don't enter a card twice. I know in Microsoft Access I just assign a key to a certain field and it will let me know if there is a duplicate entry. I would like the same kinda notification for this. Thank you for any assistance.

        • 1. Re: Preventing Duplicates
          coherentkris

          field level validation

          Defining field validation

          • 2. Re: Preventing Duplicates
            taylorsharpe

            As mentioned, it is all in the validation and Auto Enter.  You can auto enter a serial number, but I prefer decimal version of the UUID prefixed with a timestamp number.  I recommend checking the "Prohibit Modification of Value during data entry" so users can't manually mess up the serial numbers.  Make sure that validations require "Not Empty" and "Unique Value".  Also be sure to uncheck the "Allow User to Override During Data Entry". 

             

            While most people will just auto enter a serial number, this is the formula I use to create the table's primary key:

             

            Let ( [

             

            F1 = Get ( UUID ) ;

            F2 = Substitute ( F1 ;

              [ "0" ; ".0." ] ;

              [ "1" ; ".1." ] ;

              [ "2" ; ".2." ] ;

              [ "3" ; ".3." ] ;

              [ "4" ; ".4." ] ;

              [ "5" ; ".5." ] ;

              [ "6" ; ".6." ] ;

              [ "7" ; ".7." ] ;

              [ "8" ; ".8." ] ;

              [ "9" ; ".9." ] ;

              [ "A" ; ".A." ] ;

              [ "B" ; ".B." ] ;

              [ "C" ; ".C." ] ;

              [ "D" ; ".D." ] ;

              [ "E" ; ".E." ] ;

              [ "F" ; ".F." ] ;

              [ "-" ; "" ] ) ;

            F3 = Substitute ( F2 ;

              [ ".0." ; "00" ] ;

              [ ".1." ; "01" ] ;

              [ ".2." ; "02" ] ;

              [ ".3." ; "03" ] ;

              [ ".4." ; "04" ] ;

              [ ".5." ; "05" ] ;

              [ ".6." ; "06" ] ;

              [ ".7." ; "07" ] ;

              [ ".8." ; "08" ] ;

              [ ".9." ; "09" ] ;

              [ ".A." ; "10" ] ;

              [ ".B." ; "11" ] ;

              [ ".C." ; "12" ] ;

              [ ".D." ; "13" ] ;

              [ ".E." ; "14" ] ;

              [ ".F." ; "15" ] ) ;

            F4 = Get ( CurrentHostTimestamp ) ;

            F5 = GetAsNumber ( F4 ) ;

            F6 = F5 & F3

             

            ] ; F6 )

             

             

            While using industry standard UUID, it prefaces it with a timestamp of the creation date making these key fields sort in the order the records were created. 

            • 3. Re: Preventing Duplicates
              scarruth33

              Ok, maybe I'm not understanding or I haven't made my situation clear. Hopefully this helps. I will be entering cards, and the possible duplicate fields would be Players, Year, Card # and Location. I can obviously have players from different card manufactures in a year, but a card # is unique to the manufacture. I can have all of the fields in one Location but I don't want to enter the same card in the same Location. I hope that I haven't muddied the waters.Screen Shot 2015-10-23 at 1.37.57 PM.png

              • 4. Re: Preventing Duplicates
                user19752

                If "the same card in the same location" mean same card# and same manufacturer and same location, make a text field setting auto enter calculation

                List ( manufacturer ; card# ; location )

                then set field validation "unique value".

                • 5. Re: Preventing Duplicates
                  scarruth33

                  Duplicate card in a location would be the following, Name, Manufacture, Team, Year, and Card #, 2 of the same information in the same location is what I'm trying to avoid. Would I want all those fields validated?  Two or more cards can be in multiple locations but I do not want duplicate cards in the same location. I'm sorry if I'm making this difficult, I'm self teaching myself on Filemaker so its a bit of a steep learning curve.

                  • 6. Re: Preventing Duplicates
                    user19752

                    You wrote "a card # is unique to the manufacture", I think this mean if there are 2 card they have same card # and same manufacture, other field (Name, Team, Year) should be always same. If this is correct, try my previous post.

                    • 7. Re: Preventing Duplicates
                      scarruth33

                      Yes I did, and I'm sorry that I was not clear. I believe the information that you laid out would be enough to filter out any duplicates but as I said I'm completely self taught on this so I'm not sure exactly how to set up what you said

                      • 8. Re: Preventing Duplicates
                        scarruth33

                        Ok, I tried to do as user19752 suggested and the table is still permitting me to enter duplicates. I'm confused as to how I am to get the Text field to relate to the other fields.

                        • 9. Re: Preventing Duplicates
                          user19752

                          I forgot to write

                          1) For auto enter calculation, uncheck "Do not replace existing value..." check box

                          2) For validation, select "Always", uncheck "Allow user to override...", check "Unique value", (for better ux, check "Display custom message if validation fails" and enter your message, something like "This card is already exist")

                          3) the auto enter filed is better not on the layout, user shouldn't edit it directly and don't need to see.

                          • 10. Re: Preventing Duplicates
                            LaRettaK

                            scarruth33 wrote:

                             

                            Duplicate card in a location would be the following, Name, Manufacture, Team, Year, and Card #, 2 of the same information in the same location is what I'm trying to avoid.

                            Then you wish to create a concatenation ( text ), auto-enter calculation ( replace existing value ) like this:

                             

                            Name & "|" & Manufacture & "|" & Team & "|" & Year  & "|" & Card # & "|" & Location

                             

                            The prior instructions given are great.  I would not use List() for this since empty values would skew the match whereas a separator ( you can use something else but pipe is pretty standard ) ensures the correct entities are compared.

                             

                            Also keep in mind that you will not be told it is a duplicate until you leave the field.  It also helps to provide a script trigger for a nicer understandable message to the User.  Which trigger will depend upon your current process.  Experiment around and see how it goes and read up on their use in FM Help, here or other forums.  OnRecordCommit might be the best since there are several fields involved. It works best to disallow commit until the user is completely done with their entry anyway.  Also read up OnObjectValidate.  These triggers are only to provide support - not replace field-level validations.

                             

                            Since several fields are involved, you will need to determine which can be left empty ( if any ) and how your error trapping must compensate and assist the user through the process.  IOW, if a User enters only a Name, Manufacture and Location, they may lose the ability to finish their entry later because system will already find duplicates and refuse to let them continue.  If this is something which might be integral to your solution, it might be wise to standardize a card's 'uniqueness'. 

                             

                            And of course that brings up using a unique meaningless ID ( such as auto-enter serial number or UUID ).  It is critically important.  Not knowing your solution, you probably ( should ) have a Cards table.  You can take the concatenation above ( drop the Location portion ) and create a unique dataset.  Once the unique serial is assigned, use THAT in your other tables as the 'cardID'. 

                             

                            Then your validation concatenation to prohibit duplicate cards in same Location ( in your other tables ) will consist of:

                             

                            cardID & "|" & Location

                             

                            Let me know if anything needs further explanation.

                            • 11. Re: Preventing Duplicates
                              LaRettaK

                               

                              ... this is the formula I use to create the table's primary key:

                               

                              While using industry standard UUID, it prefaces it with a timestamp of the creation date making these key fields sort in the order the records were created.

                               

                              Hi Taylor,

                              An unsort will do the same thing since a table's natural sort order is creation order.  I suppose if this UUID were a foreign key in another table and one wished to sort on it there might be a value but I cannot see the use of it.  Can you explain when this might be useful?  Thank you!  :-)

                              • 12. Re: Preventing Duplicates
                                thurmes

                                To start with, make it as simple as possible. (A cardinal rule in database programming or any kind of coding.) If the card # is unique, validate on that. If the manufacturer/card # combination is unique, validate on that.

                                I'm still unclear - it sounded like the card # is unique for a given manufacturer, but is it unique? In other words, could two cards by different manufacturers have the same card #?

                                If the answer's no, you can set (in validation) the card # to be a unique value, and FileMaker will pop up an error message when it's not.

                                If the answer's yes, then you will want to validate by calculation,

                                Unfortunately, a field is validated when an entry is made into it. So, if you have a calculation set up, for instance, to give a 0 (non-valid entry) when Manufacturer is Acme and card # = 12000, you would need to apply that calculation to both the Manufacturer and the card #, so that it would show it as invalid no matter which order the information was entered (mfg, card# or card#, mfg).

                                It can be difficult to write the validation calculation, especially if you have to validate on all 4 of the fields Name, Manufacture, Team, Year, and Card #. Another way to approach the problem is to set up a field to show duplicates, and then go back and eliminate any duplicates. One way to do this would be to set up a relationship, that you call Duplicate, where Name = Name, Manufacture = Manufacture, etc., and then set up a calculation field with the calculation 'Count ( Duplicate::_kp_RecordNo ) > 1' (where _kp_RecordNo is the unique primary key for the record). Note that the count includes self, so it should always be at least 1; if it's greater, there's at least one pair of duplicates somewhere. I used the phrase 'pair of duplicates' to hint that you don't want to get rid of all duplicates, only the first (or last) of the pair.

                                • 13. Re: Preventing Duplicates
                                  LaRettaK

                                  thurmes wrote:

                                   

                                  Unfortunately, a field is validated when an entry is made into it. So, if you have a calculation set up, for instance, to give a 0 (non-valid entry) when Manufacturer is Acme and card # = 12000, you would need to apply that calculation to both the Manufacturer and the card #, so that it would show it as invalid no matter which order the information was entered (mfg, card# or card#, mfg).

                                  It can be difficult to write the validation calculation, especially if you have to validate on all 4 of the fields Name, Manufacture, Team, Year, and Card #.

                                  • 14. Re: Preventing Duplicates
                                    LaRettaK

                                    Hi Thurmes,

                                     

                                    Unnecessary to attach the validation to every field.  Validation can fire on a new field without entry into the field if Auto-Enter (Replace) is on it and if the other fields are referenced within its calculation.

                                     

                                    Again:  Auto-Enter replace on a new field (NOT CALCULATION) as described will validate uniqueness as described but everyone else above will work very well. :-)

                                    1 2 Previous Next