7 Replies Latest reply on Jul 3, 2013 6:21 PM by Stephen Huston

    Can you validate multiple fields?

    BillisSaved

      Hey all,

       

      Thanks in advance for any help or assistance you are willing to provide.

       

      I'm working in FMP 12 and I would like to prevent entering duplicate student records by checking the uniqueness of a combination of the contents of multiple fields. For instance, when creating a new student record I would like to check the "Date Of Birth" and "Last 4 of SSN" fields of the new record against all existing records, rejecting the new record if the same combination is already present. Is there a way to accomplish this? Thanks again for your help!

       

      God bless,

      Bill

        • 1. Re: Can you validate multiple fields?
          timwhisenant

          Hi Bill,

           

           

           

          Place a calculated field in the person table with DOB and SSN which builds an id to test against like;

           

          UniqID, calculation with text result, formula = GetAsText( GetAsNumber ( DOB ) ) & Right( SSN; 4)

           

          which will create a value with the first part being the numeric representation of the date of birth followed by the last 4 digits of the social security number.

           

          To test for a duplicate value, do a scripted find  for a match to the new criteria against this UniqID. If no match is found your combination is valid for new record.

           

           

           

          DOB needs to be a date field type & SSN needs to be a text field type. All tests should realize, that the calculation is a text data type, for the best results.

           

           

           

          HTH,

           

          Tim

          • 2. Re: Can you validate multiple fields?
            sporobolus

            on 2013-06-28 14:44 timwhisenant wrote

            To test for a duplicate value, do a scripted find  for a match to the new criteria against this UniqID. If no match is found your combination is valid for new record.

             

            instead of a find, you can do this with a compound self-relation on the above

            matches plus the primary key of the related record must not be equal to the pk

            of the source record, then if there are any related records, it's a dupe

            • 3. Re: Can you validate multiple fields?
              Stephen Huston

              Caveat on Uniqueness:

              The combination of these 2 pieces of data provide for less than 4-Million possible results in any age/year group, so, while that sounds really good, you might run into "duplication" among  different people at a much smaller number of students in practice than you would expect statistically.

               

              I don't know what environment you might be using this in, but adding in a text string for last name could help postpone a false-positive.

              1 of 1 people found this helpful
              • 4. Re: Can you validate multiple fields?
                BillisSaved

                Hey Tim,

                 

                Thanks for your suggestion! I think your solution will be just what I was looking for. Have a great day!

                 

                God bless,

                Bill

                • 5. Re: Can you validate multiple fields?
                  BillisSaved

                  Hey Steve,

                   

                  Would you provide a bit more detail (noob level) regarding the specific steps required to implement the validation process you described? Thanks, and have a great day!

                   

                  God bless,

                  Bill

                  • 6. Re: Can you validate multiple fields?
                    BillisSaved

                    Hey Stephen,

                     

                    It looks to me like the procedure Tim described would provide 10,000 unique SSN combinations, assuming you only use the last four digits, for any given date, which I think would be sufficient for my needs. However, I may implement your solution so that I don't look back later and wish I had. Thanks so much for calling this limitation to my attention. Have a great day!

                     

                    God bless,

                    Bill

                    • 7. Re: Can you validate multiple fields?
                      Stephen Huston

                      Statistics can be tricky. The number of  possibilities has little to do with how soon you hit a duplicate when using real-world data.

                       

                        Keep in mind that with 366 possible birthdays in a class of 30 students, there is still often a shared birthday in most classes.

                       

                      Randomness is no assurance of uniqueness, so I always try to build in something to increase the odds in my favor.