7 Replies Latest reply on Jan 7, 2011 8:34 AM by philmodjunk

    Time problem in the portal



      Time problem in the portal


      New in FileMaker Pro 11 Advanced. I am developing a simple Time Management System for the School.

      Here's my table and fields

      Student Table                                        AccessLog table

      Student Number - Number                     TimeStart - Time                                                                    

      LastName           - Text                           TimeEnd - Time

      FirstName - Text

      StudentID - Number (AutoSerial)           StudentID - Number

      Relationship is Student Table: StudentID and AccessLog: StudentID

      Note: 1. Every student has their own record such as Name, LastName, FirstName,StudentNumber, picture etc..

      2. TimeStart and TimeEnd are fields in portal from AccessLog table shown in the Student Table.

      3. Every student has their own ID with barcode: (barcode=student number)


      1. When the user makes their first entry into a Student Number field on the Student layout a script will record the system time in the portal as "Time Start" (first row of the portal).

      2. When they make a second entry, the time will be recorded as "Time End" (first row of the portal).

      3. When they make a third entry, the time will be recorded as "Time Start" (second row of the portal).

      4. When they make a fourth entry, the time will be recorded as "Time End" (second row of the portal) and so on, (loop).

      5. No time limit, as long as student enter their student number in the Student Number field then script will record the time either in TimeStart or TimeEnd in the portal on the student layout.

      6. This is to monitor and record the time of the student or employee in the portal.

      Below is my Access Time Log Script, For example I have 6 Student record in the database, the first entry of all Student record are ok because it record the time in the first row of the portal in the "Time Start" field, when I make a second entry for that particular record or student number, it is also ok, it record the time in "Time End" field. But when I make the second entry for the next record or student number it doesnt record the system time in the "Time End" field of that specific record, instead he just update the system time in the "Time Start" field in the portal. That's my problem.

      Please help and thanks in advance.


        • 1. Re: Time problem in the portal

          Hey, here i think the problem is in relationship graph.

          As studentId is a number field, which is autogenerated, so for the same student, two different studentId is generated for two entries.

          So here what you need to do is, you should create the relationship on the basis of some field, which'll be unique for each student. Doing this way, when two entries are made for the same student in table "AccessLog"(for two timein), then in the portal of student table, two rows will be generated in the same record.

          Try this, it'll work.

          • 2. Re: Time problem in the portal

            Since you may have multiple rows of data in the access log portal, you'll need to go to the correct portal row before you start assigning values.

            If you've enabled "Allow creation of records via this relationship" for accessLog in the portal's relationship, you shouldn't need a loop and can use a script like this after performing your find to find the student:

            Go To Portal Row [last]  // selects blank bottom "add" row
            go to Portal Row [Previous] // last row that may have any empty time end field
            If [IsEmpty ( accesslog::Timeend) // log student out]
               Set Field [accesslog::TimeEnd ; Get ( CurrentTime ) ]
               Go To Portal Row [Next] // go to blank "add" row to add new portal row
               Set Field ( accesslog::TimeStart ; Get ( CurrentTime) // log student in ]
            End IF

            This script will may not work correctly if you have more than one portal on your layout. IF it does a few small changes will need to be made to make sure that Go To Portal Row applies to the correct portal.

            • 3. Re: Time problem in the portal

              Thanks Raj for the response but i have no idea how to create the relationship of two different studentID. Need help for that. thanks.

              • 4. Re: Time problem in the portal

                Phil thanks also for your response and I try your suggestion, what happen is that the first entry record the system time in "Time End" field of the portal, second entry of that student record the system time at the "Time Start" field of the portal, third entry and so on stock at the "Time Start" field. It only update the time, no more recorded system time at the "Time End" field of the portal. Still having trouble with the code.

                • 5. Re: Time problem in the portal

                  Which is why you either need code to specify the correct portal row or you can use a script that isolates these records on a layout based on the portal's table, updates time out or adds new record with time in as needed and then pops back to your portal layout to display the results.

                  If the above script isn't working for you, try this script:

                  Go To Field [Student::StudentNumber]
                  Enter Find Mode [pause]
                  Freeze Window
                  Set Variable [$studentID ; Value: Student::StudentID]
                  Go To Layout [//specify layout based on accessLog]
                  Enter Find Mode[]
                  Set Field [accessLog::StudentID ; $StudentID]
                  Set Error Capture [on]
                  Perform Find[]
                  Sort [Restore; no dialog ] // sort by TimeEnd in descending order
                  go To Record/request [ first ] // after sort, first record will be most recently logged entry/exit
                  If [ IsEmpty ( AccessLog::TimeEnd ) // student needs to log out ]
                     Set Field [accessLog::TimeEnd ; Get ( CurrentTime ) ]
                     New Record/Request
                     Set Field [accessLog::StudentID ; $StudentID ]
                     Set Field [accessLog::TimeStart ; Get ( CurrentTime ) ]
                  End IF
                  Go To Layout [original layout]

                  • 6. Re: Time problem in the portal

                    Thank you for the big help Phil, anyway I have already all the data/record in my database and no need to add new data/record anymore.

                    Your suggested script work fine now because I have already a system time in the  “Time Start” field of the portal based on the Access Log table/layout  and same in the “Time End” field during student first and second entry.

                    The problem is, if I make a third or fourth etc. entry it is still record the computer or system time in the “Time Start” field in first row of the portal. I try to figure out how to add new portal then record the time in the “Time Start” field for the third entry and time in the “Time End” field for the fourth entry and so on up to last entry of that specific record/student number but I can’t.

                    I have only two table/layout: Student table/layout and Access Log table/layout.

                    Below is the script for your reference. Thanks again.

                    Freeze Window


                    Go to Field [Student::Student Number]

                    Pause/Resume Script [Duration (seconds): 2]

                    Enter Find Mode [Pause]

                    Set Variable [$studentID; Value:Student::Student ID]

                    Perform Find []

                    Go to Layout [“Student’’(Student)]

                    Set Field [AccessLog::Student ID[$StudentID]]

                    Set Error Capture [On]

                    Sort Records [Restore; No dialog]

                    If [IsEmpty (AccessLog::TimeStart)]

                    Set Field [AccessLog::TimeStart; Get (CurrentTime)]


                    Set Field [AccessLog::Student ID[$StudentID]]

                    Set Field [AccessLog::TimeEnd; Get (CurrentTime)]

                    Go to Portal Row [Next]

                    End If

                    Go to Layout [“Student” (Student)]

                    End Loop




                    • 7. Re: Time problem in the portal

                      You haven't followed either of my suggestions. I don't see why you need to add blank log records for each student ahead of time. I suggest you delete all access log records that have both time fields empty. Then let the either of the two scripts that I've posted create them as needed.