3 Replies Latest reply on Aug 3, 2009 6:54 PM by comment_1

    Calculated fields based on multiple records

    futility

      Title

      Calculated fields based on multiple records

      Post

      I'm trying to set up a table so that every field in it gets populated by information from another table.

      I have 3 tables. CONTACTS, REGISTRATION, SCHEDULE

      CONTACTS has a primary key, registration and schedule are both linked to CONTACTS via that key. I have a script so that when I create a new record in CONTACTS it also creates a new record in the SCHEDULE table so the two link.

      I'm trying to create various records in REGISTRATION table and have information then auto entered into the SCHEDULE table.

      My fields in SCHEDULE are all set up to be calculations based on an if function. My problem comes from the fact that they are only accessing the 1st matching record in the REGISTRATION table and that's it. I delete a record a different field then updates.

      How do I get my calculation to work with related records in REGISTRATION as opposed to just the 1st created record?

      Right now my calculation is:

      If ( REG::reg_day= "Monday" and REG::reg_period="1" ; REG::reg_class_name).  Iv'e also tried If ( REG::un_fk=SCH::un_fk and REG::reg_day= "Monday" and REG::reg_period="1" ; REG::reg_class_name)

       

      please help






        • 1. Re: Calculated fields based on multiple records
          comment_1
             This doesn't sound like a good approach overall. You should be using relationships to refer to records in other tables, not calculations. I am afraid I cannot say more than that without understanding what your solution is supposed to do.
          • 2. Re: Calculated fields based on multiple records
            futility
              

            I'm trying to make a school schedule. 

             

            Different kids (CONTACTS) are enrolled in different classes (REGISTRATION) which are then displayed on their schedule (SCHEDULE) which is broken down by periods and days.  While all the data about their classes is written in the REGISTRATION table, I need that data to post accordingly.  From everything I've tried, making another table where the data gets copied over seems to be the right way (as this would be a sort of hybrid style from a previous database we had)

            • 3. Re: Calculated fields based on multiple records
              comment_1
                

              I am afraid that's still very sketchy. Do they register for classes (if so, where is the Classes table?) or does each student have their own private scheduling (as is in music lessons, for example)?

               

              In the first case, the schedule would be a child of Classes, and each student's schedule would be given automatically by enrolling into one or more classes. In the latter, Schedule would be a child of Registrations, not of Students.