I hope someone may help me out with this one...
I have a large list of assets. Each asset is in one field and
structured like this:

Light, bulb, 15w, 120v, incandescent, tubular, t6

What I would like to do is create a script that will remove each word
and have it become its own record so that I may restructure the asset
into a standardized form:

Bulb, Light, Incandescent, 15W, 120V, T6

Thank you in advance,
Steve

On 7 Oct, 09:37, Steve <sbere... at (no spam) gmail.com> wrote:
I hope someone may help me out with this one...
I have a large list of assets. Each asset is in one field and
structured like this:

Light, bulb, 15w, 120v, incandescent, tubular, t6

What I would like to do is create a script that will remove each word
and have it become its own record so that I may restructure the asset
into a standardized form:

Bulb, Light, Incandescent, 15W, 120V, T6

Thank you in advance,
Steve

Hello Steve

I am going to assume that you're:
a) Running FileMaker 10. This script uses features unique to FileMaker
10.
b) Have all the data in FileMaker already, albeit each asset has its
own record with all info for that asset in one field
c) You wish to separate out the asset info into individual fields (not
records as you say above)
d) Working with a copy of the data - not the original data. This is
necessary to prevent data corruption while you are refining your
script.

A quick digression before I get started. If you do not already have
info in FileMaker and it's sitting in a comma delimited text file then
you can use the "Import" function to pull all the info into separate
records and fields.

Now, back to the original scenario. Here's some code off the top of my
head - so please debug it on your system:
*First create some fields. Give them names as follows Characteristic1,
Characteristic2, Characteristic3 etc

show all records
go to record FIRST

//1. Outer loop is to run through all records ie through the whole
list of assets
Loop
SetField [CombinedInformationField; Replace
(CombinedInformationField;",";"¶"] // changes the field where all
info is stored. Sets all commas to be carriage returns "¶" (I hope
that character comes out correctly in the email.) Aim of this is to
set things up so that we can use the wonderful "GetValue()" function
that can pick out each item in turn

//2. Inner Loop is to run through all the characteristics of an
individual asset
Set Variable [$Item,1] // sets a variable called "item" to be
"1" . This will be incremented from 1 to 6 so that we can extract all
the items ie characteristics in the asset
Loop
Set Field By Name ["Characteristic" & $Item; GetValue
(CombinedInformationField;$Item) //This is where it all happens. We're
setting "Characteristic1"..."Characteristic6" fields that you created
at the start. These field names must exactly match what you've set the
field name to be. We're going to set Characteristic1 to be line one,
characteristic2 to be line 2 etc. Remember we've replaced the commas
with carriage returns so that each characteristic sits on a line by
itself.
EXIT LOOP if $Item>6 //that's the number of characteristics that
you have for each asset
END loop

Go to record next, exit after last
End loop

Some further thoughts:
1. If at all possible, go back to the original text file where the
information came from. If you import the text file as a comma
separated file, then it will import it as desired with each item in
it's own field. It will even create the fields for you when you do the
import.
2. Another idea. If all the programming above seems like it's going to
be difficult to get right, try this idea instead. It's a bit fussy,
but less technical. Export all the information out of FileMaker as
"Tab" delimited. Now that you've exported it you can re-import it into
a new file as "comma" delimited. FileMaker should see the commas that
are already there and pop each item into its own field. You may need
to first import the file into Excel first to get it into the right
format ie each item in its own column.

I hope that helps to get you started. Once you've got the script
working you can import the raw data into your development file and run
it for real. After that, change the field names to something more
meaninigful than characteristic1 etc.

Regards
Kevin Smith

I hope someone may help me out with this one...
I have a large list of assets. Each asset is in one field and
structured like this:

Light, bulb, 15w, 120v, incandescent, tubular, t6

What I would like to do is create a script that will remove each word
and have it become its own record so that I may restructure the asset
into a standardized form:

Bulb, Light, Incandescent, 15W, 120V, T6

Thank you in advance,

On 7 Oct, 09:37, Steve <sbere... at (no spam) gmail.com> wrote:
I hope someone may help me out with this one...
I have a large list of assets. Each asset is in one field and
structured like this:

Light, bulb, 15w, 120v, incandescent, tubular, t6

What I would like to do is create a script that will remove each word
and have it become its own record so that I may restructure the asset
into a standardized form:

Bulb, Light, Incandescent, 15W, 120V, T6

Thank you in advance,
Steve

Hello Steve

I am going to assume that you're:
a) Running FileMaker 10. This script uses features unique to FileMaker
10.
b) Have all the data in FileMaker already, albeit each asset has its
own record with all info for that asset in one field
c) You wish to separate out the asset info into individual fields (not
records as you say above)
d) Working with a copy of the data - not the original data. This is
necessary to prevent data corruption while you are refining your
script.

A quick digression before I get started. If you do not already have
info in FileMaker and it's sitting in a comma delimited text file then
you can use the "Import" function to pull all the info into separate
records and fields.

Now, back to the original scenario. Here's some code off the top of my
head - so please debug it on your system:
*First create some fields. Give them names as follows Characteristic1,
Characteristic2, Characteristic3 etc

show all records
go to record FIRST

//1. Outer loop is to run through all records ie through the whole
list of assets
Loop
SetField [CombinedInformationField; Replace
(CombinedInformationField;",";"¶"] // changes the field where all
info is stored. Sets all commas to be carriage returns "¶" (I hope
that character comes out correctly in the email.) Aim of this is to
set things up so that we can use the wonderful "GetValue()" function
that can pick out each item in turn

//2. Inner Loop is to run through all the characteristics of an
individual asset
Set Variable [$Item,1] // sets a variable called "item" to be
"1" . This will be incremented from 1 to 6 so that we can extract all
the items ie characteristics in the asset
Loop
Set Field By Name ["Characteristic" & $Item; GetValue
(CombinedInformationField;$Item) //This is where it all happens. We're
setting "Characteristic1"..."Characteristic6" fields that you created
at the start. These field names must exactly match what you've set the
field name to be. We're going to set Characteristic1 to be line one,
characteristic2 to be line 2 etc. Remember we've replaced the commas
with carriage returns so that each characteristic sits on a line by
itself.
EXIT LOOP if $Item>6 //that's the number of characteristics that
you have for each asset
END loop

Go to record next, exit after last
End loop

Some further thoughts:
1. If at all possible, go back to the original text file where the
information came from. If you import the text file as a comma
separated file, then it will import it as desired with each item in
it's own field. It will even create the fields for you when you do the
import.
2. Another idea. If all the programming above seems like it's going to
be difficult to get right, try this idea instead. It's a bit fussy,
but less technical. Export all the information out of FileMaker as
"Tab" delimited. Now that you've exported it you can re-import it into
a new file as "comma" delimited. FileMaker should see the commas that
are already there and pop each item into its own field. You may need
to first import the file into Excel first to get it into the right
format ie each item in its own column.

I hope that helps to get you started. Once you've got the script
working you can import the raw data into your development file and run
it for real. After that, change the field names to something more
meaninigful than characteristic1 etc.

Regards
Kevin Smith

Kevin, I appreciate the help with the script. I unfortunatly had to go
with the less tech advanced. Nevertheless, I do appreciate your help.
Thank you,
Steve