Re: Exporting Text field line entries to individual records
- From: "Your Name" <your.name@xxxxxxx>
- Date: Wed, 5 Nov 2008 18:28:29 +1300
"Teresa K." <teresa.kabourek@xxxxxxx> wrote in message
news:4d06437c-592b-4f65-962f-e5412df8e27c@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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::DataField; 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!
:o)
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 :o(
.
- Follow-Ups:
- Re: Exporting Text field line entries to individual records
- From: Teresa K.
- Re: Exporting Text field line entries to individual records
- References:
- Re: Exporting Text field line entries to individual records
- From: Your Name
- Re: Exporting Text field line entries to individual records
- From: Teresa K.
- Re: Exporting Text field line entries to individual records
- Prev by Date: Re: Background noise
- Next by Date: Re: Background noise
- Previous by thread: Re: Exporting Text field line entries to individual records
- Next by thread: Re: Exporting Text field line entries to individual records
- Index(es):
Relevant Pages
|
