I know that filemaker can handle fields with many tables.

I want to split a table into two for ease of design and link them with a 1:1 relationship. I am having a lot of trouble doing this.

eg:

table 1

personid

name

table 2 (confidential info)

personid

sex

ssn

salary

vacation accrued

table 3 (disciplinary actions)

actionid

ssn

reason

comment

penalty

I want to link table 1 and table 2 with a 1:1 relationship via person id.

then I want to link table 2 and table 3 in a 1:many by ssn. I should be able to show the person's name on disciplinary report via these relationships. Any help appreciated.

What problem? Here is your tables with relations.

Persons.zip

thank you. I tried these. if I create a new record in table 1 and fill in personid it does not create a new record automatically in table 2. Also what if you have 100 records in table 1 and now want to add more fields in table 2. How do you get it to make table2:personid records 1 to 100.

if I create a new record in table 1 and fill in personid it does not create a new record automatically in table 2.

If you are using the relationship to create records then you DON'T set the child ID. You set (enter data into) any field in the child table EXCEPT the key field. If Allow Creation of Related is selected, just start typing in a child field. When you do, FM will automatically INSERT the ID for you.

Also what if you have 100 records in table 1 and now want to add more fields in table 2. How do you get it to make table2:personid records 1 to 100.

Same theory applies. Just choose a field in table 2 which you can set with something. Go to your first parent record and loop through them, something like:

Go to Record/Request/Page [ First ]

View As [ Form ]

Freeze Window

Loop

Set Field [ Table2::CreateDate ; Get(CurrentDate) ]

Go to Record/Request/Page [ Exit after Last ; Next ]

End Loop

View As [ Table ]

... this will create one related record for each Parent, automatically starting a new Confidential record and filling in the ID from parent ...

But you don't NEED a new Confidential record until time to put a SSN, Sex etc in, right? When you're ready to enter any information, above theory applies - just type your data and the record is created. Sometimes there are requirements to have a record ahead of time because it's more difficult to identify (or count) something that ISN'T there (less so in >=7). Fulfilling compliance regulations sometimes dictates pre-creation and tracking of data. Usually it's only created when needed.

LaRetta

Edited November 9, 200520 yr by Guest

I want to split a table into two for ease of design and link them with a 1:1 relationship.

"Ease of design" would dictate keeping an entity's data in one table.

The only reason I've found to have a separate file related via a one-to-one relationship is to simplify maintenance. In general, one-to-one relationships should be eliminated, and the tables combined.