Two fields as ONE primary key

[awayand]

I'd like to find a way to have two defined fields act as one primary key. In other words, the two fields together should be unique, not when they are isolated. Background:

I have an Employee file, that has an Employee ID in it.

I have a TimeCard file, that has an Employee ID (foreign key to Employee file) and a date field in it.

I want to collect all the times in the timecard file. However, I want to avoid having Employees make duplicate date entries...

Any ideas?

[danjacoby]

The standard answer is what is known as a "self-relationship".

In the TimeCard file, create a calc field that combines the EmployeeID and the date. Now create a relationship, related that calc field to itself. Create a portal with the related calc field in it, and you'll be able to see if there are any duplicates.

It doesn't prohibit duplicates from being made, nor does it automatically delete them. But it does alert the user when a duplicate has been made.

Yeah, we'd all like to be able to verify combinations of fields, but FMP hasn't created that ability yet, so we have these delightful workarounds.

[awayand]

Is there any other solution? Through a plugin maybe? I can't believe I'm the only one with this problem...

[Kurt Knippel]

You can use field-level validation to restrict this, however I find that to be way to difficult to control and ends up with only a confused user as Filemaker alerts them with a dialog that validation failed (granted this is a specific enough case, where they could be informed that the date has already been entered).

The better way is through scripted validation, or even calculated validation messages. Either solutions puts the developer in control of what happens and allows you to give the user methods to solve the problem.

[CobaltSky]

Hi awayand,

There's a well known technique for doing this that has been around for a good while. A variation of it ended up being published in the FileMaker knowledge base about five years ago.

The title of the kbase article is:

"Duplicate Data Entry Validation Using Multiple Fields"

It is article no 103792, and is accessible online at the following URL:

http://www.filemaker.com/ti/103792.html

The example used in the kbase write up is validating for a unique firstname and lastname combination, but it can be used just as effectively to validate for uniqueness on name and date or any other combination of two or more fields.