Which text functions should I use?

[Georgegette]

Hello members!

I have a line of text which I am having difficulty parsing into appropriate fields. Example: "Ridgefield, Ct 10035 USA". This is a line from the billing address form on my web site. City, State, Zip Code, and Country are all individual text fields which the customer fills out. The problem is that the software that decrypts my orders downloads into un-dilemmanated text which means that I have to parse the data before it can be imported into a proper database. This means my example of "Ridgefield, Ct 10035 USA" is one un-dilemmanated line.

What I want to do is to get Ridgefield in one field (some cities have more then one word, like New Haven), Ct in another (some times multiple words are entered into this field like North Dakota), 10035 into another (sometimes letters are part of zip code info from out side of the US), and of course USA( same problem as the rest).

I have experimented with text functions such as trim, middle, middlewords, leftwords, left, and postion. I have only been able to isolate the "d" returned in Ridgefield rather then the whole word. I need everything left of the coma in one field. Of course I have no idea how I'm going to address the state, zip code, and country part of it!

Any suggestions???

Thanks Georgegette

[LiveOak]

The only good solution is to prevent the data from being merged. Everything else will be broken by some unique (mostly non-USA) format for the address. Cities, states, and postal codes are different in many countries and will probably confound any simple approach that doesn't match against a worldwide directory of addresses. To capture everthing left of the comma is simple, but far from a total solution:

Left(text, Position(text, ",", 1,1) - 1)

I'd work on prevention, I don't think a cure exists.

-bd

[Keith M. Davie]

Is there some reason to not use Custom Web Publishing of your FileMaker database, i.e., integrate it into your website? There would then be no need for one to use a "software that decrypts my orders downloads into un-dilemmanated text".

[Georgegette]

That or something similar was suggested by a friend who is a database consultant. If I could afford that, then I would hire that friend of mine to do the job. I am new to building databases. I have a small e-commerce business that can not afford to pay for such professional services at this time. I must do the work myself. Since I am on a shoestring budget I have to rely on the shopping cart which was provided by my hosting company.

The web site was designed around that shopping cart. Not to mention if I restructure any of the software which came with my package then I am on my own, in terms of tech support. That would mean that I would have to be prepared to have a web designer redo my site and pay the fees of the consultant. Until I'm in a position to be able to afford those fees comfortably, I have to do the best with what I have.

Thank you, Esterphanie

[LiveOak]

The low cost alternative is to design a function that works some of the time and hand review and correct each address.

-bd

[Georgegette]

I think you are right! I have been using just such a calculation for the past year. My database pal suggested that I manually add commas into that line to seperate the words then use text function calculations similar to the one that was suggested by one of the first replies to this post. I am looking for ways to minimize manual input so that I can process my orders quicker.

Thanks Live Oak!

[kennedy]

I am assuming changing how the website generates the field is not an option... simply being able to get some non-space delimiters would help tremendously.

Given that, I'd shoot for minimizing the manual effort. Most likely you can do much better than manually delimiting and then letting functions do the rest. For example, if many of your addresses are US, you could assume standard US formatting and the majority of the records will be done without effort. Even some foreign ones will come in okay. The erroneous ones you can help highlight with some canned Finds.

First split it at the comma, everything left goes in the city.

Then let's try to set it up so that it works for the USA and you just

have to delete excess text for addresses with multi-word state, postalcode,

or country...

Which text functions? Like this...

cityend = Position(input, ",", 1)

city = Trim(Left(input, cityend-1))

rest = Trim(Right(input, Length(input) - cityend))

state = LeftWords(rest, WordCount(rest) - 2)

country = RightWords(rest, WordCount(rest) - 2)

codestart = Position(rest, " ", 1)

coderest = Right(rest, Length(rest) - codestart)

code = LeftWords(coderest, WordCount(coderest)-1)

The result of that is that all addresses that do not have spaces in state, code, or country will be fine with no manual effort. Whereever you do have a space, you'll end up with extra text in each of the other two fields. To fix that, you'll just have to go edit those records. No cut-n-paste... just simply delete excess.

