Moving field contents to a related table

[Valdrin]

Hi everyone,

I am in the middle migrating a flat file database to filemaker pro and have run into a little problem.

I have imported all the records into filemaker pro 11 and now I am need to move the contents of several fields to a child table. The old database only allowed for three phone numbers, and had fields for each number and its extension, like so:

Phone1 Extension1

Phone2 Extension2

Phone3 Extension3

I need to move each phone number to a new record in related child table that has fields for: area code, number, and extension.

There are about 10000 records so I really want to automate the process with a script, but I am not having much luck with it.

Any and all help with this will be greatly appreciated.

[comment]

Import three times, each time importing the ParentID and one of the phone/extension pairs.

[Valdrin]

That makes sense, but the part I am really having difficulty with is trying to convert the phone fields from the old database, which use just one field for the area code and number, into the new database which uses a separate field for the area code.

What makes things even harder is that some of the phone numbers have parentheses around the area code and other do not.

[comment]

It's hard to say anything specific based on so little information. In general, you need to look for some consistency in the old data, for example: do all numbers have the same number of digits? If yes, is the area code always the first x digits (I don't know your location, and this is not true in all countries)?

[Søren Dyhr]

Since we have our feets solidly in the OS X camp, should following not be ignored:

http://tokerud.typepad.com/filemaker/2006/06/new_record_with.html

...when bearing in mind that repeating fields carries the same sets of clauses as portalized stuff.

I've attached a template showing how I tweaked Bruce/Janets script. It works as follows:

Create a handfull of records in the layout initially arrive in, and when done press the button, and see the portal below fills.

--sd

mult.zip

[Valdrin]

It's hard to say anything specific based on so little information. In general, you need to look for some consistency in the old data, for example: do all numbers have the same number of digits? If yes, is the area code always the first x digits (I don't know your location, and this is not true in all countries)?

The lack of consistency in the phone numbers was driving me crazy -- they used a random mix of dashes, periods and parentheses to separate the numbers, and some don't have area codes at all.

I just realized that I can use search and replace to remove everything except the digits. That will leave me 10 digit numbers with area codes and some 7 digit number without the area code. The 7 digit numbers are not a problem, but I am not sure about the script for the 10 digit ones.

I need make a script that will do the following

If the number field is not empty

Create a new recored on the child table

moved the first 3 digits to the AreaCode field and the other 7 digits to the ManNumber field

Anyone care to say what would be the best way to do the above?

Thanks!

[comment]

I would use an interim file for the processing. First do the three imports, so that you have a separate record for each number. Then define two calculation fields:

cPhone =

Right ( Filter ( Phone ; "0123456789" ) ; 7 )

cArea =

Let ( 

num =  Filter ( Phone ; "0123456789" ) 

; 

Left ( num ; Length ( num ) - 7 )

)

Check your results and if everything is all right, import the calculations fields (along with the parent ID and the extension) into the final destination.

[Valdrin]

Since we have our feets solidly in the OS X camp, should following not be ignored:

http://tokerud.typepad.com/filemaker/2006/06/new_record_with.html

...when bearing in mind that repeating fields carries the same sets of clauses as portalized stuff.

I've attached a template showing how I tweaked Bruce/Janets script. It works as follows:

Create a handfull of records in the layout initially arrive in, and when done press the button, and see the portal below fills.

--sd

That is interesting. I've never played around with AppleScript, but it is something that I should probably learn.

[Valdrin]

I would use an interim file for the processing. First do the three imports, so that you have a separate record for each number. Then define two calculation fields:

cPhone =

Right ( Filter ( Phone ; "0123456789" ) ; 7 )

cArea =

Let ( 

num =  Filter ( Phone ; "0123456789" ) 

; 

Left ( num ; Length ( num ) - 7 )

)

Check your results and if everything is all right, import the calculations fields (along with the parent ID and the extension) into the final destination.

Thank you, this is a much better solution than the script I was trying to make.

[Søren Dyhr]

That is interesting. I've never played around with AppleScript, but it is something that I should probably learn.

I don't really know, punters usually use the Redmond Os, and turn this into a byzantine endevour.

Bildung, should usually focus on something more than whims, so if an classical angle exists to the topic would it be better than any struggle with the weird syntax AS in reality possesses :

--sd