Jump to content
Sign in to follow this  
PianoGuy

Creating unique id from Last and First Names

Recommended Posts

I've watched all the Lynda.com tutorials at least once, and I'm having trouble getting off the ground. I have an existing database with many different fields, and I need to import new records to add to it as time goes on. The database consists of records of my piano customers over many years. Every time they schedule a new service appointment, which they can do online at my website, it creates a new record in that online database. Periodically, I download records from that database, which may include records (customers) that I already have. I need to create some kind of unique ID using first and last names so I won't be creating a new record in my database (and creating duplicates). Now I'm trying to make a calculated field "Last_First Name" using the last and first names of the customers, but I'm finding it difficult to do. My question is, am I creating more problems doing it this way, or am I missing something? How can I otherwise get a unique ID for each customer? Using a serial number doesn't seem to me the best way to do this. Downloading new records doesn't create new serial numbers for them anyway.

Thanks for any hints or advice.

Paul

Oh, yeah. I'm using Filemaker Pro 11 on Windows XP (if that matters).

Share this post


Link to post
Share on other sites

Do you expect all your customers to have unique names? If I understand your description correctly, anyone can schedule an appointment at your website - and if a new customer gives their name as "George Brown", the appointment will be assigned to the already existing George Brown.

The proper solution to this kind of problems is to issue a meaningless unique ID to each customer at the time they are entered into the database for the first time. If both your website and your solution can register new customers, then you need to make sure the issued IDs do not clash - either by using a site prefix, or by employing some sort of UUID.

Using a serial number doesn't seem to me the best way to do this. Downloading new records doesn't create new serial numbers for them anyway.

Using an auto-entered serial serial number is the best way to go - and when importing new records, you have the choice to turn auto-enter on.

Share this post


Link to post
Share on other sites

Thanks for the reply.

There are instances where the same customer is identified by a spouse's name, though it is the same account, essentially. The other person may have made the appointment. So, obviously I need a way to identify them both as the same account. Using an auto-entered serial would just add a new serial number to the existing account, which wouldn't work. (I didn't see the option to turn on the serial generation for downloads). I had tried having my "Last_First Name" generated during import , which didn't work well. As I am a new user, I know what I am trying to do, but seem to run into roadblocks when I try to implement my ideas.

Obviously, using names is the easiest way to identify a customer, since records are being imported from different sources in an ongoing basis. Creating a UUID is beyond my capability (I'm not a programmer).

Just brainstorming here... Here's one idea, if I could get it done. When importing/entering new records, if I create a new record for each instance, I could do a search of existing data that would match any additional fields in addition to the name (address, phone, etc.) and have all these records merge. That would entail removal of the ID of the newest customer entries in favor of the original one. The could be done after the import as a separate process (weeding out duplicates and retaining the most current data, ie., new address, phone number, etc.)

The purpose of my using Filemaker is to organize a Reminder system, so that my secretary doesn't call the same customer twice. That was happening, and more often with long time customers because of multiple appointments/records. I did manually scan the existing records (about 1500) to weed out duplicates, but each new import will spoil it again.

The way I've thought to organize my new database is to have two tables.

1)The first would be the customer information, which originates from the website data and other data sources (prior to my website database, I had other systems and databases). The web database has a signup process which includes information about the piano, what service(s) need to be done, and other relevant information. All this data would imported into the Customer table.

2)The other table would be the Call Log, which would include:

Last Service Date

Last Service Performed (service history)

Last Reminder Call (date, time, or timestamp)

Next Reminder Call Due (date)

Customer Response (yes- let's make appointment, no-remove me from your list, call me later, etc.)

Future Service Recommended (next tuning due date, etc.)

Anyway, that's my overall plan.

But, first things first. Using the "Last_First Name" ID scheme I mentioned, I was able to make it work when manually entering a new name. When importing the new records, I could not get it to work during the import. How do I get the calculated field populated while importing?

Next question: I created the Call Log table last night. When I tried to include the "Last_First Name" calculated field, it wouldn't work. I could not find a way to generate it, or import that field into the Call Log table from the Customer table. Is there a way to do that? Do I really need a second table? Please don't mind my stupid questions, I'm just trying to understand how this program works.

Thanks very much for your time and input.

Paul

Share this post


Link to post
Share on other sites

You haven't answered my question: do you expect all your customers to have unique names? IOW, if you now have a customer named George Brown, you will not have - not now nor at any time in the future - another customer with the same name. If the answer is no (as I believe it must be) then you cannot use customer name alone as a unique identifier.

There are instances where the same customer is identified by a spouse's name, though it is the same account, essentially.

Then you should have a table of Accounts and a table of Contacts, with a one-to-many relationship between them.

I could do a search of existing data that would match any additional fields in addition to the name (address, phone, etc.) and have all these records merge.

Well, if you consider a combination of say FirstName, LastName and PhoneNumber as sufficiently unique, you could use all three as the matchfields to the parent table. There is an additional complexity here since contacts (not to mention accounts) are bound to have multiple phone numbers - but I believe this could be solved with a little ingenuity. Still, the best thing would be to assign a unique ID to each account and demand existing accounts to use it when scheduling a service.

Share this post


Link to post
Share on other sites

My customer database has about 1500 names in it. Not likely that two different customers will have the same name, ie., John Smith. More likely that John Smith has a wife Sally, who usually makes the appointments, but John surprised her this year with a tuning for her birthday. So, tying them together might be the challenge. If I put the name and zip code, phone, whatever, it probably would suffice to get a unique identifier. Theoretically, two people could have the same name, but that's not likely enough to cause a problem.

The website actually assigns a username and password when someone signs up for an appointment, but that isn't included with the record download. I might try to see if it could be included. The trouble is, there are years of customer appointments that weren't scheduled through the web calendar. That's not counting the fact that existing customers don't remember the login information and sign up again, thus creating a duplicate entry.

Now, you suggest having a table of accounts, and a Contacts table. Going a little further down that road, if I were to assign the unique identifier to the "piano", instead of the owner, by using the make/model and serial number, that would effectively create a unique ID. Then, as you suggest, the tables could be tied together. Trouble is, I don't have that luxury, since that information isn't in the database.

Another possibilty (going forward) would be to use their email address as an identifier, as is done on many ecommerce sites. The added benefit would be offering reminders through email, an email newsletter, and other offers that could be sent thru email. Again, I do have some emails, but many do not. I really don't want to put impediments to the process of people making an appointment, and many are reluctant to divulge email addresses.

Suppose, if I were to export the database, say to Excel, and then reimport them, assigning new serial numbers upon import, that would give them the unique identifier I need, right? I've already gone through them and weeded out as many duplicates as I could find. When I import more records from the web database, can I update the existing records and have new serial numbers assigned to new records? That might work. What do you think?

.

Share this post


Link to post
Share on other sites

I work for a company of 600 employees and a few years ago we hired two people, a week apart, with identical first names and last names. They worked in the same department, had the same supervisor, and had the same title. It was confusing on a multitude of levels.

I'd take Comment's advice and go with a unique serial number.

Share this post


Link to post
Share on other sites

Well, I guess anything can happen! In my case, it's really not that critical.

Ok, but can I do that by starting fresh with a new database, having exported the existing data to an Excel file, and reimport that data with auto-entered serial numbers? Can I download new records into that "clean" database, and have new serial numbers assigned to new records, and have existing files updated (minus the serial numbers)?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.