To make those easy to find, define an additional field "NeedsCorrection" that is set to 'WordCount(rest) > 3'. Then find all records with NeedsCorrection true, delete the excess text as appropriate, and then clear NeedsCorrection.

HTH.

[Georgegette]

(LOL) That sounds pretty clever! I will give it a shot and hope that I understand you correctly. I noticed that you "newbie" is next to your name. If your a newbie then I'm still a fetus, because I'm going to have print you idea and then it's going take time for me to absorb it.

I'll get back to you (smiling)!

Thank you, Georgegette

[Georgegette]

Your idea worked up to a point when I became confused.

cityend = Position(input, ",", 1)

city = Trim(Left(input, cityend-1))

rest = Trim(Right(input, Length(input) - cityend))

state = LeftWords(rest, WordCount(rest) - 2)

country = RightWords(rest, WordCount(rest) - 2)

codestart = Position(rest, " ", 1)

coderest = Right(rest, Length(rest) - codestart)

code = LeftWords(coderest, WordCount(coderest)-1)

This part worked just like you said with the exception of when the text function, Positon was used. I modified it by including the occurrence.

codestart=Position(rest," ",1,1)

I only bring that up encase someone else tries these calculations. I'm confused about the "NeedsCorrection" field.

To make those easy to find, define an additional field "NeedsCorrection" that is set to 'WordCount(rest) > 3'. Then find all records with NeedsCorrection true, delete the excess text as appropriate, and then clear NeedsCorrection.

I created a field "NeedsCorrection", made it a text calculation [WordCount(rest)>3] and it returned "1" in the NeedsCorrection field text box. What am I missing??

Thank you, Georgette

[kennedy]

I'm confused about the "NeedsCorrection" field.

To make those easy to find, define an additional field "NeedsCorrection" that is set to 'WordCount(rest) > 3'. Then find all records with NeedsCorrection true, delete the excess text as appropriate, and then clear NeedsCorrection.

I created a field "NeedsCorrection", made it a text calculation [WordCount(rest)>3] and it returned "1" in the NeedsCorrection field text box. What am I missing??

Actually, what I intended was to make it a normal Number field. Then as you import the other fields, setting them as mentioned... you also import into NeedsCorrection the result of WordCount(rest)>3, which will either be 0 or 1. If 1, then you know at least one of your state, zip, or country fields have extra data in them. Find on NeedsCorrection = 1 and clean up those fields, setting NeedsCorrection to 0 once you've cleaned that record.

Was that clearer?

(sorry about the Position typo)

[Georgegette]

Hello Kennedy,

I made the "NeedsCorrections" field into a number within the calculation text box. I'm still getting the number "1", whether the city/state/Country has one or two words. Since it is a calculation field it won't allow me to modify it's content to a "0".

Then if the city is two words, for example, all of the previous calculations are based on "x" amount of spaces between the text, won't that through off the previous calculations? I have included with this post a copy of the parsing database that I am working on. The first field "OrderText" is order just the way it is decrypted into note pad.

Naturally I changed the customers order info to protect their privacy. Since almost all the subsequent fields are calculation fields, if you want to make the state a two word state to check it out, it must be modified in the "Order Text" field. You were apologizing about a typo??? Have you checked out my run on sentences? (smiling)

Thank you, Georgegette

Parsing Orders Copy Copy.zip

[kennedy]

You have NeedsCorrections as 'WordCount(rest > 3)'.

Try 'WordCount(rest) > 3'.

As for the making corrections part... I was assuming auto-entered calculations into editable fields. Using calculation fields, you'll need to do it slightly different:

Add an editable Corrected field.

Then do a find on Corrected = 0 and NeedsCorrections = 1

Similarly, create CorrectionFoo versions of the fields that may need correcting.

Then you can create a calculation field for each called CorrectedFoo that

= If(IsEmpty(CorrectionFoo), Foo, CorrectionFoo)

That should get you working.