I have some "flat" (single table) databases that contain Subject term
fields formatted as checkbox arrays where one or more keywords can be
ticked off according to content.

Now I realize that I could make much better use of this information by
creating a Subject Term *Table* in FMP8 -- I could print out nice
indexes of subject matter, for instance.

So, I'm trying to figure out how to import the flat field data from the
original database into a new multi-table database so that each "checked
off" value in the Subject Term field becomes a single record in the
Subject Table, right?

I feel stumped -- I seem to recall when importing other files seeing an
option for splitting "Repeating Fields" into separate records - but what
about splitting multiple values in a checkbox field into separate
records?

Thanks for any pointers!

Albert

In article <asteg-317494.10444225112006 (AT) news (DOT) west.earthlink.net>,
Albert <asteg (AT) mindspring (DOT) com> wrote:

You can't split them on importing, that option is only for Repeating
fields, and only because Repeating fields were basically the poor man's
related records before FileMaker went properly relational.

You'll have to use a script that loops through each record creating all
the appropriate related records that are needed. The easiest way to
create these related records is to use a Portal that allows creation of
related records, then the script is fairly simple. Something along the
lines of (as long as there's only one Portal):

Show All Records
Go To Record [First]
Loop
If PatternCount(CheckboxField, "Option1") > 0
Go To Portal Row [Last]
Set Field [Relationship::OptionField, "Option1"]
End If
If PatternCount(CheckboxField, "Option2") > 0
Go To Portal Row [Last]
Set Field [Relationship::OptionField, "Option2"]
End If
...
If PatternCount(CheckboxField, "OptionX") > 0
Go To Portal Row [Last]
Set Field [Relationship::OptionField, "OptionX"]
End If
Go To Record [Next, Exit After Last]
End Loop



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

In article <asteg-317494.10444225112006 (AT) news (DOT) west.earthlink.net>,
Albert <asteg (AT) mindspring (DOT) com> wrote:

First, the actual content of the field you have formatted as "checkbox"
is the word associated with each checkbox. If you put a new occurrence
of that field in a layout, and leave it formatted as an Edit box, you
will see the actual values.

You defined a value list for the Subject Terms, and the checkboxes
simply choose values from that list to go into the field.

The Term table should have at least two fields: A text field to hold the
term itself (which I will call TermName), and a serial number field to
hold the primary key field that will ultimately be used to relate the
Term table to the "old" table (which you have not given us a name
for,and will simply call "Old_Table"). I will call this serial number
field kpTermID.

You will also need to define a number field in Old_Table to hold a value
of TermID, to make the ultimate relationship between the two. Call this
number field kfTermID.

I see three ways to create the Term table.

Method one:

SImple and direct: Create a new table for Term, and simply create a new
record for each term, typing the term into the TermName field for the
new table. This is easy if you have relatively few different Terms.

Method two:

Copy the already defined value list of Subject Terms to a text document.
You can do this by simply selecting the content of the value list,
copying it, and pasting it into a text document. Then import the Text
document into a new table. This will create one record for each of the
Terms.

Method Three:

Define a new Term table, and require in the field definition that the
values in the TermName field be unique. Then import records from the
"old" table into the new table, importing only the Term field.

Any of these three methods will produce a new table of Subject Terms,
with one term per record, all being unique.


Relationships:

After you have that done, then set up the relationship. The first
relationship should be

Old_Table::SubjectTerm = Term::TermName

You can stop here, and set up your value lists and layouts based on this
relationship. However, that leaves a relationship based on a field whose
content is editable. This is not ideal, for various reasons. To go the
extra step of getting a relationship based on an uneditable field,
proceed as follows:

In the Old_Table table, do Show All Records, then select the field
Old_Table::kfTermID and do Replace Field contents, by calculation, as
follows:

Old_Table::kfTermID = Term::kpTermID

This populates the kfTermID field in the Old_Table records with the
corresponding value of kpTermID from the related Term records.

After this define a new relationship between the two tables based on
TermID:

Old_Table::kfTermID = Term::kpTermID

and get rid of the other relationship based on TermName.

Now you have a properly defined relationship between the two tables,
based on the uneditable key field kpTermID. It is a one-to-many
relationship, one Term record to many Old_Table records.

You can now get rid of the field SubjectTerm from Old_Table, and put the
field Old_Table::kfTermID and the related field Term::TermName on its
place.

You can now change the value list of SubjectTerm to be dynamic, based on
the field value of Term::kpTermID, showing the second field
Term::TermName, and showing only the second field. format the field
Old_Table::kfTermID in your main layout to show the drop-down list
SubjectTerm. Then you pick the value from the list, and so can create
new records in Old_Table and assign proper SubjectTerm values to them.

--
For email, change <fake> to <earthlink>
Bill Collins

You have FileMaker 8, right? Harry's script is overkill (but probably the
only way to do it back in FileMaker 6); and I don't think "Method Three"
described by Bill C will actually work.

Check boxes put multiple values into a single field, with a carriage return
between them. Your initial thought was to "split the repeating fields" which
was correct except there wasn't a repeating field to split (which is why
FileMaker didn't offer you the option).

So, for the purposes of migration you need a repeating field. The following
calculation will give you one:

GetValue(Extend(X); Get(CalculationRepetitionNumber))

Where "X" is your check box "Terms" field. Just set up your calculation to
have a Text result and however many repetitions are required to hold the
maximum number of key word terms. The calculation will "break out" the
values one per repetition, essentially transforming field X into a repeating
field. Then you can import and split the values like you wanted to in the
beginning.

Good luck!

"Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com> wrote

"Bill" <bbcollins (AT) fake (DOT) net> wrote

In article <J4SdnUjABdNxM_TYnZ2dnUVZ_h6dnZ2d (AT) comcast (DOT) com>,
"Bill Marriott" <wjm (AT) wjm (DOT) org> wrote:

Wow -- thanks to all three of you for responding in such depth. Apart
from actually getting this task accomplished, this is a fascinating
tutorial on various Filemaker strategies and tactics!

So, I create a new, repeating text field in my original file and define
it with the calculation you specify there. Is
"CalculationRepetitionNumber" the literal text I put in there, or is
that a number I enter according to how many repetitions I determine that
I need (the max number of checkboxes checked in any record)? So, for
instance it might look like:

GetValue(Extend(SubjectKeywords); Get(10))

Is that right? I'm just unclear on what that
"CalculationRepetitionNumber" means, exactly.

Thanks again!

albert

Nevermind that last question -- I tried it out and everything works
great!

Woo--hoo!

albert

In article <J4SdnUjABdNxM_TYnZ2dnUVZ_h6dnZ2d (AT) comcast (DOT) com>,
"Bill Marriott" <wjm (AT) wjm (DOT) org> wrote:

As usual, Bill Marriott has given excellent advice.

--
For email, change <fake> to <earthlink>
Bill Collins