6 Replies Latest reply on Aug 31, 2011 10:09 PM by xtremu

    Calculation Field Problem

    xtremu

      Title

      Calculation Field Problem

      Post

       I have two tables: Student and AccessLog, Relationship is Student::Student Number = AccessLog::Student Number and Student::StudentID = AccessLog::AccessLogID. Allow creation of records in this table via relationship already checked.

      In my student table I have Fullname field which is calculation type = First Name & "" & Last Name, In my AccessLog Table I have also a NAME field which is Text type and this table viewed as TABLE VIEW.

      During transaction I wanted to have a result in the  Student Number / Name / TimeStart / TimeEnd / Date field respectively in the AccessLog Table. The desired output should like this:

      Student Number   -  Name  -       TimeStart  -  TimeEnd  -  Date

      201111111                                 4:30:10                         Wed, August 24, 2011  

      201122222                                 4:30:30       4:30:45      Wed, August 24, 2011

      201133333                                 4:35:00                         Wed, August 24, 2011

      201144444                                 5:00:10       5:30:00      Wed, August 24, 2011

      This is my problem, the name of student did'nt show in the NAME field of the AccessLog table, the other are ok such as the StudentNumber, TimeStart, TimeEnd and the Date.

      I tried different relationships, calculated values, Get functions but have no luck to solve this. Please help to have a name of student in the NAME field of the AccessLog table.

       

      Thanks in Advance.

       

      Desired_Output.jpg

        • 1. Re: Calculation Field Problem
          philmodjunk

          If you have a valid relationship between the accessLog record and a record in the student table, you can simjply add the FullName field from the student table to this layout. You can remove the Name field from AccessLog as it is not needed.

          Your relationship looks problematic:

          Student::Student Number = AccessLog::Student Number AND
          Student::StudentID = AccessLog::AccessLogID

          What is the difference between Student Number and StudentID?

          And why do you match StudentID (Which would seem to uniquely identify a record in student) with AccessLogID (Which would seem to identify a record in AccessLog)?

          A relationship such as:

          Student::StudentID = AccessLog::StudentID

          Where Student::StudentID is an auto-entered serial number and AccessLog::StudentID is a simple number field (no auto-entered serial).

          • 2. Re: Calculation Field Problem
            xtremu

             Phil I removed already the Student::Student Number = AccessLog::Student Number relationship and what I left is the Student::StudentID = AccessLog::StudentID relationship. My Student::StudentID is an auto-entered serial number and AccessLog::StudentID is a simple number field (no auto-entered serial).

            I made a field in the AccessLog table and specify that field under Student table and FullName field. But still their is no name of student appeared in table view mode of the AccessLog table. Tried to used the script above but does'nt work, I removed also the Set Field [AccessLog::Name; Student::Full Name] in the script but still does'nt work, no name appeared in the assigned field Phil, What's missing or wrong Phil?

            Thanks for the quick replied.

            • 3. Re: Calculation Field Problem
              philmodjunk

              Do you have a value in AccessLog::StudentID that matches exactly to a studentID value in Student?

              • 4. Re: Calculation Field Problem
                xtremu

                 Phil sorry for the late response, having 4 days vacation. Anyway their is no value on both field because that was only the primary key and assigned for relationship only. Student::StudentID = AccessLog::StudentID relationship (Student::StudentID is an auto-entered serial number)=(AccessLog::StudentID is a simple number field). My script above is not correct or the script function that I used is not right to come up with the field that will show the fullname of the student in the AccessLog table?

                Phil I want to show the Fullname of the student together with StudentNumber, TimeStart, TimeEnd and Date when they scan/manually entered their student number in the gStudentNumber field, the script above for StudentNumber, TimeStart, TimeEnd and Date works fine except the script or relationship for the FullName field. Trying to figure out but still no luck. Please help

                Thanks

                 

                • 5. Re: Calculation Field Problem
                  philmodjunk

                  in order for the relationship to be valid, AccessLog::StudentID must receive the correct matching value from Student::StudentID. If it does not, then the relationship will not work. How a record in AccessLog gets that matching value depends on the design of your database. A script could add this value each time it creates a record in the AccessLog. If you create these records in a portal to AccessLog from a layout based on Student, the Id's are added automatically.

                  Those are just two out of many possible ways to set this up.

                  • 6. Re: Calculation Field Problem
                    xtremu

                     Thanks Phil, anyway I didnt used portal to show the desired field in my AccessLog table and Im only using the Table view mode. I was confused in the relationship that I have, I only change it and it works fine now Phil. Thanks a lot for the help.

                    xtremu