Exporting Text field line entries to individual records

[Database Administrator]

FMPro V6.04
Windows XP

Been doing alot of searching on this group, and have not come up with
anything like this.

I have a text field that my people enter in all their software code
that is affected by work on a Software Problem (SP). The SP has it's
own unique control number.
--------------------------------------------
EXAMPLE

SP: 221705
Field Name: Module_Affected
type: Text

Stuff that is entered in the field, followed by a carriage return:
abc.ada
mickey.java
donald.txt
puppy.cc
--------------------------------------------

What I want to do is be able to export this text field in a manner
that I can re-import each individual filename that was entered in the
Software_code field into a child database I've established so that
each filename now is it's own record, with the SP number also being
exported in a manner that each child record will contain it as well so
that my portal will pull in the record correctly based on the SP key.

--------------------------------------------
EXAMPLE of what I need in Child-database after export/import

Record 1
SP: 221705
Module_Affected: abc.ada

Record 2
SP: 221705
Module_Affected: mickey.java

Record 3
SP: 221705
Module_Affected: donald.txt

Record 3
SP: 221705
Module_Affected: puppy.cc

--------------------------------------------

Am I asking the impossible? I just don't see a method to do with the
FMP V6.0, and haven't a clue as to other ways I can search for this
situation on this google group. I wouldn't mind doing this all
manually, except that are thousands of SP's in this database, and some
of them of hundreds of lines of code being affected by work on them.

Any and all help would be appreciated. Thanks!

- Teresa K.

[Database Administrator]

In article
<10405d52-fb1f-4a67-a7e7-fcac940d3fc9@n1g2000prb.googlegroups.com>,
"Teresa K." wrote:

> FMPro V6.04
> Windows XP
>
> Been doing alot of searching on this group, and have not come up with
> anything like this.
>
> I have a text field that my people enter in all their software code
> that is affected by work on a Software Problem (SP). The SP has it's
> own unique control number.
> --------------------------------------------
> EXAMPLE
>
> SP: 221705
> Field Name: Module_Affected
> type: Text
>
> Stuff that is entered in the field, followed by a carriage return:
> abc.ada
> mickey.java
> donald.txt
> puppy.cc
> --------------------------------------------
>
> What I want to do is be able to export this text field in a manner
> that I can re-import each individual filename that was entered in the
> Software_code field into a child database I've established so that
> each filename now is it's own record, with the SP number also being
> exported in a manner that each child record will contain it as well so
> that my portal will pull in the record correctly based on the SP key.
>
> --------------------------------------------
> EXAMPLE of what I need in Child-database after export/import
>
> Record 1
> SP: 221705
> Module_Affected: abc.ada
>
> Record 2
> SP: 221705
> Module_Affected: mickey.java
>
> Record 3
> SP: 221705
> Module_Affected: donald.txt
>
> Record 3
> SP: 221705
> Module_Affected: puppy.cc
>
> --------------------------------------------
>
> Am I asking the impossible? I just don't see a method to do with the
> FMP V6.0, and haven't a clue as to other ways I can search for this
> situation on this google group. I wouldn't mind doing this all
> manually, except that are thousands of SP's in this database, and some
> of them of hundreds of lines of code being affected by work on them.
>
> Any and all help would be appreciated. Thanks!
>
> - Teresa K.

Mac or PC? Really easy with Applescript. On a PC you might do it with a
calculation field that selects text between carriage returns.

