Hi this is Tom van Stiphout.
I am going to be talking about Simil today.
Simil is an algorithm that compares two strings,
and returns the similarity between those two,
as a number between 0 and 1;
Zero (0) meaning completely different,
and One (1) meaning identical.
The nice ones are in between
where Simil might say that the similarity is 0.8, which means it is more similar than other strings
that have a similarity of 0.6, for example.
Fuzzy String algorithms like Simil have interested me for a long time,
and I actually wrote an article about it in this book:
"SQL Server MVP Deep Dives" that came out a year or two ago.
If you want all the details about Simil,
I would recommend buying that book.
Here where are looking at a page from the book which explains how the Simil algorithm works.
It is a longest common substring algorithm.
What is does is look at the 2 words; in this case, 'Pennsylvania' and 'Pencilvaneya' spelled a different way.
It is looking for the largest substring that the 2 words have in common,
so that is "lvan", and the length of that in word1 plus in word2 is 8.
And then it is going to continue recursively looking at the substrings that are left,
looking at the left-hand side and on the right-hand side, trying to find common substrings again.
It is finding "Pen" for a length of 6,
and it is finding "a" on the right-hand side for a length of 2.
And then what is left has no more similarities, so the algorithm stops.
So we have a subtotal of 16 characters in common, out of a total length of 24. Therefore, the Simil is 16 divided by 24, which is 0.67.
Before I get into the details of the algorithm, lets first take a look at the end result.
What we have here is the Northwind database in Access 2000 MDB format.
This file is downloadable. (LINK in VIDEO DESCRIPTION).
What we want to do is prevent duplicate company names from entering the system.
The way I implemented that is by going to a new record,
and entering the name of your company;
none of the other fields are required,
so for the demo, I am just going to hit Save.
Now what is happening is that Simil is looking at all available company names in the database
and finding the ones that have a Similarity greater than 0.65
So in this case it found two of them.
0.65 is arbitrary; that is just the value I chose for this demo.
It is showing "Hungry Howie Night Store"
Is that perhaps this one or that one?
The user can either click on New,
to indicate that this is a new company,
or choose an existing one (from the records displayed).
If we click on New, then of course the record will be created.
If we click on Use Existing, then we will jump to that record
so that you can continue to work with it.
In this case, we will click on Use Existing
and we would go to the Hungry Coyote Company.
Lets take a look at how we implemented this functionality.
Here we are in the Customer form, and we are in the (form) BeforeUpdate Event, which is what fires just before a record is saved.
So this is the right time to look for similar customers.
So on Line 10, we are saying, if we are on a new record,
then on Line 20, we are opening the SimilarCustomers form.
It is a modal dialog,
and we are passing in the company name that we just entered.
Now, that Line 20, that modal dialog, will continue to run until the form is closed or it is no longer visible.
And then Line 30 will run and it will find out which of the buttons we clicked
whether it was New, Cancel, et cetera. We'll get into that.
Ctrl-F6 (switch module window).
Lets take a look at the Similar Customers form.
As I said, I put the Simil threshhold at 0.65, which is a little on the low side, but is good for this demonstration purpose.
The form Open (Event) is where most of the work exists.
I am going to skip a few things that are more like eye candy,
and I am also going to skip the SQL Server implementation that we have.
It is a very, in my mind, elegant way to do things, where, in SQL Server,
you can call upon a DOT NET assembly to do the work.
In the blog (LINK in VIDEO DESCRIPTION), we will write about that,
and all the code for that will be included in a download package.
Today, we are going to focus on the Access implementation.
Our strategy is that we are going to collect an IN clause with CustomerIDs that are above the threshhold.
GetSimilarCustomers is going to return that IN clause.
If there is something, then we are going to create a Recordset object where CustomerID is IN the ones that were found.
If there are no similar customers
then we need to be able to handle that in the ELSE clause -- and again, we are setting the Recordset.
So lets take a look at GetSimilarCustomers.
Jump to the Definition.
(This is a) fairly straight-forward form.
We are, essentially, going to open a recordset on the customer table.
We are going to loop forward over that recordset
and we are going to call mySimil -- that is the algorithm that compares the 2 strings:
one being the one in the recordset, and other being the one that we hand-entered.
If the Similarity is greater than the threshhold,
then we want it in the IN clause.
... a little bit more eye candy and we are done.
mySimil is defined like this:
as a function implemented in an outside DLL called "SimilDLL",
and it takes 2 strings and returns a floating point value, a Double.
So after we call GetSimilarCustomers,
we can set up a recordset with those customers that we are interested in,
with ones that have a similarity greater than our threshhold.
Then we are waiting for the user to click one of the buttons:
New, Use Existing, et cetera.
So if we look at New, for example,
here is a Property that we are setting to "New",
and then we are setting Me.Visible = False so that we fall out of the modal dialog.
We do the same thing for Use Existing.
We set the dialog result equal to whatever our CustomerID is that we want to jump to.
If we then come back in our main customers form, we will pick up the dialog result.
If it is new, there is nothing to do. The record will be saved.
If the user selected Cancel, we set Cancel to True
so that the form BeforeUpdate Event stops the record from being saved.
Otherwise, there will be a CustomerID.
The dialog result will contain that CustomerID so that we can do a FindFirst.
Using the Bookmark technique, we can jump to that record and show it to the user.
It is interesting to look at Tom's code and see how he did things.
One of the first things I noticed was using the SQL statement for OpenRecordset to the bind data to the form, which is unusual.
A more common way to bind data is to assign the SQL statement
directly to the form RecordSource property.
When data is in a back-end, which it usually is,
it looks like the method that Tom uses would
reduce time-out problems and improve performance
since opening a recordset keeps a connection open.
At the top of the OPEN event of the popup form,
the similar records are found by the GetSimilarCompanies function.
If the SQL statement does not return any records,
a standard approach might be to Cancel the Open event.
Instead, the form is hidden so the DialogResult property remains available for testing.
By not closing the form and using a global variable,
the functionality of the form stays together
so integration into other applications is more seamless.
The Visible property cannot be set in the Open event because the form is not actually open yet.
Tom found a way around this limitation
by using the Timer event to set visibility.
If it is desired to hide the popup form because there are no records,
in the Open event, Tom simply sets the TimerInterval to one millisecond,
which immediately triggers the Timer event.
The Timer event then sets the visibility of the form to false
and stops the modal behavior.
Since it has done its job, the Timer event turns itself off
by setting the TimerInterval back to to zero.
This is really clever.
Making the form invisible, to users, is like closing the form.
Code can still read the values.
In the code behind the Companies form,
the DialogResult property of the invisible popup form is assigned to a variable, strDialogResult.
Another trick Tom uses is searching the RecordsetClone
without setting an object variable,
by using WITH to reference the RecordsetClone object.
The record on the form is then found
by setting the form Bookmark, Me.Bookmark,
equal to the Bookmark that was
found by FindFirst on the RecordsetClone. {can also use Me.Recordset.FindFirst ~thanks, John}
Thanks, Tom, we have really learned some neat tricks … and Simil is a super cool tool :)