ato duplicate record dependant on field value?

[Sojiki]

Hello folks

I'm new to the whole filemaker thing and finding my way fairly slowly. Hopefully this question is in the right place.

I'm using Filemaker 7 on a mac and a pc.

I have a list of address's database with fields like this:

street number, street name, town, county etc

for each street name there are several street numbers as such:

1;2;4;6;7;8 harley street london etc

what i need to be able to do is automatically create multiple records so that i have (in this case) 6 records with one street number thus:

1 harley street

2 harley street

4 harley street etc.

the reason i need it automated is I have 15000 odd records that need to be separated. any ideas

thanks in advance

[Genx]

Need more examples of what you're after...

[Sojiki]

ok

current fields: street no, street name, town.

current records are as such:

'1;2;4;6;8', 'harley street', 'london'

'4;5;7;10;12', 'queen street', 'london'

'1;3;4;6;7', 'george street', 'southampton'

etc.

what i want is additional records made up from these like this:

'1', 'harley street', 'london'

'2', 'harley street', 'london'

'4', 'harley street', 'london'

etc.

because i have to process the existing 15000 records (to create something like 50000 records) i need to automate the process. so i was thinking of something like being able to count howmany numbers are in the street number field, create that many duplicate records and then use 'right' or 'left' function to fill the street number field with the relevant number. the only other way i think would be some sort of relted value list or array function all of which is currently beyond my knowledge of Filemaker.

[Genx]

Ohhhhhhhhhh, righto.

Well, the script will be a series of loops based on the fact that your "street numbers" are all seperated by semi colons.

The functions i suggest you use are: PatternCount, Position, and most probably Middle... look them up in the help so you can isolate the appropriate street number.

You will have to use several globals to store temporary values.

Anyway the jist is...

#Main Loop to run through all your records

Show All records

Go To Record[First]

Loop



..#Find the number of street numbers within the current record and store as a global

..Set Field[Globals::Global1 ; PatternCount(YourStreetNumberField ; ";") + 1]

..#Set the street numbers of the current field to a global so it can be accessed in a different table, additionally, CRLF seperate the street name and town, country etc. values in the same global

..Set Field[Globals::Global2 ; YourStreetNumberField & CRLF char & StreetField & CRLF char & townField & CRLF & countryField

..Go To Layout(your new layout attached to your new table with your new data structure)

..Set Field[Globals::Global3 ; 1]

..Loop

....New Record / Request

....#Check how many street numbers exist

....If[Globals::Global1 = 1]

........Set Field[NewTable::StreetNumber ; GetValue(Globals::Global2 ; 1]

....Else

........#Parse Out the appropriate street number from Global2 based on Global3

........Set Field[NewTable::StreetNumber ; Parsed Street Number based on current value stored in Global3]

........I can't work the parsing at out at 1 in the morning... if someone else doesn't by tommorow, i'll take another look.

....End If

....Set Field[NewTable::StreetName ; GetValue(Globals::Global2 ; 2]

....Set Field[NewTable::Town ; GetValue(Globals::Global2 ; 3] 

....Set Field[NewTable::Country ; GetValue(Globals::Global2 ; 4] 

....Exit Loop If[Globals::Global3 = Globals::Global1]

....Set Field[Globals::Global3 ; Globals::Global3 + 1]

..End Loop



Go To Record[Next ; Exit After Last]

End Loop

[Genx]

Okkayyy, getvalue() apparently doesn't exist in FM7, so either use multiple globals, or use some other way to store and parse out the temporary values.