11 Replies Latest reply on Jan 5, 2015 1:55 PM by regex

    Counting

    regex

      Hi Everyone,

       

      I thought I posted this earlier today, but with the new site, not sure where it went.  That said, I created a simple DB that provides a Check-In and Check-Out status for students.  I just want to provide a script that shows me what students are currently checked-in and checked-out for that day.  The Check-In and Check-Out fields are timestamp fields. How could this be done?

        • 1. Re: Counting
          rstory

          Are you simply overwriting the check-in and check-out timestamp fields, or is this in a related table that adds new records for each instance of check-in and check-out?

           

          There are a few ways to skin this cat, the easiest way would probably be to create a calculation field that evaluates the values of the check-in and check-out fields and then determines what the check-in/check-out status is, and then base the report on that field.

          • 2. Re: Counting
            regex

            Basically, I just have a related table Students & Events.  The students status layout is based on the Events table.  The Events::statusInDate is the timestamp field and the Events::statusOutDate.  I want to add two fields on that same layout that would provide a calculation of current number of students checked-in and number of currents checked-out for that particular day.  Make sense?

            • 3. Re: Counting
              coherentkris

              You said "I want to add two fields on that same layout". What context is "that layout" layout based on?

              • 4. Re: Counting
                regex

                The layout is based on the Events table. 

                • 5. Re: Counting
                  mtwalker

                  If there are multiple students and multiple events, then I think you'll need a join table (maybe "Attendees). Each record in the attendees table will have the a field for the studentID and the EventID along with the check in and check out date. Then from the Event table you could calculate Count ( Attendees::outDate ) to get the number of checked out students and Count ( Attendees::inDate ) - Count ( Attendees::outDate ) to get the current number of checked in students.

                  • 6. Re: Counting
                    regex

                    It is always the same event.  If i do the Count (Attendees::inDate) would that actually count the inDate for the current day, or all days? 

                    • 7. Re: Counting
                      mtwalker

                      You have marked this as answered, are you good now?

                      • 8. Re: Counting
                        mtwalker

                        I may need a little more detail about what exactly you are trying to do.

                         

                        If it just a single event (i.e. there will always be a single record in the event table) that students attend, then I think the check in/out field should be in the Students table, because they are checking in and out. Then from the event table you can use the Count function. However, it will count all records, not just for a specific day. If you need to track multiple events/days with multiple students, you'll need a join table.

                         

                        Understanding and creating many-to-many relationships in FileMaker Pro | FileMaker

                        • 9. Re: Counting
                          regex

                          I only have two tables. Events and Students.  The event is always the same, but the attendance day and times different.

                           

                          For example, student arrives to a session on 1/5/2015 2:20:42 PM.  Then I check them out on that same day: 1/5/2015 2:37:43 PM

                           

                          The following day, I check them in and then again check them out on that same day.  All during the day--I might have 80 students checked in for that session.  I just want to see the total students checked in and total students checked out for that particular day. 

                           

                          The Students table has a __pk_Student_ID key and the Event table have __pk_Event_ID and  __fk_Students_ID keys.

                          • 10. Re: Counting
                            mtwalker

                            So if you have multiple days that can be attended by multiple students, then you have a many-to-many relationship. You can't do that with two tables (unless you create a new file for each day or clear out the check-in/out fields each day).

                             

                            I would create a join table (e.g. Attendees). In it I would have _fk_Students_ID and _fk_Events_ID as well as the check in time and check out fields. They could be timestamps or times. You don't really need the date because you could get that from the Event record.

                             

                            Each day create a new record in the Events table. Then add new Attendee records when they check in and update that record when they check out. There are several ways to create the records depending what functionality you need.

                            • 11. Re: Counting
                              regex

                              That works.  I created the join table as described and all is well.  Now, the script can do a find on records for a particular day and count the Check-In and Check-Out days.  Thank you very much!