Lara, this is pretty easy using a value list if your tables/relationsips are setup properly. It would be better to choose the Student ID first which auto populate the name field (I am assuming the Student Number is an auto.created serial number...but I question that looking at the table Contact List). Can you show us the relationship graph and/ or send us a copy of the file? Yes, let's make sure the relationships between tables is setup properly.
If I might make a suggestion that will make your life easier. Have 3 tables. Students. Enrolments. Classes.
The Students table has 1 record for every student with their personal information etc.
The Enrolments table keeps 1 record for each student for each class. So if a student is enrolled in 5 classes there will be 5 records for that student - 1 for each class.
The Classes table keeps 1 record for each class that is run. So if you have a class called Citizenship that will have 1 record.
Each of the 3 tables needs to have its own primary key field - ie. StudentID, EnrolmentID, ClassID. These should be auto enter serial number fields that cannot be modified.
The Enrolment table also needs foreign key fields for StudentID_kf and ClassID_kf - that way you can associate each enrolment record with 1 student and with 1 class.
The layout for Enrolling students should be based on the Enrolment table and will have dropdown/pop up menu fields for StudentID_kf and ClassID_kf - these will allow you to select from the student list and the classes list to populate the enrolment record.
I have attached a simple sample file for you showing this setup.
Student Classes.zip 67.5 K
I forgot to add that the relationship between your Students table and your Enrolments table is based on Student::StudentID = Enrolments::StudentID_kf.
The relationship between your Enrolments table and your Classes table is based on Enrolments:ClassID_kf = Classes::ClassID
Thank you for your help on this. This database project is a favor to a nonprofit organization and while I love messing around with computers, I admit that I am not a computer tech., in any way (i.e. I wouldn't pay me to do anything on computers!), so I really appreciate your help.
I have attached the file. I made everything menu driven to reduce user error, but nothing is password protected so you can see what I've done.
I tried my hand at the relationship thing...but it did not work--nor does it really make sense when I look at it from the outside. That, frankly is why I was trying to get away from using that space.
I welcome your thoughts and am grateful for your time.
30Mar.fmp12.zip 458.4 K
I think for most of us newbies that's hard to figure out is where to set the option or how to set the option for relationship type, for example one to many? I can't find how to set it anywhere! Lara and I both will need to set that so we can have one student or one customer with a relationship to many other entries.
Kevin, why it is important to have a unique primary key field in all your tables ... in your table Customer it is the field "ID". Taking your case as an good example ...we have an example of a one to many relationship where we have a customer who can have multiple Work Orders ... the "many" side of the relationship. to relate the two tables we need to have (what is called a foreign key) in the WorkOrders table. In your case it is called "CustomerID" . To actually make the relationship (in the Relationships Tab of Manage Database) you click on ID in Customers and drag over to CustomerID in WorkOrders. You can view and edit that relationship by clicking on the square box on the line connecting the two tables ... you now have a one to many relationship. attached is a screen grab hopefully better depicting the "how to".
I think for most of us newbies that's hard to figure out is where to set the option or how to set the option for relationship type, for example one to many?
there is no such option. What type a given relationship is depends on your implementation.
e.g. if you give your LineItems table a foreign ID for Orders, but not vice versa, that is a sure sign that Orders --< LineItems is intended as a one-to-many, not the other way around.
You need a CustomerID (a “foreign key”) in Workorders – exactly like you have a WorkorderID in Expenses. Consider
Customer::customerID --< Workorders::customerID
What you have done is relating the two tables by their respective primary keys which doesn't work. Relate the primary key of the “one” to the foreign key of the “many” table.
So, your three tables should have the following key fields:
customerID (primary key, auto-enter, unique)
workorderID (primary key, auto-enter, unique)
customerID (foreign key, same data type as Customers::customerID)
expenseID (primary key, auto-enter, unique)
workorderID (foreign key, same data type as Workorders::workorderID)
So you can build
Customers::customerID --< Workorders::customerID / Workorders::workorderID --< Expenses::workorderID
Ok I'll set it up that way!
Kevin, … only how you setup the field on creation and how you use it. i.e.. when you are setting up a “Primary Key Field” you are setting it as a Number, Auto Create, Serial… and that number will be the record identifier …you will not let it ever change. In the related table you will setup a “Foreign Key Field” as type… Number.…it’s value will be set when you create related records. FM knows when you make the relationship between two tables by dragging a field that is setup as a serial value to another table that it is setting up a one to many relationship… you can see this represented on the relationship graph. experiment with changing the relationship between two tables with different fields … by clicking on the ‘square’ on the line between the tables on the graph and how it changes the line connecting the tables.
this is wrong …. in WorkOrders the customerID field need to be of type Number …that’s it. Turn off "Auto-enter Serial, Unique"