Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
field splitting
- Thread starter learnfox2
- Start date
Mike Lewis
Programmer
Something like this:
Address2 = SUBSTR(Address1, 61)
If Address2 is a field (rather than a variable), you need to REPLACE it (I know you already know how to do that).
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
Address2 = SUBSTR(Address1, 61)
If Address2 is a field (rather than a variable), you need to REPLACE it (I know you already know how to do that).
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
TamarGranor
Programmer
Mike has given you the answer to your question, but this sounds like it might be a design problem with your data. Why do you need to do this?
Tamar
Tamar
Olaf Doschke
Programmer
Like Tamar, I'd also say this is more of a design problem.
I'd store an adress in a memo or in it's seperate parts in seperate fields long enough.
That apart, if for example your adress1 field is C(60) only you can't split that field to put the exceeding part to an adress2 field, as the inserted values are truncated and what was truncated is lost.
Bye, Olaf.
I'd store an adress in a memo or in it's seperate parts in seperate fields long enough.
That apart, if for example your adress1 field is C(60) only you can't split that field to put the exceeding part to an adress2 field, as the inserted values are truncated and what was truncated is lost.
Bye, Olaf.
LearnFox2,
Mike Gave you half the answer,
As per Mike
Address2 = SUBSTR(Address1, 61) or
REPLACE Address2 with SUBSTR(Address1, 61)
The rest of the story
Address1 = SUBSTR(Address1,1,60) OR
REPLACE Address1 with SUBSTR(Address1,1,60)
But I still Agree with Tamer and Olaf
My Addresses are broke up into several Parts
Number C6 IE 12345
Direction C5 IE North
RoadName C35 IE Main
RoadType C10 IE Street
MailStop C10 IE APT / Suite
City C35
County C35
State C2
Zip C13
GeoX1 C4 Degrees
GeoX2 C4 Minutes
GeoX3 C4 Seconds
GeoX4 C4 Other
GeoY1 C4 Degrees
GeoY2 C4 Minutes
GeoY3 C4 Seconds
GeoY4 C4 Other
Adr_Type C1 (Business/Residental/Industral/PostBox)
But then again, Most people do not have to figure out School Bus Routes, U.S. Mail Box Pick Up points, Emergency Service Dispatch Points, Etc.
David W. Grewe Dave
Mike Gave you half the answer,
As per Mike
Address2 = SUBSTR(Address1, 61) or
REPLACE Address2 with SUBSTR(Address1, 61)
The rest of the story
Address1 = SUBSTR(Address1,1,60) OR
REPLACE Address1 with SUBSTR(Address1,1,60)
But I still Agree with Tamer and Olaf
My Addresses are broke up into several Parts
Number C6 IE 12345
Direction C5 IE North
RoadName C35 IE Main
RoadType C10 IE Street
MailStop C10 IE APT / Suite
City C35
County C35
State C2
Zip C13
GeoX1 C4 Degrees
GeoX2 C4 Minutes
GeoX3 C4 Seconds
GeoX4 C4 Other
GeoY1 C4 Degrees
GeoY2 C4 Minutes
GeoY3 C4 Seconds
GeoY4 C4 Other
Adr_Type C1 (Business/Residental/Industral/PostBox)
But then again, Most people do not have to figure out School Bus Routes, U.S. Mail Box Pick Up points, Emergency Service Dispatch Points, Etc.
David W. Grewe Dave
dgrewe
Must be nice to only need to deal with one address format.
I have to consider that addresses
1) can have NO directional indicators (DI)
2) can have the DI before the Number
3) can have the DI after the Number
4) can have the DI after the RoadName
5) can have the DI after the RoadType
6) can have the DI split between any two of the above!
And often have two or more of these formats in the same city, county, and/or zip code.
Not to say anything about that when Harris Street crosses the city limits into the county it continues as Harris Street Road.
Now is Harris Street Road
1) a RoadName (Harris) plus a RoadType (Street) plus a RoadType (Road)? OR
2) a RoadName (Harris) plus a RoadType (Street Road)? OR
3) a RoadName (Harris Street) plus a RoadType (Road)?
mmerlinn
"Political correctness is the BADGE of a COWARD!"
Must be nice to only need to deal with one address format.
I have to consider that addresses
1) can have NO directional indicators (DI)
2) can have the DI before the Number
3) can have the DI after the Number
4) can have the DI after the RoadName
5) can have the DI after the RoadType
6) can have the DI split between any two of the above!
And often have two or more of these formats in the same city, county, and/or zip code.
Not to say anything about that when Harris Street crosses the city limits into the county it continues as Harris Street Road.
Now is Harris Street Road
1) a RoadName (Harris) plus a RoadType (Street) plus a RoadType (Road)? OR
2) a RoadName (Harris) plus a RoadType (Street Road)? OR
3) a RoadName (Harris Street) plus a RoadType (Road)?
mmerlinn
"Political correctness is the BADGE of a COWARD!"
>>And often have two or more of these formats in the same city, county, and/or zip code.
Sounds like you need to get yourself a proper Address Checker. I believe that the best one around right now is called AccuMail - it does an excellent job of sorting out precisely these issues as well as handling mis-placed things (like wrong city name or a misspelled street).
May be worth checking out (and it's not even that expensive compared to some of the ones I have seen)
----
Andy Kramek
Visual FoxPro MVP
Sounds like you need to get yourself a proper Address Checker. I believe that the best one around right now is called AccuMail - it does an excellent job of sorting out precisely these issues as well as handling mis-placed things (like wrong city name or a misspelled street).
May be worth checking out (and it's not even that expensive compared to some of the ones I have seen)
----
Andy Kramek
Visual FoxPro MVP
Mike
Read through my comments in thread669-1127243 and thread183-1299244. In the U.S. splitting the city, state, and zips make lots of sense because of the 'system' we use. From my experience working in several U.S. Post Offices in almost every job description and my experience programming around all of the oddities, I just don't see any way that these items could be lumped together and work properly in the U.S.
mmerlinn
"Political correctness is the BADGE of a COWARD!"
Read through my comments in thread669-1127243 and thread183-1299244. In the U.S. splitting the city, state, and zips make lots of sense because of the 'system' we use. From my experience working in several U.S. Post Offices in almost every job description and my experience programming around all of the oddities, I just don't see any way that these items could be lumped together and work properly in the U.S.
mmerlinn
"Political correctness is the BADGE of a COWARD!"
Imaginecorp
IS-IT--Management
If you have 2 Address boxes and a city, state & Zip boxes where the user inputs values, do the following in the First Address box's Valid event
thisform.address2.value = substr(this.value,61)
this.value = substr(this.value,1,60)
Or you can do a Replace
BUT; if you are using 1 box to capture the complete address (street, city, state etc) break them up as its next to impossible to accurately do so in code...
thisform.address2.value = substr(this.value,61)
this.value = substr(this.value,1,60)
Or you can do a Replace
BUT; if you are using 1 box to capture the complete address (street, city, state etc) break them up as its next to impossible to accurately do so in code...
Mike Lewis
Programmer
Mike,
Your idea of putting cities, states / provinces, countries, etc. in their own tables, each with a foreign key from their respective parent tables, makes perfect sense from a database design point of view.
But how do you envisage it would work from the user interface point of view? When entering a new address, would the user enter the street, etc., then use a drop-down to select the city? Or perhaps do a search for the city, and choose the correct one from a set of close-matching results?
Presumably, once the city has been selected, the software could locate the matching state/province and country, and fill these in.
What if the user needs to correct a mistake? What if a customer calls up and says that the company has got his address wrong: he's not in Portland, Maine, but in Portland, Oregon. Would the user know that they had to find the customer record, then select a different city from the drop-down? Or would they go to the city table, find Portland, and change its state field from ME to OR? In which case, of course, all Portand ME residents would be moved to OR?
I'm not saying this is a reason for not doing it the way you suggested, but I wonder how you handle these user-interface issues.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
Your idea of putting cities, states / provinces, countries, etc. in their own tables, each with a foreign key from their respective parent tables, makes perfect sense from a database design point of view.
But how do you envisage it would work from the user interface point of view? When entering a new address, would the user enter the street, etc., then use a drop-down to select the city? Or perhaps do a search for the city, and choose the correct one from a set of close-matching results?
Presumably, once the city has been selected, the software could locate the matching state/province and country, and fill these in.
What if the user needs to correct a mistake? What if a customer calls up and says that the company has got his address wrong: he's not in Portland, Maine, but in Portland, Oregon. Would the user know that they had to find the customer record, then select a different city from the drop-down? Or would they go to the city table, find Portland, and change its state field from ME to OR? In which case, of course, all Portand ME residents would be moved to OR?
I'm not saying this is a reason for not doing it the way you suggested, but I wonder how you handle these user-interface issues.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
Imaginecorp
IS-IT--Management
Street, City, State etc. in their own tables... Amazing. Make NO sense even "from a database design point of view..." Forget about praticality
Mike Lewis
Programmer
Mike (Yearwood),
Thanks for the clarification. However, I'm still doubtful -- not least because I took a similar route myself once, and failed.
In my application, we had a many-to-many relationship between people and addresses. The people were doctors. One doctor had several addresses: home, practice, hospital(s) where she/he worked part time, and so on.
Meanwhile, one address could have several doctors. Several doctors working at the same practice or hospital, for example.
I know this isn't quite the same as what you're suggesting, but I feel the same sort of problems could arise.
The first problem was that the user couldn't get used to entering new addresses in a two-stage process. They first had to enter the address into the address table, and then assign it to the doctor in question. We made it as transparent as possible for them, but they still continually asked: "Why can't we just type the guy's address next to his name?"
The other (bigger) problem occurred with changes of address. We never succeeded in making the users understand that a doctor moving to a different practice was not the same as a doctor reporting a mistake in his address. (The first involved linking the doctor to a different [new or existing] address record; the second required a simple change to the address table.)
We tried taking the "mistakes must be cleared by a supervisor" route, but it led to huge user frustration ("Gosh, I only made a simple spelling mistake. Why do I have to get the boss to fix it?").
We also had constant problems caused by users failing to find an existing address to assign to a doctor. You mentioned using a combo box to select the city. In our cases, there were many thousands of addresses in the system, so a combo box wouldn't have worked. Instead, the user had to search for the address; there were various ways of doing that, such as searching on postcode or a substring from the main part of the address.
Invariably, the search would fail, even though the required address was in the table. That happened typically when the user made a mistake in entering the search term. When that happened, the user would immediately conclude that the address hadn't yet been entered, and would go ahead and enter it. So we had many duplicate addresses in the table -- often with minor variations in spelling.
I don't want to discourage anyone from properly normalising their database along the lines you suggested. If anyone can suggest ways of overcoming these problems, I'd like to hear from them.
In our case, we concluded that the whole thing was too much trouble. We knew that making the address a unique attribute of the person was not proper database design, but it was hugely more convenient, so that's what we ended up doing.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
Thanks for the clarification. However, I'm still doubtful -- not least because I took a similar route myself once, and failed.
In my application, we had a many-to-many relationship between people and addresses. The people were doctors. One doctor had several addresses: home, practice, hospital(s) where she/he worked part time, and so on.
Meanwhile, one address could have several doctors. Several doctors working at the same practice or hospital, for example.
I know this isn't quite the same as what you're suggesting, but I feel the same sort of problems could arise.
The first problem was that the user couldn't get used to entering new addresses in a two-stage process. They first had to enter the address into the address table, and then assign it to the doctor in question. We made it as transparent as possible for them, but they still continually asked: "Why can't we just type the guy's address next to his name?"
The other (bigger) problem occurred with changes of address. We never succeeded in making the users understand that a doctor moving to a different practice was not the same as a doctor reporting a mistake in his address. (The first involved linking the doctor to a different [new or existing] address record; the second required a simple change to the address table.)
We tried taking the "mistakes must be cleared by a supervisor" route, but it led to huge user frustration ("Gosh, I only made a simple spelling mistake. Why do I have to get the boss to fix it?").
We also had constant problems caused by users failing to find an existing address to assign to a doctor. You mentioned using a combo box to select the city. In our cases, there were many thousands of addresses in the system, so a combo box wouldn't have worked. Instead, the user had to search for the address; there were various ways of doing that, such as searching on postcode or a substring from the main part of the address.
Invariably, the search would fail, even though the required address was in the table. That happened typically when the user made a mistake in entering the search term. When that happened, the user would immediately conclude that the address hadn't yet been entered, and would go ahead and enter it. So we had many duplicate addresses in the table -- often with minor variations in spelling.
I don't want to discourage anyone from properly normalising their database along the lines you suggested. If anyone can suggest ways of overcoming these problems, I'd like to hear from them.
In our case, we concluded that the whole thing was too much trouble. We knew that making the address a unique attribute of the person was not proper database design, but it was hugely more convenient, so that's what we ended up doing.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
Imaginecorp
IS-IT--Management
In other words re-train the client to use your application your way. If we ever did that to our clients, we would be shown the door so fast it would make our head spin. Whatever happened to, IT exists to make the users life easier, not the other way around, was this not the promise for computerization?
I am sorry, but this multiple table scenario makes absolutely no sense neither from a design nor a practical point of view.
Now the user, rather than entering a city, state, zip code has to click on a drop down which will display all cities starting with a “T”, then a “To”, then a “Tor”… (Interactive change) then scrolls down to find the city state and zip code he/she wants… Will be fun for a user that has client’s world wide…
I am sorry, but this multiple table scenario makes absolutely no sense neither from a design nor a practical point of view.
TamarGranor
Programmer
Seems to me that city, state, postal code is a good candidate for normalization, while street address may not be. (And yeah, I've tried the many-to-many of people and addresses and it's tricky.)
I know there are plenty of apps out there using a CSZ table of some sort because I often get asked for my zip code rather than address when calling customer service type places. To me, that's the obvious input item. You have the user enter the zip/postal code, and then if that's ambiguous (as many are in the US system), let the user select the correct town from the list of towns in that zip.
OTOH, I wouldn't pull city, state and zip each into a separate table. I see the value in some apps of a separate states table that links long state name with abbreviation and maybe some other data, but it's hard to see the value of a cities table unless the app is about cities as entities.
Tamar
I know there are plenty of apps out there using a CSZ table of some sort because I often get asked for my zip code rather than address when calling customer service type places. To me, that's the obvious input item. You have the user enter the zip/postal code, and then if that's ambiguous (as many are in the US system), let the user select the correct town from the list of towns in that zip.
OTOH, I wouldn't pull city, state and zip each into a separate table. I see the value in some apps of a separate states table that links long state name with abbreviation and maybe some other data, but it's hard to see the value of a cities table unless the app is about cities as entities.
Tamar
Mike Lewis
Programmer
Mike,
I agree. I did point out that the situation I described was different from your suggestion. My point was that similar user interface problems could arise, in my opinion.
The situation in North America is somewhat different from the rest of the world. All US addresses have a city, a 2-letter state code and a zip code. I guess Canadian addresses are similar, with a city, a 2-letter province code and a postal code.
Elsewhere, addresses are much more free-format. A city field wouldn't work in Scotland, because most addresses aren't in cities. In England, some people try to use a county field in place of state or province, but that doesn't work in large metropolitan areas, like London, or in Scotland, neither of which have counties.
I agree that there issues regarding searching (in Scotland, searching is always done on postcode, which uniquely identifies the entire address apart from the house number). But I'm not convinced that normalising the address in the way you described is the best solution.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
I agree. I did point out that the situation I described was different from your suggestion. My point was that similar user interface problems could arise, in my opinion.
The situation in North America is somewhat different from the rest of the world. All US addresses have a city, a 2-letter state code and a zip code. I guess Canadian addresses are similar, with a city, a 2-letter province code and a postal code.
Elsewhere, addresses are much more free-format. A city field wouldn't work in Scotland, because most addresses aren't in cities. In England, some people try to use a county field in place of state or province, but that doesn't work in large metropolitan areas, like London, or in Scotland, neither of which have counties.
I agree that there issues regarding searching (in Scotland, searching is always done on postcode, which uniquely identifies the entire address apart from the house number). But I'm not convinced that normalising the address in the way you described is the best solution.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
TamarGranor
Programmer
I agree with you on the value of standardization. I just don't see City and Customer as parallel constructs.
Tamar
Tamar
Imaginecorp
IS-IT--Management
Sure Mike, But I fail to see what you will accomplish by putting Cities in a drop down. The only way to get a complete list of cities involves buying it, this adds to the cost of the application... businessess grow, now the user wants all cities in Mexico, buy a list again and incorporate it in cities table... where does this stop.
All this "...standardization and repeatable known construction techniques..." is great in a classroom but I am sorry not in the real world.
Give the user some credit. We have never been asked to allow the users to select a city state zip from a dropdown as I think our clients would fire any of their employees who could not enter an address correctly...
Imaginecorp
IS-IT--Management
Mike; we had this feature about 5 years ago. It was just a PIA to maintain. The users were not very happy with it though, but our clients in those days were doing business in the USA, Mexico & Canada. Like you we allowed our users to build the Cities with the State and Zip code tables… The problems started with our international and overseas clients… The remark “Give the users some credit…” is a direct quote from one of our clients.
But if your clients are happy, who are we to argue…
But if your clients are happy, who are we to argue…
Olaf Doschke
Programmer
True, Imaginecorp.
Regarding internet shops for example, as a customer I simply would like to be able to enter my adress as it should be printed. There are so many differences in international adresses, that I would do it that way:
Ask for Country,Zip or Postal Code,City,Street,House number. Then compose the address as you would print it and allow your customer to edit it as it should be printed, because he should know best and store that as additional memo.
Then you have the mixture of single informations you can use to compare and verify adresses and the address, as it should work.
Bye, Olaf.
Regarding internet shops for example, as a customer I simply would like to be able to enter my adress as it should be printed. There are so many differences in international adresses, that I would do it that way:
Ask for Country,Zip or Postal Code,City,Street,House number. Then compose the address as you would print it and allow your customer to edit it as it should be printed, because he should know best and store that as additional memo.
Then you have the mixture of single informations you can use to compare and verify adresses and the address, as it should work.
Bye, Olaf.
- Status
