Sounds like a solid set up. Not sure what the question is, though...
I need to create a form where they select a trip from a dropdown and the accurate mileage populates the mileage field in table 3. I can get the dropdown to populate with the possible trips but it doesnt make the mileage appear in the mileage field.
There are several ways to do this.
I will assume your tables are called EMPLOYEE, MILEAGE, and TRIPS
Make another Table Occurance of the table 'MILEAGE'. Re-name it as trips_Mileage
Make a new ralationship from the TRIPS table to this new table, using the school-to-school field as the link in both tables.
On the TRIPS table layout add a new field. In the Specify Field dialogue change the Table Source at the top to be from the relationship trips_Mileage. As you change the school-to-school pop-down list you will see the correct mileage appear.
Note that you do not need to have 'Mileage' as a field in the TRIPS table.
You can create a field called 'Mileage' in the Trips table and make it autofill by a lookup. Base the lookup on that same relationship, and that same Mileage field from the MILEAGE Table. The advantage of that is that if one day they make a diverted trip you can over-ride the standard mileage.
Ive done this db application in Access but i need it to run on a MAC which is why im trying to reproduce it in File Maker if possible. Can i send either of you my access app for suggestions on how to do this best in File Maker?
My school system's filter wont let me view that site