This relationship will match a student record to a specific tuition amount record by the criteria that you specify:
Student::AcYear = TuitionAmounts::AcYear AND
Student::Status = TuitionAmonts::Status AND
Student::Program = TuitionAmounts::Program AND
Student::International = TuitionAmounts::International
And then a TuitionAmount field in the Student data can use a looked up value field option to auto-enter a value from the TuitionAmounts table.
All 8 match fields must contain data for this to work. AcYear can be a global field so that you select the academic year once for your session. It can also be an unstored calculation field that computes a value from the current date or froma value in a related table.
ok i created a global field in my student table for Academic Year and linked it to Academic Year in my tuition table. the status, program and international fields are linked.
i created a Tuition amount field in my student table and put in the looked up value option, but the field is blank in all my records, almost like it can't find anything. Not sure what i did wrong.
Doesn't sound like you have done anything wrong.
As a test, create a new student record and select values in the 4 match fields. Does a tuition amount appear?
If so, you can put the cursor in one of the 4 match fields, show all records and select Relookup to update your existing records to copy over the current tuition amount values.
You may not want to use a looked up value setting that copies over a tuition amount like this. If you simply refer directly to the tuition amount field in the related table, such as by putting that field from the related table directly on your student layout, the value will appear and you will not need to use Relookup to copy over the values.
Here's the trade off: If you directly refer to the related table's amount field, any changes to that amount will automatically appear. If you use a looked up value setting, changes to the amount in the tutionAmount table will not automatically update the values shown in the Students table.
You'll have to decide which option works best for your use of this data. Often, invoicing/billing systems use a looked up value setting because they can't allow the values shown in invoices that have already been sent to the customer to change after the fact if a tuition amount is changed in the related table.
the tuition amounts only change once at the beginning of each new academic year. i'm not worried about overwritting the previous value, this will happen anyways for remaining students when we switch over to the new academic year.
How do i directly refer to the tuition amount from the tuition table, the tuition table isn't entered in using student ID#'s, so i need the field to go look at Academic Year, Status, Program & International Stu in order to find the correct tution to use from the tuition table.
Thank you for your help!
Go to your student layout.
Enter layout mode.
Use the field tool to add a new field object to your layout.
Select the TuitionAmount field from the related TuitionAmount table occurrence.
This adds the tutionamount field from the related table directly to your Student table.
done, but it's blank because the tuition amounts table doesn't specify which student ID is paying which tuition amount. I need the field to do a search to compare the 4 fields and then pull the tuition.
In excel i'd write a nested IF statement like IF(Student::Academic Year = Tuition::Academic, IF(Student::Program = Tuition:: Program, IF(Student::Status = Tuition::Status, IF(Student::International Student = Tuition:: International Student, Full Tuition Amount,"N/A"),"N/A"),"N/A",)"N/A")
It should not be blank and the student ID has no bearing on this issue.
Did you add the field from the TuitionAmount table to your student layout?
Yes i did, it's blank in all records. I don't understand how the system would know which tuition amount from the Tuition table (there are 16) to pull from for each individual student. Shouldn't i be creating some kind of calculation so the system knows to check the 4 fields?
You have a relationship with 4 match fields:
They must contain values that match the corresponding match fields in the related table:
If they do, then a copy of TuitionAmounts::TuitionAmount, when placed on the Student layout, will display the Tuition amount for that combination of the 4 values in your match fields.
If you have your relationship correctly defined, the next thing to check is the 8 match fields. Each pair of fields should be the same data type. The values in each pair for a related pair of records must be exactly the same or the field will be empty.
yes my Academic Year; Status; Program and International fields are linked. 3 of the 4 of those are drop down value lists, so the fields match exactly. the academic year is typed in, but they both have 2012-2013.
The Academic Year field in my students table is a global storage field, would that be the problem, because the field is still blank.
It should be a global field as otherwise you'd have to set this value each time you change to a different student record.
Check the data types in the fields. The two academic year fields, for example, should both be of type text. Becareful of invisible characters that might keep the records from matching as well. 2012-2013 will not match to the value 2012 - 2013. (There are spaces before and after the - in the second instance.)
ok, both academic year fields are text and i made sure they were the exact same. the tuition field is still empty.
ok i just tried something similar, i tried to add the first and last name fields from my Faculty of Science table and put them on a layout based on my Mandatory Courses table, they are blank. if my StuID# is linked between the two tables shouldn't it be able to pull the first and last names in?
ok sorry about the multiple posts, but i just found something else. if i manually go into my Mandatory Courses layout and manually add in a student's ID and select the dept the first and last name fields populate. but only if both StuID# and Dept are filled in. Why would it need me to specifiy the Dept, each StuID# is unique, that field alone should be enough for the system to be able to pull the first and last name from the other table.... shouldnt it??
How about we try something else. if i could create a button that when i press it, a pop up box with all available courses in the matching dept show up, and i can pick one and it will assign that course to the student. ??