There were several different answers to my original question on how to deal with a field in the database like "Company Name", when you allow the users to enter it in themselves, rather than choose it from a dropdown. The problem with this method is when it came time for an administrator to search for a user based upon the company they work for. If the admin didn't know the exact company name or even ANY company name, they wouldn't be able to do a search. Therefore, our search page contains a drop down built by selecting the distinct Company Name from a table in the database, built from the info on the form. Unfortunately, there were up to 8 or 9 different ways for someone to spell their company's name. ARGHS! So: I wanted to post a compilation of all the replies, because all of them were rather really good at solving the problem. Thank you everyone! So for those of you with similiar issues, here you go: ----------------------------------------------------------------------- From: Tobe Goldfinger [mailto:[EMAIL PROTECTED]] when I have situations like this and there seems no other way around the problem...... what I do is put 2 fields on the record the first one to hold the company name the way it was entered and a second one that is initialized to that user-entered data..... but the administrator has a screen where they can cleanup/fix that second field to get the values down to a standard way of typing/naming things this way they can clean up the data as it comes in..... as slowly or as quickly as they feel like it..... if it's a system where consistency is seriously important, then I email them whenever a new record arrives so they're notified to go and see if they want to fix the spelling/typing for the newest record and of course your admin dropdown form fields would now use this 2nd 'cleaned-up' field ----------------------------------------------------------------------- From: Judith Campbell [mailto:[EMAIL PROTECTED]] Build a drop down of all the existing companies from the user to pick from, or a field for them to add their company if it's not on the list. ----------------------------------------------------------------------- From: Craig S. Kiessling [mailto:[EMAIL PROTECTED]] Another good idea might be to shout out loud "Siiiiing--Me--A--Sooong--For--Daaddy, Oh, Sing--Me--A--Song..." (ok, so that's not exactly a solution, but it made you smile right??!) ----------------------------------------------------------------------- From: Sandra Clark [mailto:[EMAIL PROTECTED]] How bout running an onChange event on the entering of the company name. Have a hidden frame run a query on the database on say the first 2 or 3 letters entered. Populate a select box on the original page with those companies with one selection being "Keep current company". That way you would only show those companies with the names similar to those the user is typing in. They can make their own selection and you would have some sort of integrity check user side. Of course I would run something on the Admin side as well to make sure that they could catch whatever falls through the cracks. ----------------------------------------------------------------------- From: Jim McAtee [mailto:[EMAIL PROTECTED]] Unless you've got a table of distinct companies for registrants to choose from, you've got to solve the problem in the search application. Using a drop down list here, when you know there could be multiple spellings of a company, is probably the wrong approach. It depends a little on how exact the search needs to be. For instance, are you trying adding up $$ amounts for billing purposes, or do you just want administrators to be able to drill down to the individual registrations? If it's the latter, then searching using wildcard matching should suit your purposes. ----------------------------------------------------------------------- From: Bob Silverberg [mailto:[EMAIL PROTECTED]] We had a similar issue with the City field in a database. We maintain a City table, and each user record only contains the primary key (an integer id) to the corresponding record in the City table. For the user interface we created a combo-type box, so that a user could either select their City from the drop-down, or enter a new City in a text box. If they entered a new City in the text box, a record is added to the City table. The administrator then has a module in which they can approve or disapprove new cities added. Once they approve the city, it will appear in add drop-down boxes. If they don't want to add the city, then they'll change the pointer in the user table to point to the correct city, and they delete the new city from the City table. It's a bit of work, but it works well for the client. ----------------------------------------------------------------------- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Yep we had an identical thing where students had to select a school that they attended in the UK from the complete list of schools (a lot!). We got around it asking the user to enter the first three letters of there school - we then present them with the form with a reduced list of schools begininning with those three letters. ----------------------------------------------------------------------- From: Keith Meade [mailto:[EMAIL PROTECTED]] If you don't mind me plugging my own product, cfx_kmSuite contains a couple of functions that might help. If you could store your company names in a CF list, the "cfx_kmList.ListBestMatch" function could be used to resolve user input to an existing company name. Another option would be to use the "cfx_kmMisc.Soundex" function to build soundex values for each company name. You could compare the soundex of the user input to the stored soundex values. cfx_kmSuite adds more than 100 functions to Cold Fusion. I try hard to keep my customers happy. If there's something you want that isn't in the product, I'll try to add it. Documentation and purchase info for cfx_kmSuite at: http://www.hoptechno.com/kmtools/ ----------------------------------------------------------------------- From: Jim McAtee [mailto:[EMAIL PROTECTED]] You could make your search strings more sophisticated, allowing both wildcards and Boolean operators. More work for you, since you have to parse the search string and compose the appropriate SQL WHERE clauses. For example allowing the following entry for someone searching for "The White Pharmaceutical Corporation": (*White Ph*) OR (*White Farm*) OR (*Wite*) Would match: The White Pharmaceutical Corporation White Wite Pharm. The White Co. White Pharmaceuticals And probably a few you don't want, which may not be a problem, so long as you don't take the search results as gospel (as for billing): Harry J. Witeman Chemicals R.J. White Drug Company ----------------------------------------------------------------------- From: Scott Bartlett [mailto:[EMAIL PROTECTED]] The easiest approach is probably: Building a dynamic list based on values already entered so far, with an "other" text box so people can enter the name manually if it's not already on the popup list. The first person will *have* to type their company name (as they're not on the list yet), but others should then get it from the pop-up. This is probably the easiest way, assuming they remember to look at the pop-up!! Alternatively, you could try a bit of psychology (a bit long winded this one): Get the users for the same company to add some other kind of field where they're much more likely to enter the same detail. For example, postcode is good (for companies with one location!). URL to their main web site is another. The domain part to their email address is probably best of all. When a record is entered, search the DB for the first existing record with the same key detail. Before doing that search, do things like strip spaces, strip http:// and trailing /'s from URLS, capitalise postcodes or lowercase URLs, etc etc ... from the key detail to make it as standardised as possible, to maximise your search matches. Then set the new record's company field to be the same as the first record. Thus, all new records pick up the same name as the first one from that company... Yes, there's still room for error, but probably a lot less than you've got at the moment. I think the former approach is the better one (and less work). ----------------------------------------------------------------------- From: Judith Campbell [mailto:[EMAIL PROTECTED]] What we do on the large sites where we have to manage this is have a company table with the correct spelling of the name that we build the drop down from, but if you have too many names for a drop down you might consider limiting by alphabet..let them enter the first letter of the company name, then produce a drop down with only companies that start with that letter. Or if the companies are of different types - manufacturers, distributors, etc you might have a select company type, then display the company. We never store the company name in the user table - non-normal data form - we just store the company id. How often do new companies get added, and is that a process you can control..i.e. pre-build the table? Building the company table initially from the data in the user table will involve human intervention..someone to determine which names are duplicates, etc. then you'll need to run a routine that puts the company id in the user table with the correct id for all the duplicates. It's an interesting exercise in data cleaning, but one we've done many, many times. I'm glad to offer you whatever help I can. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-community@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
