Using multiple fields as a key

[Leo the 3rd]

Coming from the Access DB world and in Access you had the ability to define 3 fields in a table as a unique key. How do I do the same thing in FM8 Advanced? Sorry if this question was posted before - I searched but didn't find anything similar.

[Ender]

See if this recent discussion helps:

http://fmforums.com/forum/showtopic.php?tid/176154/

I've done it both ways.

[Leo the 3rd]

Ender: Read this post several times and I'm not quite getting it. Should I be using a Join table to accomplish what I want to do?

[Ender]

The usual case for having mutliple key fields that determine uniqueness IS a join table. If this isn't your situation, then perhaps you can explain it.

In any case, the arguments in the other thread should still be valid. You don't really need a separate primary key if you can use the combination of your three fields together to determine uniqueness, but it may be easier to use a separate auto-enter serial number.

You seem to be asking how to use the combination of three separate fields to determine uniqueness. But uniqueness for what purpose? If it's for a relationship, it shouldn't be necessary (depending on what the relationship is, you should be able to guarantee uniqueness, or if the three fields are to be the key for a new relationship, then all three can be used in the relationship.)

[comment]

Perhaps you can clarify your question. At least to me, not being familiar with Access, the phrase "define 3 fields in a table as a unique key" is meaningless. In Filemaker, 3 fields can be defined as a key, and/or 3 fields can be validated to be unique - these two have little in common.

EDIT: I see I have been assigned to summarize Ender...

Edited May 6, 2006 by Guest

[Leo the 3rd]

Ok, let me see if I can better explain my problem:

I have tables: Customer, ApptDates, Services, Products. Each table has an ID of CustID, AppDateID, ServicesID and ProductID. CustID is a unique serial number auto-assigned to each customer entry. The Customer can have multiple ApptDates assigned as well as multiple Services and Products for each service. What I'm trying to do is have the AppDateID equal to the CustID +Date + Time (Date and Time are assigned in the ApptDates table for each appointment scheduling).

[comment]

What I'm trying to do is have the AppDateID equal to the CustID +Date + Time (Date and Time are assigned in the ApptDates table for each appointment scheduling).

For what purpose? What are you actually trying to accomplish here?

[Leo the 3rd]

I want a table that allows for multiple appointments, but doesn't have duplicate appointments. Example. The customer can have an appointment at 12pm and 4pm but never a duplicate appointment at either of those times in the same given date. I want the Appointment date to be the unique key.

[comment]

Now I see (hopefully). There are two methods to accomplish this:

1. In the Appointments table, define a text field, set it to auto-enter a calculated value =

CustID & "|" & GetAsNumber ( AppointmentDate ) & "|" & AppointmentTime

Uncheck the "Do not replace existing value.." option. Set the field's validation to 'Unique value'.

2. Define a relationship from the Appointments table to another occurence of the same table (self-join), let's call it Duplicate:

Appointments::CustID = Duplicate::CustID

AND

Appointments::AppointmentDate = Duplicate::AppointmentDate

AND

Appointments::AppointmentTime = Duplicate::AppointmentTime

Validate all three fields by calculation =

IsEmpty ( Duplicate::AppointmentID )

AppointmentID is an auto-entered serial number field.

Note that both methods will trigger on record committment only.

[T-Square]

Comment--

I appreciate ideas on how to accomplish the unique multifield key; I started in Access long ago and have often wondered how to do this. (It's slightly frustrating to me that the developer has to do this themselves; it's just so easy in Access to set this up... But that's just one of the ways that FM differs from Access!)

BTW, another place this sort of cross-field uniqueness comes in handy is when you are building lists, where you have some group entry (say "Fall Outreach Mailing"), you want to be able to add people to the list, and you don't want them to appear more than once in the list.

Cheers,

David

Edited May 6, 2006 by Guest
If I could read...

[Ender]

If you use a relationship filtered by all of the keys to create the related record, then no validation is necessary. It would simply overwrite the existing related record. Or if you didn't want to overwrite the existing record, then a test for the related record can be used (as in method 2 above), and the record creation skipped. I prefer this technique of creating the unique related record in the first place over a process that requires a "Unique" validation after the record is already there.

[comment]

Another option is to present to the user only unused choices (e.g. a 'dwindling' value list, or a filtered list view). I believe it makes for a much nicer user experience than validation kicking in.

[T-Square]

Hmm... dwindling lists does sound good. But aren't there refresh issues with this sort of value list?

[comment]

Yes, but can be solved by a scripted Refresh + Go to Field.