data transfer 1

[yuchieh]

I am new to FileMaker Pro.

I have a database with one field "Address", which contains "address1, address2, city, state, zip". Is there a way to break the data and put it in 5 separate fields "address1", "address2", "city", "state", and "zip"? There are almost 2000 records in the db. kind of big if cleaning up the data one by one.


Thanks

 

[JeanW]

Is your Address field a repeating field ?

 

[yuchieh]

no it's not.

 

[JeanW]

Is there always the same sign/space between Add1, 2, etc ?

What we are looking for is a consistent sign, hard return, etc. to start parsing out the diff. textblocks.

HTH

 

[yuchieh]

I don't think there is any consistent sign for each address block. I think, in this case, I might need to clean up the data manually.

Can you tell me, if there was a consistent sign, how to parse out the different textblocks?

Thank you.

 

[JeanW]

I use two methods, depends, a calculation or a script.

Lets say you have your field with city, state and zip = address = City, State, zip and the values in this field looks like Merida, CA 97200
and you want those value in new fields, city_ct, state_ct and zip_ct with a calculation :

city_ct = Left(address; Position(address; ","; 1; 1) - 1)
state_ct = Middle(address; Position(address; ","; 1; 1) + 2; 2)
zip_ct = Right(address; Length(address) - Position(address; " "; 1; PatternCount(address; " ")))

Here we’re looking for the “,” and the “ “ (space) sign as constant.
In city_ct we’re starting from the left, looking for the “,” sign (1), going 1 position back (-1) to grap the value. (see Position in help file)
The same for the state and zip value.

If you want to script this for all the records, make fields cityNew, stateNew and zipNew :

ParseAddress script :

Goto Record/Request/Page(First)
Loop
SetField (“cityNew”; Left(address; Position(address; ","; 1; 1) - 1)
SetField (“stateNew”; Middle(address; Position(address; ","; 1; 1) + 2; 2)
SetField (“zipNew”; Right(address; Length(address) - Position(address; " "; 1; PatternCount(address; " ")))

Go to Record/Request/Page(Exit after last, Next)
End Loop

HTH

 

[yuchieh]

Hi,

Thank you for the information/coding. I will take a good look and learn it.

Another question. I used to do Cold Fusion and SQL for almost 5 years. Now I switch to FileMaker Pro (a new job, new s/w to me). I have been going through the book "Filemaker 6" by Chris Kubica on my own from the beginning to learn it. But I am kind of stuck at portal, many-to-many relationship part. Will I benefit more if I go to classes? Or do you recommend other good FM books?


Thank you.

 

[JeanW]

As a teacher/programmer I would say : go for classes, depends on who's paying...
Those classes are very good, but if 'the boss' is not paying the bill...you're better of with books, like the one you already have.

The best way to become familiar with the different relationship types is to work through examples. If you want I can send you a few basic examplefiles.

An other very good book is the Scriptology from Matt Petrovski and John Mark Osborn. You don't have to read the whole book, just grab it when you stuck, 9 out of 10 you find the answer there.
Credit to both for the following...

As far as I see it after years working with FileMaker, is that the level of understanding you want to achieve with FM rests entirely on one concept : understanding functions...

This may seen a little drastic at first, however, if you understand functions you will be light years ahead of the learning curve whenever you see someone else's solution, or when you have something explained to you.

Learn every function in a way that allows you to understand it, then practice it at least 3 different ways. If you do this, and write them out on paper, you will always know how to take small bites from a big problem.
Working from the basics in this way will provide you the confidence that allows you to feel like you can do anything.

There are more than 120 different functions. Some you may use all the time, some never, but all are important if you want to know FM inside out.
The key is to learn those functions, there's no way around it. If you pick 5 or 10 a day, you can learn them all in about a month.
If you're willing to do that, you may be able to ask for a raise at work.....

HTH

 

[yuchieh]

Thank you for the tips.

Boss will pay. So I will probably take Intermediate class, maybe advanced as well. Or I should just jump to 3-day Developer classes.

The book you mentioned, Scriptology from Matt Petrovski and John Mark Osborn. Currently, it's out of print, maybe because it's for the older versions of File Maker Pro. oh well..

guess I will start working on functions.

Thanks again.