Proper way to Relate 2 Databases by Name

[AvrioTech]

I'll give you guys a senario that im having a hard time with..

I have 2 tables (Table 1 RollCall) (Table 2 Students with information)

Im relating the rollcall table to the students table based by a dropmenu valuelist of active students (so by name instead of table ID). I cant figure out a way to relate the tables by ID instead of student names by a valuelist.. Anyone have any idea's? I would keep the relationship by name but what if theres more than one student with the same name then there would be a problem.

Thanks

[T-Square]

Check out the Filemaker Help. There is a good discussion of how to build relationships. In brief, your rollcall table needs a StudentID field that takes the ID for a given student. Before you go too far, note that each entry in the rollcall table likely represents one student's attendance on one day. Or else you are adding multiple dates in successive fields in each record. There's likely to be high redundancy, and you might want to consider creating other tables (CourseInfo?, Teachers? others?).

In relational databases, each table should have one field (or a combination of fields, but I digress), that is used as the *key field*. This field is set up to be unique, so that when you request a record (or the database "requests" a record), the system can return that record without ambiguity. That's why you read so much in these forums about ID fields.

HTH,

David

[AvrioTech]

Gotcha. I should have explained myself better sorry. I understand that all records should be related by unque values only (recordIDs).

I guess my problem is there a way to extract the record ID from the student value list without showing the recordID in the value list (if that makes sense). A teacher wouldnt know what "2342 John Doe" means in the value List compared to "John Doe" while 2342 is the student ID. Also teachers love the autocomplete field option.

[Sergeant Ron]

If you really want to relate by name you could create a text Calc field that concatenates the name with the DOB (NameDOB) in which the calc would look something like (Name & DOB) text.do that in each table then relate them to each other. It's rare that you'd have the same name and DOB for more than one person at your location. You could also use a Social Security number as a unique identifier if you wanted. Hope that was helpful.

Ron

[T-Square]

Since you're in 8, you should look at the options in the value list definitions. I hear that they allow you to conceal the ID field (as has been possible in Access for some time). It was a big feature in the announcement literature.

[AvrioTech]

Since you're in 8, you should look at the options in the value list definitions. I hear that they allow you to conceal the ID field (as has been possible in Access for some time). It was a big feature in the announcement literature.

I thought the same thing you did, but it works oddly : . For instance in the value list you can have two values and Hide one. Lets say I have an ID(value1) and a Name(Value2) in a valuelist. I set the options to show 2nd value only (Name), While you select the values, It just shows the names only (which is good) but once its selected it only shows the ID value in the field which is very bad, because it will confuse the user. I wish there was a way to grab the second value without ever seeing it.

[AvrioTech]

If you really want to relate by name you could create a text Calc field that concatenates the name with the DOB (NameDOB) in which the calc would look something like (Name & DOB) text.do that in each table then relate them to each other. It's rare that you'd have the same name and DOB for more than one person at your location. You could also use a Social Security number as a unique identifier if you wanted. Hope that was helpful.

Ron

I thought of this too and will probably end up doing something similar. By doing it the way you suggested would fix one of my problems by making the user tell the difference between "John Doe" 1 and "John Doe" 2 if they have the same name. I was thinking of trying to add a picture + name valuelist (if its possible). I guess im being pretty anal about the valuelist thing, but I personally dont see a reason why the user would ever need to see record numbers in a value list. If anyone can think of any other ideas please tell me. You guys are comming up with some good ones.

Thanks

[comment]

These two threads might be helpful:

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

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

[AvrioTech]

These two threads might be helpful:

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

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

Well darn, I was hoping for a "grab hidden value from value list function" but I guess there isn't one :. I guess ill have to go to the old school way of Name + ID .

[comment]

I may be missing the meaning of "grab hidden value from value list function". I thought defining the value list as ID + Name (with first field hidden), and defining the ID field as a popup menu would give you what you're after.

[AvrioTech]

I may be missing the meaning of "grab hidden value from value list function". I thought defining the value list as ID + Name (with first field hidden), and defining the ID field as a popup menu would give you what you're after.

Yes its a fix sorta, but to me, its alot faster to use pulldown menus instead of popups, Also the autocomplete is nice to : . Everytime ive tried popup menus people get mad at them because it takes alot more time entering them in vs pulldowns (especally when theres alot of options). Thanks for the information on them, I never knew you could do that with popups.

thnx