5 Replies Latest reply on Jan 31, 2011 10:08 AM by philmodjunk

    Problem of setting time in portal row

    voltsmura

      Title

      Problem of setting time in portal row

      Post

      Hi All,

      I am using Filemaker Pro 11 Advance working in windows xp, I am developing a simple database for the school entrance system. I have two tables : student and Transaction tables, Layout is also Student and Transaction layout by name.

      Student table contain: Student information such as: StudentNumber, Name, LastName picture etc..Transaction table contain portal field such as TimeStart and TimeEnd which is shown in the Student table.

      Relationship is Student::StudentID = Transaction::StudentID, Allow creation of record in this table via relationship=enable

      Let's say I have 3 students in my database and every time they enter their student number in the Student Number field, automatically set the time in the TimeStart field of the portal (first row), then second enter of that student number script will find that record and set the time in the TimeEnd field of the portal (first row). Below is the flowchart but I am not sure if that's right. 

      I am new in scripting at the moment, can anyone help me do the script?

      Thanks Filemaker Team

      volts

      Flowchart-3.jpg

        • 1. Re: Problem of setting time in portal row
          philmodjunk

          I think you are making this more complex than it needs to be.

          Have the scanned or entered studentID be entered into a global student ID field. This is a different field from your existing studentID field.

          Then the following script will find the Transaction records for the student that is entering/leaving. Checking the most recently created transaction record will then tell you whether to create a new Transaction record and log the entry, or to update the most recent transaction record by logging an exit.

          #Prior to this script, Student number has been entered or scanned into global field, gStudentID
          Freeze Window
          Go to Layout ["Transaction" (Transaction)]
          Enter Find Mode [] //clear pause check box
          Set Field [Transaction::StudentID ; Student::gStudentID ]   //gStudentID can actually be defined in any table and this will still work
          Set Error Capture [on]
          Perform Find []
          Go To Record [last]     // unsorted records will already be in the order they are created. You may want to sort by date, time to be 100% sure
          IF ( Get ( FoundCount ) = 0 or Not IsEmpty ( Transaction::TimeEnd ) /* Log an entry */ ]
             New record/reuest
             Set Field [ Transaction::StudentID ; Student::gStudentID ]
             Set Field [Transaction::TimeStart ; Get ( CurrentTime ) ]
          Else If [ IsEmpty ( Transaction::TimeEnd ) /* Log an exit */ ]
              Set Field [Transaction::TimeEnd ; Get ( CurrentTime ) ]
          End IF
          Go To Layout [Original Layout]

          If you run this script from a student layout with a portal to Transation, you'll see the data in this portal update each time this script is run to log a student in or out.

          Note: if these are university students who might enter/leave after midnight, use timestamp fields and Get ( CurrentTimeStamp ) to log both the date and time in the TimeStart and TimeEnd fields.

          • 2. Re: Problem of setting time in portal row
            voltsmura

            Hi Phil,

            I followed your suggested script above to provide gStudentID field in the Student table and manually entered student number on that field wherein that student numbers are  stored in the database and it works fine now. It creates system time in TimeIn and TimeOut field with their corresponding StudenID number in the transaction table or layout. That's nice so far.

            1. I noticed that if I enter any numbers or any student numbers in the gStudentID field which is not stored in the database, it automatically save and also set the system time in the TimeIn and TimeOut of the portal and save also that numbers in the StudentID of the transaction table with time. Is there any way to restrict putting any numbers that is not in included in the database?

            2. I also noticed that every Student Number or StudentID produced only 1 TimeIn and 1 TimeOut time respectively for their first and second entry of the student number. What if they have 20 times entries, can we add another rows in every StudentID to record the time for the rest of the entries? So that if we want to find that Student number or StudentID we know how many entries they have in the transaction table?

            Hope you can help me figure out the additional script for this system.

            Thanks..

            • 3. Re: Problem of setting time in portal row
              philmodjunk

              1) there are several ways that you can add a check to validate manually entered student numbers. Here's just one way:

              Add these script steps to the beginning of the script I posted earlier:

              Freeze Window
              Enter Find Mode[]
              Set Field [Students::StudentID ; Student::gStudentID ]
              set Error Capture [on]
              Perform Find[]
              If [ Get (FoundCount) = 0 /* no student record was found for this number */]
                 Show Custom Dialog ["Error: no student record found for this nuber"]
                 Exit Script[]
              End IF
              //put the rest of the original script here

              2) If you give this script repeated tests, you should see that when a student enters or scans their ID numbers, New records are automatically created in Transaction on the 1st, 3rd, 5th, etc. times their ID is entered and then a timestart time is entered in this new record. On even numbered entries of that student's records, the most recent record will be used to log a TimeEnd. Thus, the more times a student enters and then leaves, the more records in transactions will be created for that student automatically. A Portal to transactions on the student layout should update automatically to show these ever increasing numbers of records.

              • 4. Re: Problem of setting time in portal row
                voltsmura

                Hi Phil,

                Thanks for the reply, based on your previous script I was figured out how to create multiple row in the protal within that specific StudentID by adding If [Get(FoundCount)>0, new record request, get timeIn and timeOut time etc. and that's ok. But my problem is that the time which is set in the TimeOut field for the second entry of that studentnumber/studentID in the gStudentID field is also the same in the TimeIn field..

                Can you please help me correct or add the script for this error? I want to record the time by each entry not to duplicate the time in the TimeOut in the TimeIn field for their next entry. Thanks

                Output look the same like this,

                StudentID     TimeIn               TimeOut

                1                 6:20:50 PM        6:20:55 PM

                1                 6:20:55 PM        6:20:58 PM

                1                 6:20:58 PM        6:21:00 PM

                1                 6:21:00 PM       

                2                 6:30:00 PM        6:30:10 PM

                2                 6:30:10 PM        6:30:15 PM

                2                 6:30:15 PM     

                 

                  

                • 5. Re: Problem of setting time in portal row
                  philmodjunk

                  Check your script:

                  These are the two script steps that enter the TimeIn and TimeOut:

                  Set Field [Transaction::TimeEnd ; Get ( CurrentTime )

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

                  They enter the time currently showing on your computer's system clock at the time the script is run. They will not enter the same time into both fields like you have shown unless you scan an ID card twice within a few second interval. I can see times entered a few seconds apart, if you are just swiping an ID card or manually entering an ID number within seconds of each other, then I would expect to see data like you have posted.