Repeating fields as Keys

[jrp]

*Repeating fields as Keys*

I

[Vaughan]

Stay well away from repeating fields, they won't help you here. You want a multi-key field which is a normal field with multiple entries separated by paragraph marks (returns).

A field formatted as checkboxes is ideal for this.

[Lee Smith]

I agree with Vaughan, I usually do

But to add a little to his advice, Repeating fields are holdovers from earlier versions of FileMake (before it became relational). They do make a good place to store your interface graphics, but little else. They're there mainly for the backwards compatibility with earlier the earlier versions of FM.

Lee

[jrp]

I do understand that about repeating fields - which is why I was uneasy. I'm still unclear about the other options, though. I'm about to go searching through my books and this forum for more discussion of multi-key fields, but if you have time, and could explain it a bit? I would be most appreciative.

Thanks.

[jrp]

OK - found the info I need, and some good theoretical discussion on the values of multi-keys and join files - good stuff! I think I'm going to go ahead and create a join file for the example I listed, since I need to store a bit more information about every link. But there's another instance of this in my solution where the multi-key relationship will work perfectly. It's certainly a nice tool to have in my belt.

Thanks again!

[Vaughan]

Only use a join file if you have a many-to-many relationship, not because you need to "store a bit more information about every link." If you need to store more information, add more fields!

[jrp]

Yes - the many-to-many is staff to projects, and the "bit more information" is the task that each staff member is doing on the project - a field that doesn't belong in the project file, or the staff file, neh?

[Vaughan]

You need to analyse the data structure.

It appears you have three entities: staff, projects, tasks.

Work out the relationships between all three -- whether they are one-to-one, one-to-many or many-to-many. Only relationships that are many-to-many require a join file.

It depends on how you structure your work flow. Is the project assigned to staff and only those staff work on tasks, or are tasks assigned to any available staff?

If tasks are assigned to available staff then the structure might be:

projects and tasks: o2m, since a task belongs to only one project

tasks and staff: m2m, since a staff member can work on more than one task and a task can have more than one staff member

so you need Projects >> Tasks >> Join >> Staff

If the project is assigned to staff then you also need a relationship between projects and staff. If a project can be assigned to more then one staff then you need a join between projects and staff as well.

This is but one way of doing it. You need to analyse your situation very carefully and decide which structure fits your needs.