I have a Microsoft SQL Server database with five fields: ConsRowID (int primary key identity), ConsID (int unique not null), LastName (varchar(64) not null), FirstName (varchar(64) not null) and Birthdate (date null). I want to allow the user to search for different users by first name, last name, or Consumer ID (ConsID), and finally by birthdate given same names. It is expected that the ConsID will be unique, but I'm going to use ConsRowID as my identifier for now.
This is a table of Consumers. A consumer has a unique Consumer ID (ConsID), but we frequently have many with the same name. I want four combo boxes: Consumer ID, Last Name, First Name, and Birthdate. If a person searches by Consumer ID, that will simply populate the remaining combo boxes with the one person that matches this.
But if want to search by last name, I would want the combobox to be populated with all soundex values from the table that equal that input. So if I start typing in Smith, it will also populate with Smyth. The consumer ID combo would populate with all consumer ID's that match anyone with last name of Smith or Smyth (or any other Soundex matching Smith), and the First Name field would populate with a unique list of first names of these individuals.
Likewise, I could do the same thing with First Name: assuming that there is no selection in the Consumer ID or Last Name field, I could type in "Francisco" and that would populate the Consumer ID and Last Name fields with every individual whose first name is "Francisco".
Same with Date of Birth.
If existing values have been selected in any of the combo boxes, then they would serve as filters. E.g., If I type in Smith in the last name, and select "Smyth" then the resulting values available to choose from First Name and Birthdate would only apply to anyone with the last name of Smyth.
We have to assume that there may be two John Smith's born on the same exact day given worst case scenario, but generally, First Name, Last Name, and Birthdate would boil it down to a single individual.
I'm really having a hard time, for some reason, putting the logic together on how to approach this. It involves some appropriate stored procedures in SQL, some appropriate event handlers for each of the combo-boxes, including dynamically populating them based on other selections in other combo boxes; and some autocomplete processes data sources for each combo box.
It would be so much easier if the user just used Consumer ID's, but they are adamantly against this.
I'm not looking for exact code...just an algorithm (although if you provide code, so much the better).