how to deny double records

[ronny123]

hi,

working with FM 6 on OS X and have to create a customer database.

the problem i have is if the record still exists to get a message.

sounds simple but after hours of research in different discussion boards i have not found any answer.

any ideas, scripts???

kindly regards

reinhold

[RalphL]

The way I handle this is take the user to layout that showa all names in a portal. I have a field were the user can type a few letters of the last name and portal shows only names starting with those letters. If the name exists the user can click on a button which take them to the detail layout. If the name does not exist the user clicks on a new button which takes them to the detail layout. The letters that were typed in are now in the last name field.

[ESpringer]

RalphL beat me, but I'll post what I was writing anyway:

If you have one customerID field, then of course you can use field validation to make sure that each value in that field is unique.

However, if your problem is more fuzzy -- making sure that you don't get two records (with different custIDs) for the same person -- it's more complex, especially in FM6.

One approach is to have uniqueness validation on the phone number field. Although it's possible that two records will share a phone number, it's not likely. The validation should allow user to override. Still, if you're working with LOTS of names, there could be typos, phone changes, name changes, duplicate names, etc., making a match on any one field unreliable.

The most complex approach is to make data entry happen on a special layout with special global fields to hold the values intended for each normal field: gPhone, gLName, gFName, gAddr, gCity, gST, gZip, etc.

I like the idea of making this screen serve double-duty as a "find" and as a "new record" step. Set up three relations matching gPhone to Phone, gLName to LName, gAddr to Addr (these are the fields that are at all likely to be unique). Now, make three portals: to all records with THAT phone, another portal to all records with THAT last name, and another portal to records with THAT addr. (Each portal also gives a glimpse into the other fields for easy confirmation that it is or is not the customer you're dealing with at the moment. Now, the person interested in making a new record can see immediately whether there's a likely match already in the system.) Each portal row has a "Go to this record" button, which goes to that record and clears the global fields. But if the data-entry person does NOT see any likely matches, the rest of the basic contact fields should get their data input, a unique ID should be generated, and then the basic idea would be to have a button on the layout confirming the need to create new record. But the script on the button would do more: assign a customerID, create a new record, set the new record's field according to the corresponding global, clear the globals, and go to the best layout for further work on the record...

[ronny123]

thanks for all the information,

i will try this

regards

reinhold

[Steve T.]

Hmm.... I haven't done this, but what if you were to concatenate all fields you wanted checked into a single string and validated that for uniqueness? That way, you could omit fields like keyid's/serialnumbers/creationdates, etc. and just focus on those fields important to you.... I have not thought this through, though... just hypotheisizing...

--ST

[Leb i Sol]

I am with you Steve...and you are right on the $

Here something that I posted just a few days ago and got the the link from Queue.

OLD thread and links:

http://www.fmforums.com/threads/showflat.php/Cat/0/Number/107975/page/2/view/collapsed/sb/5/o/all/fpart/1

above all, the problem is in rony's post....which doesn't say squat...it could very well be that all he needs is to do simple /single field validation...

so the real question is "What (fields) does make each record unique?"

eg." the fact that there can not be 2 "johny smiths" or the fact that "there CAN be 2 Jonhny SMiths but NOT with the same address" etc.

All the best!

[ESpringer]

I have a couple reservations about the concatenating-all-fields approach.

It means that we are dependent on perfect data entry; if one record has "Av." and another has "Ave.", or one has "(212)555-1212" and another has 212-555-1212, or one has William and another has Wm. & Mary, it may still be the same individual/household. Having portals to show *potential* duplicates based on the two or three fields most likely to be unique, was my idea of how to help a data-entry person avoid tripping over minor issues. (Of course, if two records for the same individual have minor variations in all three of these fields, even my method won't help! This should keep us on our toes for ways to prevent data variations before they start, such as by auto-formatting phone and address field data.)

Also, can a self-join work well on a concat, since the right side is not indexed? (I thought I had actually managed such a solution in the past, but now wonder how.)

[MoonShadow]

You can index a concatenated field. I use self-joins quite often this way and the right-side can be indexed. In fact, I usually just put it ON, as any field such as this frequently searched, used in relationships or sorted anyway.

I prefer to accept global input (one field) - just part of the information and display a portal of the results. I concatenate LastName, FirstName, CompanyName, Phone, CustomerCode and anything else the business wants to add in. I then Find using Insert Calculated Result [ ConcatenatedText, "*" & global & "*". All matches are then displayed in a portal along the side. User either selects one (and is taken to that record) or clicks 'New'. One human eyeballs can make this level of distinction.

I never let them simply create a new record - not for Customers. That is how our Customers/Contacts bloat with duplicates and are then a mess to clean up and consolidate ...

[ESpringer]

Indeed, the concat would be useful for a quick find from a global "whatever" field!

But perhaps uniqueness of a concat self-join is not 100% reliable as a validation check of the sort Steve T describes unless it screens out variations.

At any rate, I'm happy it can be indexed OK; I don't know why I lost track of that confidence along the way.

[Steve T.]

Humans? There's humans involved? Just kidding... actually, it's just good to know there are options. Leb may be right... Reinhold may not know about field validation under Define Fields... so that could be good enough.

I've seen Springer's phone number trick before and I think that's probably the easiest/quickest and could be all that's needed... it's all relative, eh folks? Reinhold, so it's up to you... how smart are your people, how many of them will be creating accounts, how smart are you w/FileMaker, how big is your customer base, how many of your customers have similar names, blah blah blah ad inifinitum. Good for you for getting started, though. We're all here to help!

--ST