FILEMAKER PRO 7.

Hello again,

I've searched high and low, but have been unable to find an answer,
apologies if I'm missing something very obvious...

Basically, I have a long-established table which now makes sense to
'normalize'. I'd like to replace all of the fields in the existing
table with a corresponding ID from the new table. Is there a simple
in-built way to do this? Or should I create the new table and run some
sort of 'replace' function?

Thanks,

Kirsty

In article <1163431830.377319.23420 (AT) h48g2000cwc (DOT) googlegroups.com>,
"kirstychestnutt (AT) googlemail (DOT) com" <kirstychestnutt (AT) googlemail (DOT) com>
wrote:

Your description is not entirely clear, but I think what you want to do
is assign serial numbers to the records in your existing table. That is
very easy to do.

Define a new field in your table. Give it a suitable name. If it is
going to be the primary key field in relationships (which is the basic
reason for defining a serial number field), call it something like
kp[entity]ID, where [entity] is the name of whatever kind of entity the
table stores data about.

Define the field to be a number field, with the contents to be filled
automatically by serial number, starting number 1, increment 1.

Now go to a layout of your table, and put that field on the layout.

In the Browse mode, Show All Records.

Click in the serial number field. Do the menu command
Records >> Replace Field Contents

In the dialogue box that appears, click on Replace with serial numbers,
initial value 1, increment 1, and check the box Update serial number
Entry Options.

Then click Replace.

That will put serial numbers in all the records, in the order they
appear. It will also change the next value in the definition of the
serial number field to one more than the highest serial number assigned
by the replace Field Contents action.

Now go back to the field definition of the serial number field and click
the box Prohibit modification of value during data entry. That will
prevent anyone from changing the serial number value, and so will
protect any relationships based on that value.

If simply assigning serial number, as described above, was not what you
were trying to do, then ask your question again, stated more clearly.

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

Bill wrote:
No, sorry, to make it clearer:

I have one table with shedloads of repeating values (it's an inventory
database and against each piece of inventory there's a customer name ->
many pieces of inventory per customer).

I'd like to extract the repeating customer field into a separate
related table, replacing the customer name with a reference to the
customer table.

Kirsty

In article <1163498331.214088.202170 (AT) m73g2000cwd (DOT) googlegroups.com>,
"kirstychestnutt (AT) googlemail (DOT) com" <kirstychestnutt (AT) googlemail (DOT) com>
wrote:

I've been faced with the same problem. The first thing, obviously,
is to create the required table(s) of customer, items, etc. Each
one should have some kind of unique id field included (serial number
etc.)

I think there are various ways to approach the subsequent step of
converting the existing records. Perhaps the cleanest way would
be to create the new tables in a new database, then import the old
database into a table in the new one, and use the autoenter feature
to match customer names and set the id field accordingly. You would
probably need to import at least one of the existing customer fields
to achieve this, but you could always redefine this later to show
the same info via the relationship based on the identifier.

You could do all this in the existing database, too, but I prefer
to have a known, stable backup handy if I'm playing with the
structure of an existing database!

I also want to add that.

Although repeating fields have their merrits, storing huge amounts of
information is not one of them. Better to start with a clean file and import
those repetitions as seperate records.

Ursus

"David Stone" <no.email (AT) domain (DOT) invalid> schreef in bericht
news:no.email-7EDB5A.07481014112006 (AT) news1 (DOT) chem.utoronto.ca...

In article <no.email-7EDB5A.07481014112006 (AT) news1 (DOT) chem.utoronto.ca>,
David Stone <no.email (AT) domain (DOT) invalid> wrote:

I now understand better what you are trying to accomplish.

David gave good advice.

You can do thisin a logical way, though it is a little complicated. I
can't give you a detailed description without knowing the actual
structure of your current database, but I can lay out the steps required
in general terms.

I think your new database structure should have the following tables:

Customer
Order
Line Item
Inventory

Customer and Inventory are "primary" or "parent" tables, that contain
data that does not depend on any other tables. Order and Inventory are
"child" tables, that depend on other tables.

Customer would contain customer info: Name, address, etc

Inventory would contain a record for each item of inventory: Name,
description, cost, price, supplier ID, etc

Order is related to Customer (one to many), and contains info about the
order: date, total price, payment terms, etc

Line item is a join table than joins Order to Inventory (many to many),
and that contains info peculiar to the particular order: Quantity,
extended price, etc.

All four tables would of course have serial number primary key fields.

The related tables would also have number fields for the related record
foreign key IDs:

Order would have kfCustomerID

Line item would have kfInventoryID and kfOrderID

Relationships:

Customer::kpCustomerID = Order:: kfCustomerID

Order::kpOrderID = LineItem::kfOrderID
Inventory::kpInventoryID = LineItem::kfInventoryID

Set up the relationships to allow creation of Order from Customer, and
creation of LineItem from Order.

Import records from the existing table into these four tables, bringing
the appropriate fields into each of the four new tables, and splitting
repeating fields into separate records. The splitting of repeating
fields is an option you check on the Import dialogue.

The kp serial numbers for each table will be generated when you do the
import, so the corresponding relationships with the kf fields in related
tables will not be automatically established. You need to go through
some more gyrations to make that happen. This gyration has to start with
a known, existing connection of the various related records, and that
existing relations has to be unique and unambiguous. Customer name might
be one such existing identifier, and Item name might be another. Or, you
might want to have a new repeating field that is a unique identifier for
each of the repeating items. You will need to set up temporary fields in
the several tables to receive these initial unique and unambiguous
identifiers, so that temporary relationships can be set up on the basis
of these temporary identifiers.

You will then need to eliminate duplicates from some of the tables: I am
guessing you will need to eliminate duplicates from the Customer and
Inventory tables, and maybe from the Order table.

You may actually be able to eliminate the duplicates when you first do
the import, by requiring a field validation that each of the "temporary"
identifiers be unique. Whether you can actually do this automatic
elimination of duplicates without loss of vital information depends on
the actual structure of your current data.

Then you use the temporary relationships to to a Replace Field Contents
on the respective kf ID fields, to fill those fields with the
appropriate kp numbers from the related tables. After you have that done
correctly, you change over to the permanent relationships based on the
kp = kf identities, and get rid of the temporary identifier fields that
you used in the import.

You should develop scripts to automate these import and manipulation
processes, run the scripts on a trial basis, and make corrections as
needed to get the process to run right. Chances are it will not run
right the first time, because of logic errors in setting up the process.
This is why you should script the process, run it and correct the logic
errors that you discover, and keep doing that until it runs right
without further correction. Then, after that, do the final import and
change over to using the new database.

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

In article <4559cf6f$0$18081$dbd41001 (AT) news (DOT) wanadoo.nl>,
"Ursus" <ursus.kirk (AT) wanadoo (DOT) nl> wrote:
Repeating values does not necessarily mean repeating fields! In the
context of database normalization, 'repeating values' simply means the
same thing is entered into the same field in multiple different records.
This isn't the same as having a FileMaker "repeating field", where
you can have multiple entries within a field in the same record.

In case anyone is interested, I basically did it the long way, which
didn't turn out to be too bad at all:

1. Created value list of items in the Customer field of the existing
Inventory table
2. Using ValueListItems, obtained list of these Customers
3. Created new table (Customers) with Customer_Name and Customer_ID
4. Created Customer_ID field in the Inventory table
5. Wrote a script that searched Inventory table for customer field and
copied over the Customer_ID
6. Created relationship between Customer and Inventory field
7. Deleted original Customer field in Inventory database

Thanks for the ideas!