6 Replies Latest reply on Dec 1, 2012 11:49 AM by philmodjunk

    Using dates to autofill



      Using dates to autofill


           I am building a databse that allows teachers to record some lesson information for a class.  Each class has a schedule for each day ie day, period of day, start time, classroom which i have entered on a table called Subject_Schedule.  What i am unable to work out is how a teacher can select a date on a layout  for a related Subject_Information table using a calendar drop down that will cause the remaining information to appear ie if the date is a thursday it fills in the info from the subject schedule that matches a thursday.

           At the moment I can get information if I manually choose the day but I would like this linked in so that they date works out the day and causes information fill. I have used a cDay field to work out the day but cannot link this together. 

           I have a feeling it is an easy fix but cant for the life of me work out how!


           Any help greatly accepted

        • 1. Re: Using dates to autofill

               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.

          • 2. Re: Using dates to autofill

                 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.

            • 3. Re: Using dates to autofill

                   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.

              • 4. Re: Using dates to autofill

                     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

                • 5. Re: Using dates to autofill

                       picture didnt add before

                  • 6. Re: Using dates to autofill

                         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.