A basic question about relationships and portals

[Steven Cappiello]

I've read the manuals and articles I could find on the web. I understand relational design I think. I understand the different types of relationships and I understand the need for primary keys and foreign keys.

But.... here's what I don't get..

When I'm listing information in a portal from a related database for a user to pick from, the "key" field is usually a meaningless number to the user..

If I'm assiging students to classes in the class assignments table via a portal, I don't want my user to be bothered with knowing the class ID.

My question is, is there a way I can avoid showing the ID at all ? and make it so that the user just sees the information that makes sense to them (i.e. Algebra 3)

I'm thinking that you NEED to have the serial # in there to make sure you're referring to the correct Algebra 3 --- so I was thinking maybe I'd create a concatenated field ( Class_Name_and_ID) which would certainly be unique.. but I mentioned this to someone and they grimaced.. because they said I shouldn't match to a calculated field... Or perhaps I could use the value list of IDs and Class names and sort by Class (but then I wouldn't be able to type-to the Class name in the value list)

could someone recommend the be

Am I making sense to anyone : because I'm certainly confusing myself!

Could someone elighten me ? Note: I'm using Filemaker 7 and some of my tables will be a lot bigger than a few classes, so I'd love to understand the best way to do this.

many thanks.. I hope this made sense.

[dbruggmann]

>>My question is, is there a way I can avoid showing the ID at all ? and make it so that the user just sees the information that makes sense to them (i.e. Algebra 3)

Yes, just place only the fields onto the portal row, that you want your users to see. Filemaker will do the correct relating

[Steven Cappiello]

Great thanks.. so.. in a situation like this:

STUDENTS TABLE

kp_Student_ID

Student_Name

CLASSES TABLE

kp_Class_ID

Class_Name

ENROLLMENTS TABLE

kf_Class_ID

kf_Student_ID

I'm guessing I would put the portal on the Enrollments Table Occurrence

and the portal would have only the following fields in it ?

STUDENTS TABLE::Student_Name

CLASSES TABLE::Classes_Name

then I'd set the relationship to say that records can be created in the Enrollments Table

per each relationship. Something doesn't feel right about this.. Is something wrong here ?

[Ugo DI LUCA]

Hi,

If you want the ID to be hided, then you'd use another portal based either on a Cartesian Join (the X sign) listing all students, or on a filtered Type-Ahead portal.

Then each row of this portal would be a button assigned a script which would create a new record in the Assignment and set the StudentID with the ID you just picked in the Student record from this portal.

[Ugo DI LUCA]

I'm guessing I would put the portal on the Enrollments Table Occurrence

and the portal would have only the following fields in it ?

STUDENTS TABLE::Student_Name

CLASSES TABLE::Classes_Name

Having a portal in the Enrolment Table wouldn't help much. The Enrolment Table helps linking Students to Classes, and would be used to get lists of assigned students.

There's in fact :

- very little need to have Form Layouts in such a Table, therefore no need for a portal either there

- many risks to have users "playing" in a Join Table

Assigning a Student to a Class could be made either

- in the Class Table, involving the Portal listing Students

- In the Student Table, involving a Portal listing Classes

Those using Separation would even use other ways.

[Steven Cappiello]

Is it a bad idea to make your primary key field a concatenated calculation field

(eg. Last Name & ", " & First Name & " - " & Student ID )

if so.. why ?

[Ender]

I'm not sure what table you are thinking of using this concatonated key in. You should have a unique Student ID in Student already.

As for the join table: Conceptually, you can think of records in your join table being uniquely identified by their StudentID-ClassID pairs. However, in practice, if there ever needs to be another relationship off the Enrollment table, then it's better for Enrollment to have an auto-entered unique ID. A unique ID in each table is much safer for avoiding duplicate ID problems.

Also, names don't work well for relationship IDs. They change too easily, and then your relationships don't work.

[Ender]

PS: For interface relationships, using other fields to build relationships is fine.

[Steven Cappiello]

the main reason i wanted a concatenated join field is that i wanted to be able to "type to" the value in the value list