1 2 Previous Next 17 Replies Latest reply on Aug 10, 2017 8:15 AM by philmodjunk

    Preventing Duplicate Full Names What am I doing wrong.


      I have the need to be notified when I add a new Royal Ranger into my Ranger Database so that I am not duplicating full names.  I am ok with

      Mark Jones,  Mark Jr.  Jones,   Mark Sr. Jones   but don't want Full names duplicated.

      I have tried this many different times the past 2 days and it still doesn't work.


      I created a new file with just enough to look this over. What have I missed. Even when there is no duplicate First or Last Name it pops up a validation error.  https://www.sugarsync.com/pf/D3374685_08646690_711050



      Any help is appreciated.  I am not a programmer. I am a lineman for at&t but have been using Filemaker since the early 1990's.  Most of my datafiles are clunky at best and flat file structured.  I do have 35 plus data bases that cover most information a Church Scout District level on down would need to track.

      This is not critical it just pops up every now and then.  I have sometimes 3 generations of men and boys that are in the program and I track all their advancements and info.   If you need your broadband internet fixed. I can do that.  This relational validation thing is kicking my tail. HELP!

        • 1. Re: Preventing Duplicate Full Names What am I doing wrong.

          The obvious question is: why must names be unique? I used to play in a cricket club that had two Ray Watsons. We managed.

          It sounds as if you may be using full name as a primary key which is an approach doomed to fail, as you are discovering. What are you going to do with all those John Smiths? Are you going to force them to change names? Adopt a nickname? Spell their names in different ways? It is about as silly as saying that no two cars can have the same colour, so you have pale yellow. yellowish white, greeny yellow … … …

          If there's one thing you should have learned since 1990 its the importance of having a unique identifier for every single record AND (well OK two things) that the unique identifier should not be comprised of real data.

          • 2. Re: Preventing Duplicate Full Names What am I doing wrong.

            How about this:


            This adds a new record since it's not in the DB (you could add more fields):




            The two fields in the header FirstName and LastName are "Global" since they're only for capturing user data entry. We add that data using the "Add" button in the header and the script its tied to (see below).




            But, if you try to add a name already in the DB. ...



            Here's the script I (quickly) wrote for the Add button:



            This is a super quick example and I didn't extend it since I'm not sure this is what you need.


            HOPE THIS HELPS.

            • 3. Re: Preventing Duplicate Full Names What am I doing wrong.

              Not that every record shouldn't have a PK, it should, but I think he's really looking for basic data entry validation.

              • 4. Re: Preventing Duplicate Full Names What am I doing wrong.

                In simple databases, an ID and name based value list doesn't work with duplicate names even though the value list is entering an ID.


                There are more sophisticated options that allow duplicates, but if using such a value list, you can set up a text field that uses an auto-enter calculation to combine first and last names. This field can then be set to validate for unique values in field options.

                • 5. Re: Preventing Duplicate Full Names What am I doing wrong.

                  Kind of like what I did above?

                  • 6. Re: Preventing Duplicate Full Names What am I doing wrong.

                    My point is that there is nothing invalid about non-unique names.

                    • 7. Re: Preventing Duplicate Full Names What am I doing wrong.

                      Yep, I got that, and that's a good point, too.


                      I was just answering the OP's question. I am assuming in his case, unique names are a business requirement.


                      A "natural key" like names + employee ID could be another way to allow the same name (but for a different person) and still catch outright duplicates.


                      The OP needs to weigh in at this point so we can better understand his requirement to continue the discussion.

                      • 8. Re: Preventing Duplicate Full Names What am I doing wrong.

                        The script will have trouble finding unique names.  A "==" is need to do this properly.   The variables should be:


                        Set Variable [$fname; Value: "==" & Untitled:UserFirstName]

                        Set Variable [$lname; Value: "==" & Untitled:UserLasttName]


                        Without the ==, doing a find for Tom would find Tom, Tomi, Tommy, Tomasina, or anything else that begins with Tom.

                        • 9. Re: Preventing Duplicate Full Names What am I doing wrong.

                          Sure, good point. It was just a quick example to flush out the OP's requirements.

                          • 10. Re: Preventing Duplicate Full Names What am I doing wrong.

                            Assuming that you only need to be able to distinguish between, say, 2 different John Smiths for your own internal record-keeping purposes, and not for anything that'll be displayed to the public, one approach would be to create a brand-new field which I'll call NameNo (short for "name number") and set it up to auto-enter the value "1". (For all your existing records, you'll want to use the "Replace Field Contents" command to insert the value "1" into that field just to bring them up to speed.)


                            Next you'd relabel your existing "Full Name" field as "Full Name Out". (I'm assuming it's a calculated field along the lines of First Name & " " & Last Name.) That's to distinguish it from the new field you'll be creating called "Full Name In", which is also a calculation field equal to Full Name Out & NameNo. Then do a Find for duplicate values of "Full Name Out", sort them alphabetically, and insert numbers other than "1" in the NameNo field where appropriate (John Smith 1, John Smith 2, John Smith 3, etc.).


                            Thereafter, do your duplicate searching on Full Name In and change the value of NameNo on the newly created record if necessary.

                            • 11. Re: Preventing Duplicate Full Names What am I doing wrong.

                              One of the guys on YouTube downloaded the file and tried to get it to work as he has done in the past.  It wouldn't work no matter what he did to it. 


                              He created another new file exactly as I had done 10 times over and it worked. He sent me the file and it works.


                              I am wondering if my FM 15 Advanced is needing reloaded.


                              I did a recover on my one of my main database files that for 25 years has never had issues. I have been messing with it making self join relationships.   It recommend I not use the recovered file. I put up a back up from 2 weeks ago and things are working again.  YET the file I sent him was authored today and it was only 200k in size.  Not sure what is going on with Filemaker.  It has always been very stable.



                              I will try the self joining table relationship one more time.


                              When your talking to a Group of Royal Ranger leaders and your discussing people  they know. It's important to keep in mind they only know the person as John Doe Jr.  If you say no just John Doe they will ask you which one?  Sr. Jr.  or you talking about Bud.  GRIN!  I am not doing a bunch of lookups in my databases. They are flat as a pancake with only a few name  and group lookups from one main database file.  Just like today.  When the main database file was no longer doing simple things it's done for years and years.... I put up a backup from last week.  All is well again.


                              I couldn't do that nearly as easy if I had 35 tables for each of my database files in one database file.

                              I had added 35 advancements into a Advancements database and they are still there and secure because the data remains in that one file.  (Yes it makes for a BIG 35 relational database but it works and is easy to fix if I mess something up.


                              PK_ID  fields I added them to some of my database files but never put them to use.  A full name lookup works well for my limited small records of a few hundred people.  Now in this case... It's still a matter of no duplicate Full names.  Hence the need for a validation.  Even if I was using the PK_ID field I would still need to know which John Doe I was looking at half the time all I have is a Full Name.  No phone number, address, or even email. Just get a First and Last name if I am blessed!

                              Thanks guys and gals.  Your ideas make things work for me and the rest of the world.

                              Mark Jones

                              • 12. Re: Preventing Duplicate Full Names What am I doing wrong.

                                Its up and working perfectly in my Local Outpost database and also in my District Database.


                                Don't know why we couldn't get that file to work but it's working now in my two old 20 plus year old files.


                                Thanks again folk for helping out.  Not being a real programmer has it's head bouncing off the wall moments.


                                I love  Filemaker. It allows folks like me that are not very skilled at programing to make something that blows anything else I have seen out of the water.


                                Thanks all.


                                Mark Jones

                                • 13. Re: Preventing Duplicate Full Names What am I doing wrong.

                                  In this case if I had
                                  Full Name   Primary Key
                                  Mark Jones  pk 001
                                  Mark JOnes  pk 002
                                  Mark Jones  pk 003

                                  The Primary Key. It's still of little value to me. I most of the time only get a first and last name and possibly a town or church they are from...sometimes. 

                                  It's better for me in this case for me to have the Jr. Sr. or what town they are in.  Just as long as I don't have a duplicate First and Last Name it works great.  Again the data files only have a few hundred to a 1000 folks in it.  The most I have had was 3 Mark Jones in my District Data file. 

                                  Mark Jones Sr.       pk001
                                  Mark Jones Jr.       Pk001
                                  Mark Jones Sikeston  pk003

                                  Now.  When I import from one file to another..no need to match up or convert Primary keys.  Just full names and it's a quick export import. I do this many times a month and it saves me lots of time.

                                  I just use what I can get.  Sometimes the name is..the new kid or the Mark Jones nephew.  As time goes along I do get more information but most cases a first and last name is all I get.

                                  I appreciate the efforts here and there are many ways to get something done.  My work at at&t.  Used to I could make a 3 minute call and it was done. Today it may take a hour and a half to Que in to smart chat, hit 3 departments, each keystroke is logged..time for all is recorded and spreadsheeted... bottom line is it used to take 3 mintues and 2 people.  Today it's a complicated mess.  I have to log into sometimes 5 or six different databases to get one thing done.  Then you may get disconected in the process and have to reboot your IPad so you can close out a ticket that used to take a minute..now 20 minutes. 

                                  Some things just work better when they are simple. 

                                  I do want to thank John Mark Osborn for helping me out with a duplicate Full name calculation that used the Self Join relationship with a Primary Key and full name calcualtion.  His videos on Youtube are awesome.

                                  I glean a lot of stuff from FMforms as well.  Many times I don't understand what you all are talking about but if I keep poking around and trying stuff it works.  I am no programer. That is for sure. Yet with Filemaker's help I am a good record keeper. I have used this program to make Cardboard Boat Racing software, Orienteering Course Cards, Pay check programs and Royal Ranger Tracking program that blows the rest of the stuff I have seen out of the water all kinds of graphical stuff.  I love Filemaker.  My hat is off to you guys that program.  I wished I had your brains.

                                  Mark Jones

                                  • 14. Re: Preventing Duplicate Full Names What am I doing wrong.

                                    A primary key is not intended to be used fo help the user tell one record from another. It's used by the database to tell one record from another and to link a record to other records in relationships.


                                    Not only are names not unique, names change--and not just to reflect a change in marital status. A child's name can change as well. Plus, names cannot be spell checked. It's quite possible to add a new contact, link in some related records and then find that the name is misspelled.


                                    If you link by name, any such name change can break the link to related records unless you correctly update their name field as well. This is an issue avoided if you link by serial number or UUID which is then both unique and never ever changed.

                                    1 2 Previous Next