You'll need to describe the tables, their purpose and how they are related at this point before we can explore ways to make this work for you.
Ok - thanks Phil
I have the following tables
Subjects with ID, Name, Year group fields (stores subject info)
Subject Schedule with ID, SubjectID_fk, Day, Period, Classroom (Subject::ID=SubjectSchedule::SubjectID_fk) (stores timetable info)
Lessons with ID, SubjectID_fk, Date,Term, Week, Objectives and some other related lesson fields. (Subject::ID=Lessons::SubjectID_fk) (stores lesson info)
I have a subject schedule layout that is allows uses to enter the schedule for each subject using the SubjectSchedule table.
I have a layout based on the Subject table which includes a portal based on the Lessons table showing a brief summary of the lessons that are related to that subject ie date, topic
I have a layout called Lessons based on the Lesson table where the detailed information will be stored.
That is the set up i currently have. What i want to allow is for a date to be selected either in the portal on the Subject layout or in the lesson layout that will automatically enters/shows the period of the day, time the class starts and where the class is from the SubjectSchedule table. On the lesson table i can select a date and have added a cDate field that gets the day but i am unsure how or whether i need this to pull out the other info.
Hopefully this is clear.
Is this what you have?
Subjects::ID = Subject_Schedule::_fkSubjectID
Subjects::ID = Lessons::_fkSubjectID
If any of this notation is unfamiliar: Common Forum Relationship and Field Notations Explained
What type of value is stored in Subject_Schedule::Day?
I don't think that you want to select this date IN the portal but rather a field on the layout such that only Lesson/subject_Schedule records appear for the specified Subject and date.
For the subject layout, you can create a new occurrence of Lessons with this relationship:
Subjects::ID = LessonsByDate::_fkSubjectID AND
Subjects::Date = LessonsByDate::Date
Then a portal to LessonsByDate will show only the lesson record(s) for the current subject for the date selected/entered in Subjects::Date.
A similar method can be used for Subject_Schedule from the Lesson layout, but we may have to set up a calculation field to get values that match to the Day field in that table.
To create a new table occurrence:
In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Thanks Phil - I think I understand this and will try to implement your suggestions to assist finding classes.
I may not have been as clear as i needed to be as i am wanting a user to be able create a new record on the lesson layout then select the date of the lesson with a drop down, from which the day is calculated and then fills in all the other information based on this day ie New record - select the 3rd of December - works out that the day is a Monday so enters time as 9.30 and class as CA 11.
My current set up lets me select a date but i cant get the record to work out what day it is and fill from that. This layout is based on lessons table, the date is Lessons::date, whilst the day is SubjectSchedule::Day, period is SubjectSchedule::Period and time is SubjectShedule::time
picture didnt add before
I asked a question that is still unanswered: What kind of data is stored in Day? A number? text naming the day of the week? a Date?
Your screen shot shows fields that come from at least two different table occurrences. Those that start with :: in your screen shot come from a related table occurrence where the others come from the layout's table occurrence. Such fields, unless located inside a portal refer to the "first" related record in the related table occurrence--which may not be the record you want.