Or (haven't tried this, just brainstorming): Export the text field to a
text document, then import that document to the other database. It
should treat the carriage returns as record dividers. To get the control
number to go with it you might need a calculation field that appends
that to each new entry in the text field, separated by a comma.

[Database Administrator]

"Bill Steele" wrote in message
news:ws21-E1F210.15540131102008@newsstand.cit.cornell.edu...
>
> Mac or PC? Really easy with Applescript. On a PC you might do it with a
> calculation field that selects text between carriage returns.
>
> Or (haven't tried this, just brainstorming): Export the text field to a
> text document, then import that document to the other database. It
> should treat the carriage returns as record dividers. To get the control
> number to go with it you might need a calculation field that appends
> that to each new entry in the text field, separated by a comma.

Carriage returns are not exported by FileMaker as returns. FileMaker swaps
them to vertical tabs because returns are used to separate the vidual
records. You could still do this, but it would require an extra step in the
middle to process the exported text file to re-replace the vertical tabs
with returns again before re-importing them.

Better would be not to bother exporting them at all and have a Script that
loops through the Found Set splittng the Text Field and creating new Records
in another Table as it goes.

Even better would be to re-organise the original database's structure to use
a Portal and separate Table instead of the current Text Field. That would
get rid of the need to do this at all. )

Helpful Harry
Horrendously stuck on a pokey Windows box because my ISP has screwed up my
Mac's Internet connection - yet again! (

[Database Administrator]

On Oct 31, 5:32*pm, "Your Name" wrote:
> "Bill Steele" wrote in message
>
> news:ws21-E1F210.15540131102008@newsstand.cit.cornell.edu...
>
>
>
> > Mac or PC? Really easy with Applescript. On a PC you might do it with a
> > calculation field that selects text between carriage returns.
>
> > Or (haven't tried this, just brainstorming): *Export the text field to a
> > text document, then import that document to the other database. It
> > should treat the carriage returns as record dividers. To get the control
> > number to go with it you might need a calculation field that appends
> > that to each new entry in the text *field, separated by a comma.
>
> Carriage returns are not exported by FileMaker as returns. FileMaker swaps
> them to vertical tabs because returns are used to separate the vidual
> records. You could still do this, but it would require an extra step in the
> middle to process the exported text file to re-replace the vertical tabs
> with returns again before re-importing them.
>
> Better would be not to bother exporting them at all and have a Script that
> loops through the Found Set splittng the Text Field and creating new Records
> in another Table as it goes.
>
> Even better would be to re-organise the original database's structure to use
> a Portal and separate Table instead of the current Text Field. That would
> get rid of the need to do this at all. *)
>
> Helpful Harry
> Horrendously stuck on a pokey Windows box because my ISP has screwed up my
> Mac's Internet connection - yet again! *(

Funny you should write about re-organizing the original database,
because that is exactly what I am doing now (and wish this had been in
the original design), however, I still need to get the new child
records populated. Can you possibly expand on how to set up a
looping script to fix this? Thanks so much Harry. You are the
greatest!

- TK

[Database Administrator]

"Teresa K." wrote in message
news:4d06437c-592b-4f65-962f-e5412df8e27c@s9g2000prg.googlegroups.com...

> Funny you should write about re-organizing the original database,
> because that is exactly what I am doing now (and wish this had
> been in the original design), however, I still need to get the new
> child records populated. Can you possibly expand on how to
> set up a looping script to fix this? Thanks so much Harry. You
> are the greatest!

It sounds like you already have the database redesigned so that it has the
second Table to store the new Related Records, so the first thing you need
to do is make sure the Relationship is defined to allow the creation of
Related Records.



Next you will need a Layout from the main Table that has a Portal to the new
Table using this Relationship. It may be a Layout you already have or you
can create a temporary one that can be deleted later.



The carriage returns can be used to work out where each new Related Record's
data ends, but to make sure the last paragraph is also transferred we need
to add an extra carriage return to the end of the data.



Now you can create a Script to transfer the Text Field's paragraphs into
separate Related Records.



To do this the Script needs to loop through all the Records, and within each
Record it needs to loop through the TextField's paragraphs transferring each
paragraph into a new Record in the Related Table via the Portal ... using
the Portal means that FileMaker will itself handle the necessary ID / key
field data for the Relationship to work and you only need to transfer the
"real" data.



Trying to extract paragraphs from the middle of data can bee complicated, so
to make things simpler this Script actually deletes each paragraph as it
goes, BUT it only deletes from a copy of the data put into a Global Field
(g_GLOBALTextField), not from the original Text Field, so all your data will
be left intact.



Another temporary Global Field (g_TempField) can be used to store each
paragraph's text to be transferred to the new Related Record.



The basic idea for the script is (apologies for the formatting - I have no
idea where the line-wrapping length is for this application):



- Go to the Layout with the Portal, Find ALL

the Records and start with the first one



- Loop through the Records



- Copy the content of the Text Field to

a temporary Global Text Field and add

the extra return to the end and then

loop through the Field's paragraphs



- Finish the Field loop if the Global

Text Field is empty or just a

return (this also means the loop

won't be performed if the Record

has no data in Text Field to

transfer)



- Copy the top-most paragraph to the

temporary Global Field ready for

transferring and then removing



- Go to the last Portal Row (the

empty one for creating new Related

Records) and put the temporary

Global Field's data into a newly

created Related Record



- Delete the now-transferred paragraph

from the Global Text Field so that

the next paragraph is at the top.

The easiest way is to use the

Substitute function to change the

temporary Global Field (plus a

return) into a blank / nothing ""



- Loop back around for the Global Text

Field's next paragraph, which is now

at the top (the loop will exit at

the start when the Global Text Field

is empty, ie. all paragraphs have

been processed and removed)



- Go to the next Record and repeat the

Text Field loop again, until all

Records have been processed





Translating this into Script commands gives something like:



Go To Layout [PortalLayout]

Show All

Go To Record [First]

Loop

Set Field [g_GLOBALTextField; TextField & "{ret}"]

Loop

Exit Loop If [(IsEmpty(g_GLOBALTextField)) or

(g_GLOBALTextField = "{ret}")]

Set Field [g_TempField; Left(g_GLOBALTextField;

Position(g_GLOBALTextField; "{ret}";

1; 1) - 1)]

Go To Portal Row [Last]

Set Field [ChildTable:ataField; g_TempField]

Set Field [g_GLOBALTextField; Substitute(

g_GLOBALTextField; g_TempField &

"{ret}"; "")]

End Loop

Go To Record [Next; Exit After Last]

End Loop



where {ret} is really the "backwards P" paragraph / carriage return symbol
on one of the buttons in the Define Calculation window.



This seems to work in a quick test run, but as always MAKE A BACKUP FIRST!
)



