6 Replies Latest reply on Apr 8, 2014 8:58 AM by the606_1

    Script or Relationship issue?

    the606_1

      Title

      Script or Relationship issue?

      Post

           Hi

           Im stuck.

           I have two tables; Student reports & Report setup.

           In report setup there are 5 fields Year, English Comment, French comment. entry date (auto timestamp) and comment number (auto serial)

           In student reports there are many fields but these include student ID (unique), English comment1, English comment 2, French comment 1, French comment 2, Year and a few others.

           The two tables are related using the year field (on the form I only want comments from the selected year to appear).

           Basically I need to save the English\French comments into the student reports table based on which English comment or comment number is chosen from the dropdown list (this is because we need to keep the reports against each student but the comments change every so often and I cannot have an ever increasing numbered list of comments as this will be unmanageable for the teachers).

           So, how can I achieve this?

           I can obviously save the one selected in the dropdown but I need to save both.

           Thanks, any help greatly appreciated.

            

            

            

        • 1. Re: Script or Relationship issue?
          philmodjunk

               Can you explain what you want to do with these comments in more detail? Perhaps with an example of how you want to make this work?

               I don't understand why you have this set up as you can simply click or tab into a comment field and enter a comment. I don't get the process of "saving" nor why both tables have comment fields yet are linked by the Year. Do you want one comment to apply to all students for the year?

               

                    the comments change every so often and I cannot have an ever increasing numbered list of comments as this will be unmanageable for the teachers

               You might want to consider a related table linked to students that stores all comments ever posted about that student. A portal can display those comments with them sorted so that the most recently added comments are listed first. That should make the comments manageable, but also provide the ability to review all past comments posted about a given student.

          • 2. Re: Script or Relationship issue?
            the606_1

                 Hi,

                 Thanks for the reply.

                  

                 Basically this is for use in a foreign language school. The teacher chooses the English comment but the French version of the comment is entered into the reports etc. sent to the student or parents (as this is their local language).

                 There are around 600 students.

                 I haven't used portals at all so I must have a read.

                 Hope this helps.

                  

            • 3. Re: Script or Relationship issue?
              philmodjunk

                   Yes but why enter a comment into a table linked to all students by year? You don't want to enter the same comment for all students for that year do you?

                   Are you not recording a different comment for each student? Perhaps one or two comments for each student per year?

              • 4. Re: Script or Relationship issue?
                the606_1

                     It could be up to around 10 comments per student per year.

                     The reason I want to keep the comments per student per year is they may be there for more than one year and we need to be able to go back and look.

                     There is also other stuff on the report that's not an issue to store too.

                • 5. Re: Script or Relationship issue?
                  philmodjunk

                       Ok, but why do you have comments in both tables?

                       Here's what I recommend from what may be an incorrect understanding of what you need:

                       Students-----<Comments

                       Students::__pkStudentID = Comments::_fkStudentID

                       Fields in the Comments table:

                       Date (auto-enters the creation date), Comment ( text) , _fkStudentID (number) and maybe a cYear field defined as Year ( Date ).

                       You can place a portal to Comments on your Students table and sort the portal by Date in Descending order so that the most recent comments appear at the top of the portal. At your option, you can add a portal filter that filters the comments by year to show either only the comments for the current year or only the comments for a specified year. With a portal that sorts the most recently added comments to the top, you may want to add a button with a script for adding new portal records so that you can click a button to put a new blank record at the top of the portal in order to then enter data into the comment field in that new blank record to record a new note.

                       And now that I've posted this, I realize that if you have a traditional school year that starts in the latter half of one year and ends in the first half of the following year, you may want to define cYear with a more sophisticated calculation to return the same year for your entire school year rather than the calendar year, but the basic principles described here will be the same.

                  • 6. Re: Script or Relationship issue?
                    the606_1

                         Thank you so much for your time, I will look at this now. Going to do some playing around with Portals so I understand.

                          

                         Thanks again.