1 of 1 people found this helpful
If you are dealing with only a few (definite) cities, you could use the Substitute function, as in:
Trim ( Substitute ( City ; [ "Denver Co" ; "Denver" ] ; [ "Denver, Co" ; "Denver" ] ; [ "Denver, Colo" ; "Denver" ] ; [ "Denver, Colorado" ; "Denver" ] ; [ "DNVR" ; "Denver" ] ; etc.,
If you have many cities, but know what they are, you could put them in a separate table, each with a unique ID. Then in your main database, you would have the CITY ID selected from a value list and the corresponding City would appear. Adding new cities would be far easier using the separate table as opposed to having to adjust the Substitute function many times.
Thanks for the approach. You captured the essence of the variations of city inputs appropriatly. I have actually done as you suggested and manually I can make this work. Unfortunately I get between 500 to almost 1,000 schedules downloaded a day and want to automate.
What I want to do is receive the schedule. Accept the destination cites as provided but relate them to a standard city name as I import the schedules. I do have a table with standard cities with unique ID. I also have a table for input. I have tried variation of join tables but have failed in having them work as I import.
The simplified tables are
Table for import
Table for Standard City
I think I need an additional table to connect the variations of cities from the import table to the standard table and use it as a lookup table. This next steps eludes me.
Thanks again for your help,
Since you are dealing with trucking companies, any chance they use (or could provide) longititude/latitude coordinates in the data they provide? There are many such databases available on the web (some include zip codes) that show cities and states from around the world. If you could get your imports to have the coordinates, then you could use a lookup to get the cities based on the longitutes, latitudes, or zip codes you receive.
What if you had a Table called CityVar. In that table you would have, along with a unique id, two fields; City and CityVar. You would create a record for each variation conceivable of each city you work in, and on each record you would also have the correct city name.
Then you would create a script trigger in the Trucks table, (where ever you enter the data where the truck is located ) so that when you enter the variation, the correct city name would poplulate the city field. I'm attaching a screen shot.
The answer is I am sure I could. However it complicates more than helps. I don't need to know the exact location but a general. In reality its a design problem similar to a thesaurus. Where you want to find a word with a similar meaning. In my case I want to find one word which may have many different spellings. The end goal is to find out the frequency a truck visit a city.
My question is to ask if there's any repeatability of the city spelling within each data source.
If there is, then make use of that source information as a way of maximising the number of matches.
There is repeatability with each source. However occasionally I get a new variant which may be a common misspelling or a contraction of the cities name which is fairly unique. Think of Minneapolis, MN and think how somone could shorten the name. What I need to do is build a table that will be populated with all of the different found spellings as I run into them and still be able to corelate to the standard city.
I believe I can create the matching tables for the variants. What I'm struggling with is how connect these tables to get the right result of a standard city, Minneapolis, Mn, if I get Minn. MN or Minn, MN or Minn or...
Unless you can receive more definite info, such as zip codes (which will allow lookups), you are describing a labor-intensive situation which forces corrections/adjustments after the information is received. You are always playing "catch up." There is no way to anticipate in advance all the variations of spellings and misspellings human beings can give to a city. Having grown up in Cincinnati, I am very family with Cinncinati, Cincinatti, Cincy, Cinti, Cinci, Cin, etc.
In these instances I set up a multi-predicate key field, carriage return separated...
St. Louis <CR>
The relationship will work with any match on any line in the key field.
FileMaker handles this type of key field well.
It is labor intensive at first. Less so overtime. If I could get zipcodes all the time that would be great.
If it isn't trucks with a location it is still a problem I need to solve. It is like a spell checker. How do you find the correct word from a misspelling. The problem is I'm really stuck having to use City as a metaphor for my solution.
I assume a multi-predicate key field is the same as multi Key field. I had not thought of that. I'll look into it as it sounds promissing. Thanks
Since there is some repeatability within each soucre, I think you should try to incorporate the source ID into your multipredicate key, either by way of some filtering once you've imported each set of data, or as a compound word eg Source & "_" & City.
That should help to reduce the manual process somewhat.
Perhaps you could tackle your problem in steps. If you set up a "Soundex" value from the city, (http://www.briandunning.com/cf/265), so that similarly spelled city names would have the same value. I'm not sure that will help too much with your application, but it is something I do to take care of names spelled differently.
I've been looking at your solution as a possible way out of my problems. I do have a few questions
1. I don't understand the purpose of the set field script. Is it a trigger or how is it invoked?
2. In the table links the relationship is a many to many and I understand that. Do you actually use the UUID fields and UUID city fields in the solution?