If the Text Field contains any blank rows, then you will get blank Related
Records, but these can all be manually deleted in one go from the Related
Table itself by simply performing a Find using the = symbol in the DataField
(making sure they are all empty!) and then deleting the Found Set.





Helpful Harry

Still stuck on a s-l-o-w old Windows PC (

[Database Administrator]

On Nov 4, 11:28*pm, "Your Name" wrote:
> "Teresa K." wrote in message
>
> news:4d06437c-592b-4f65-962f-e5412df8e27c@s9g2000prg.googlegroups.com...
>
> > Funny you should write about re-organizing the original database,
> > because that is exactly what I am doing now (and wish this had
> > been in the original design), however, I still need to get the new
> > child records populated. * Can you possibly expand on how to
> > set up a looping script to fix this? *Thanks so much Harry. *You
> > are the greatest!
>
> It sounds like you already have the database redesigned so that it has the
> second Table to store the new Related Records, so the first thing you need
> to do is make sure the Relationship is defined to allow the creation of
> Related Records.
>
> Next you will need a Layout from the main Table that has a Portal to the new
> Table using this Relationship. It may be a Layout you already have or you
> can create a temporary one that can be deleted later.
>
> The carriage returns can be used to work out where each new Related Record's
> data ends, but to make sure the last paragraph is also transferred we need
> to add an extra carriage return to the end of the data.
>
> Now you can create a Script to transfer the Text Field's paragraphs into
> separate Related Records.
>
> To do this the Script needs to loop through all the Records, and within each
> Record it needs to loop through the TextField's paragraphs transferring each
> paragraph into a new Record in the Related Table via the Portal ... using
> the Portal means that FileMaker will itself handle the necessary ID / key
> field data for the Relationship to work and you only need to transfer the
> "real" data.
>
> Trying to extract paragraphs from the middle of data can bee complicated,so
> to make things simpler this Script actually deletes each paragraph as it
> goes, BUT it only deletes from a copy of the data put into a Global Field
> (g_GLOBALTextField), not from the original Text Field, so all your data will
> be left intact.
>
> Another temporary Global Field (g_TempField) can be used to store each
> paragraph's text to be transferred to the new Related Record.
>
> The basic idea for the script is (apologies for the formatting - I have no
> idea where the line-wrapping length is for this application):
>
> * * - Go to the Layout with the Portal, Find ALL
>
> * * * the Records and start with the first one
>
> * * - Loop through the Records
>
> * * * *- Copy the content of the Text Field to
>
> * * * * *a temporary Global Text Field and add
>
> * * * * *the extra return to the end and then
>
> * * * * *loop through the Field's paragraphs
>
> * * * * * - Finish the Field loop if the Global
>
> * * * * * * Text Field is empty or just a
>
> * * * * * * return (this also means the loop
>
> * * * * * * won't be performed if the Record
>
> * * * * * * has no data in Text Field to
>
> * * * * * * transfer)
>
> * * * * * - Copy the top-most paragraph to the
>
> * * * * * * temporary Global Field ready for
>
> * * * * * * transferring and then removing
>
> * * * * * - Go to the last Portal Row (the
>
> * * * * * * empty one for creating new Related
>
> * * * * * * Records) and put the temporary
>
> * * * * * * Global Field's data into a newly
>
> * * * * * * created Related Record
>
> * * * * * - Delete the now-transferred paragraph
>
> * * * * * * from the Global Text Field so that
>
> * * * * * * the next paragraph is at the top.
>
> * * * * * * The easiest way is to use the
>
> * * * * * * Substitute function to change the
>
> * * * * * * temporary Global Field (plus a
>
> * * * * * * return) into a blank / nothing ""
>
> * * * * * - Loop back around for the Global Text
>
> * * * * * * Field's next paragraph, which is now
>
> * * * * * * at the top (the loop will exit at
>
> * * * * * * the start when the Global Text Field
>
> * * * * * * is empty, ie. all paragraphs have
>
> * * * * * * been processed and removed)
>
> * * * *- Go to the next Record and repeat the
>
> * * * * *Text Field loop again, until all
>
> * * * * *Records have been processed
>
> Translating this into Script commands gives something like:
>
> * * Go To Layout [PortalLayout]
>
> * * Show All
>
> * * Go To Record [First]
>
> * * Loop
>
> * * * *Set Field [g_GLOBALTextField; TextField & "{ret}"]
>
> * * * *Loop
>
> * * * * * Exit Loop If [(IsEmpty(g_GLOBALTextField)) or
>
> * * * * * * * * * * * * (g_GLOBALTextField = "{ret}")]
>
> * * * * * Set Field [g_TempField; Left(g_GLOBALTextField;
>
> * * * * * * * * * * * Position(g_GLOBALTextField; "{ret}";
>
> * * * * * * * * * * * 1; 1) - 1)]
>
> * * * * * Go To Portal Row [Last]
>
> * * * * * Set Field [ChildTable:ataField; g_TempField]
>
> * * * * * Set Field [g_GLOBALTextField; Substitute(
>
> * * * * * * * * * * * g_GLOBALTextField; g_TempField &
>
> * * * * * * * * * * * "{ret}"; "")]
>
> * * * *End Loop
>
> * * * *Go To Record [Next; Exit After Last]
>
> * * End Loop
>
> where {ret} is really the "backwards P" paragraph / carriage return symbol
> on one of the buttons in the Define Calculation window.
>
> This seems to work in a quick test run, but as always MAKE A BACKUP FIRST!
> )
>
> If the Text Field contains any blank rows, then you will get blank Related
> Records, but these can all be manually deleted in one go from the Related
> Table itself by simply performing a Find using the = symbol in the DataField
> (making sure they are all empty!) and then deleting the Found Set.
>
> Helpful Harry
>
> Still stuck on a s-l-o-w old Windows PC *(

THANK YOU THANK YOU THANK YOU THANK YOU!!!! Going to work on this
now! And thanks for the explanations! It helps so much to also know
why I need to do things so I can use this knowledge for future
fixes. THANK YOU!!!!

- Teresa K.