Use of reference table

[Greg Tuite]

I am reworking a database in FM12 that was originally designed in FM3. I'm going to start from scratch. Part of what I plan to change is to use a table as a reference to add data to two fields on my main table through value lists.

Main table = Matters

1 Matter has 1 insurance company assigned to it

1 Matter has 1 adjuster assigned to it

(The adjuster works for the insurance company at one of it's field offices)

1 insurance company has many field offices

1 field office has many adjusters working there, but the adjuster only works at one field office

The fields on the Matter I want to enter info are:

Insurance Company, address, phone fax

Adjuster name, direct phone, e-mail

I used to have a table with a relationship between matter and adjuster. I had a separate table with insurance companies and had a relationship between adjusters and insurance companies. Therefore when I entered the adjuster the insurance company info world come with it. The problem I had was twofold. Adjusters change companies a lot and often go to work for competitors so its hard to keep the data properly updated. Their former files may be transferred to a number of different adjuster. Insurance companies don't change much (unless they go broke or are bought up) so I want to make that the but they have a lot of different claims offices. I'm trying to think of the best structure for this in a FileMaker and value list format. Thanks.

[comment]

The problem I had was twofold. Adjusters change companies a lot and often go to work for competitors so its hard to keep the data properly updated.

Should it be updated? Or should the company remain unchanged when the adjuster defects? If the former, use a "live" relationship to fetch the data; if the latter, use a lookup

[Greg Tuite]

The company and field office stays the same so will use a relationship. I had been thinking too long about how to structure the tables, but now it seems clear to have one for matters, one for insurance companies and one for adjusters.

[comment]

And one for field offices, of course.