Using Lookup to find matching address in different tables....
I have three tables (actually many more, but this is all that pertain to my question), Workorders, Customers, and JobTacking.
This is an established database to which we just added the JobTracking table. The flow is as follows. The individual inputs workorder details in the Workorder table and then enters the customer information which is stored in the Customers table. JobTracking is where another individual is entering job details. What we are trying to do is have it so that when the Workorder person enters a new customer, it checks the JobTracking table to see if there is a matching address, just the address. I have done this prior using Lookup on invoice numbers, works great. If I use Lookup to do the aforementioned I never seem to get a direct match. Some don't seem to match at all, some have partial strings of letters within a street name etc. How do I make sure that the match is reasonably the same, meaning, someone in Workorder might put 25 Redstone Street and someone in JobTracker might put 25 Redstone St. or possibly even misspell the street name.
In the WorkOrder table I have a field called Match set as a Calculation as follows:
Match= Lookup ( JOBTRACKING::JobAddress; "No Match" )
What is the best way of doing this? Is lookup the best way? Eventually we would like to make it so that if there is a matching address the person in warranty can click a link to see the match in Jobtracker and copy specific fields over to Warranty.