8 Replies Latest reply on Oct 6, 2010 1:33 PM by LaRetta_1

    Prevent duplicate entry in multiple fields

    CoolDuck

      Title

      Prevent duplicate entry in multiple fields

      Post

      Hi,

      FMP 11 Advanced.

      WIN XP.

      I hope this makes sense.

      I have 3 fields, fleld 1, field 2 and field 3.  While entering data (radio program code #'s) into any of the 3 fields, if the data being entered already exists on another record in the "2 remaining fields", I would like a warning to be displayed and give the user the option to contue or not.

      Thanks for any help.

        • 1. Re: Prevent duplicate entry in multiple fields
          LaRetta_1

          The three fields should be related records.  This is an important design aspect.  If you don't switch now, you will regret it later since you will be increasing the complexity of your design by 'more than three times'.

          If your radio program code numbers are RECORDS, then you simply set the validation on that field to be 'unique'.

          • 2. Re: Prevent duplicate entry in multiple fields
            CoolDuck

            Hi LaRetta,

            Thanks for replying.

            Each record has the 3 fields.  The 3 fields are in the same table.  I already have each of the 3 fields validation set to "unique".  That only works to prevent a duplicate entry into that one field.  I want to have the same validation of "unique" to span accross the other 2 fields as well.  Does that make sense?

            In other words;

            If I enter "ABC" in field 1 and it already exists on "another record" in field 2 or 3, I want to have a warning (or something) come up.

            Thanks again.

            • 3. Re: Prevent duplicate entry in multiple fields
              philmodjunk

              Define a text field with an auto-entered calculation that combines the three fields: Field1 & Field2 & Field3

              Set your unique values validation rule on this field and it will flag any records where the combined fields are not unique.

              • 4. Re: Prevent duplicate entry in multiple fields
                CoolDuck

                Hey PhiiModJunk,

                That works great and simple to.

                Thanks for all your help.

                Victor

                • 5. Re: Prevent duplicate entry in multiple fields
                  rjlevesque

                  Sorry for intruding but does that work?

                  I get the answer to be take field1 & field2 & field3 and combine

                  For example if the fields were abc(field1), 123(field2), and "xyz (field3)"

                  So once we combine them they become "abc123xyz"

                  ----------------------------- Now...

                  If we need to check if any of the individual fields are repeated, then how will the result of combining the 3 fields do that? Shouldn't we do a unique value test on each individual field? Perhaps I misunderstand...

                  I mean because "abc" is not the same as "abc123xyz"

                  • 6. Re: Prevent duplicate entry in multiple fields
                    philmodjunk

                    The method is to make sure that no two records have abc123xyz entered in the three separate fields. It's not fool proof but works most of the time. If you need values in just one field to be unique, then you don't use this method, you just put the unique values validation on just the one field.

                    How it might "fail":

                    Field 1: abc
                    Field 2: 123
                    Field 3: xyz

                    record 2:
                    Field 1: ab
                    field 2: c12
                    Field 3: 3xyz

                    Validation error will occur as the combined fields will produce the same value: abc123xyz. If this is a concern, you might insert a separator character of some sort in your expression.

                    Field1 & "§" & Field2 & "§" & Field3

                    • 7. Re: Prevent duplicate entry in multiple fields
                      CoolDuck

                      For the way I'm using it, it works like a charm.  What I had not said in my first post was that only one of the 3 fields get input data while the other 2 stay empty.  Sometimes field one will get data input while field 2 and 3 stay empty, and other times field 3 will get data input while field 1 and 2 stay empty.  If that data existed on one of the other 2 fields on "another" record, I wanted the system to notify me.  This methode works great for my use.

                      Thanks again PhilModJunk! 

                      • 8. Re: Prevent duplicate entry in multiple fields
                        LaRetta_1

                        I am suggesting that those three fields are 'like' each other and that implies they should be a related table.  If they were a related table, you wouldn't need to check all three fields at all but rather have 'unique' validation on ONE field.

                        You said, "While entering data (radio program code #'s) into any of the 3 fields, if"

                        This means that you are using three fields for ONE radio program code.  This goes against data normalization.