Need help seperating data

[iwasnevy]

Hi all

Wasn't sure if this should go here or in the import/export forum, so forgive me if it's in the wrong spot.

Here's the problem, and I *think* a calculation will solve it, 'cause I've seen some pretty slick calculations to modify and format field data. I don't even know where to start though, 'cause calculations are one of my weak points!

I'm migrating some data from an older database into my company's new one. In the old database, there was a field labeled "contact name" which was abused with data designating both a mother and father's first names. At least they were consistent - the data in this field is "Mo: name Fa: name". There are about 1200 records with data in this field formatted in this manner.

In the new database, there are fields to accomodate this: ParentAfirst and ParentBfirst

Since they consitently used a "mo:" and "fa:", I was hoping to come up with a calculation that would seperate the names out to the two new fields in the new database via a script that I could run through all the records once. Something like "take the words between mo: and fa: and put them in ParentAfirst" then "take the words after fa: and put them in ParentBfirst". Some of the records have both a first and last name, hence me saying "all the words between..." I know I'd still have more data to seperate out after the calculation does it's thing, but there's few enough of them that I could deal with that manually.

Whew. Any calculation gurus out there care to take this one on? It would be magic if it could happen!

thanks in advance!

p.s. can I just be a total geek for a sec and point out how COOL databasing is? It fascinates me, what can be done with data!

Edited July 14, 2009 by Guest

[comment]

Perhaps I am missing something, but it seems rather trivial:

Let ( [

v = Substitute ( contact name ; [ "Mo: " ; "" ] ; [ "Fa: " ; ¶ ] ) 

] ;

GetValue ( v ; 1 )

)

and for the father's name use GetValue ( v ; 2 ) at the end.

This assumes there are no carriage returns in the field - though that too could be handled, if necessary.

[Lee Smith]

Wasn't sure if this should go here or in the import/export forum, so forgive me if it's in the wrong spot

Close enough. :

Not sure I'm totally understand your problem,

the calculations might be as simple as

LeftWords (Mo; 1)

and

RightWords (Mo; 1)

You can also attach a file here so that we can see first hand what you are describing.

HTH

Lee

[iwasnevy]

Perhaps I am missing something, but it seems rather trivial:

LOL trivial for someone who knows calculations! : Thanks - I appreciate the help - I'll give it a try in the morning. No carriage returns, although some of the records deviate a bit from that mo: and fa: consistency with last names as well, and I've even seen a phone number or two and a couple records with a little note in there behind one of the mo's and fa's names. But as long as I know where the data's going, I can fix those little inconsitencies. But fixing 1200+ records requires a script with a calculation!

@ Lee Smith:

In a nutshell, I'm trying to take two seperate pieces of data (deliminated by the words "mo:" and "fa:") which is containied in one single field and split it into two seperate fields.

If this seems simple, keep in mind that 6 months ago, I wouldn't have known a database if it bit me in the leg, and now I'm fairly confident at developing them in FM. At this stage, I'm a total noob with calculations, but I'm learning! :

Edited July 14, 2009 by Guest

[Lee Smith]

copy a few examples of the actual field data.

Need to see how the mo and fa are seen in the field.

Lee

[Lee Smith]

Hi comment, I didn't see your post until I read the OP reply.

Looks like it was a tie anyway.

Lee

[iwasnevy]

@ Lee

Sorry, I forgot to mention the file - I'm hesitant to upload it 'cause it's full of our actual customer data at this point. But here's the fields and the data it contains:

Field: "ContactName"

Data it currently contains: "Mo: Susanne Fa: John"

It should be split to two seperate fields in the new database:

ParentAFirstName "Susanne"

ParentBFirstName "John"

I think I have a copy of the file laying around without a ton of our data in it - I'll look for it and post it.

[comment]

fixing 1200+ records requires a script with a calculation!

Not necessarily: you can create two calculation fields (result is Text), see the results and fine-tune the formula. Then when you're satisfied, change them to text fields, and you're done (at least with stage one). Just make sure you do NOT set the storage of the calculation fields to "Do not store…".

[iwasnevy]

For what it's worth, once the data is split once, since some of the data contains a first and last name for each deliminater, (example below), I'll then run another calc to split those into a ParentAFirstName, ParentAlastName, ParentBFirstName, ParentBLastName, hopefully using the space as the deliminater.

sample of field "ContactName" which contains a first and last name:

Data in the field: "Mo: Susanne Clark Fa: John Walker

There's even a couple that have things like "Mo: Susanne don't call", or "Mo: Susanne work 303-555-5555"

Speaks volumes to planning a database for future expansion, huh?

Edited July 14, 2009 by Guest

[iwasnevy]

Not necessarily: you can create two calculation fields (result is Text), see the results and fine-tune the formula. Then when you're satisfied, changed them to text fields, and you're done (at least with stage one). Just make sure you do NOT set the storage of the calculation fields to "Do not store…".

Good idea. I'll have to play with your code tomorrow to figure out exactly what it does : so I can tweak it for stage two of the 'cleanup'. While I'm thinking about it though, how can I do a calculation that repsonds to a field with more than one word in it - for filtering out the record that are fixed, leaving only those with both a last and first name? I'll have to give this some thought.

[comment]

how can I do a calculation that repsonds to a field with more than one word in it - for filtering out the record that are fixed, leaving only those with both a last and first name?

I am afraid I don't understand the question.

[iwasnevy]

Found the sample file! Now don't laugh at the field names - Renaming some of the fields to make sense is on my list... :

For now, I imported that 'contactname' field into the emergency contact field. I'll create some new intermediary fields to dump the data into while I work with it, probably usign comment's suggestion about the calc fields.

File.zip

[iwasnevy]

Don't worry, I didn't understand it either. : I'm just thinking out loud about how to do a search or find based on two or more words existing in that one field so I can run the second split on only the found records which need it.

When it comes down to it, I think there's only a handful of records which contain both a first and last name, and I can probably take care of those by hand just as quickly, but it would be helpful to search based on the field containing more than one word so I don't have to sift through to find them.

Edited July 14, 2009 by Guest

[comment]

You could search for a space in the field (enter a quoted space as the search criteria).

[iwasnevy]

You could search for a space in the field (enter a quoted space as the search criteria).

duh. sometimes simplicity is best. my brain hurts. :hair:

[iwasnevy]

Comment, your solution worked like magic! You saved me hours of work. Can I buy you a beer? PM me.