Jump to content

Using multiple fields as a key


This topic is 5691 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

  • Newbies

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).

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
If I could read...
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5691 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.