1 2 Previous Next 17 Replies Latest reply on May 18, 2015 8:57 PM by philmodjunk

    Students and Date

    ThomasShaw

      Title

      Students and Date & Time Records

      Post

      To anyone who can assist:

      I'm working on an aftercare billing solution with several tables.  For now I would like to focus on (2) tables- Students and Date and Time.  I have linked up a relationship for IDStudent in the Student Table to IDfStudent in the Date and Time Table.  I have a layout that lists all the students in list view and I have (2) buttons CheckIn and CheckOut out to the far right of each student in the list.  How can I create a new Date and Time record for the student in the Date and Time table without using a portal and/or switching layouts.  I can't get my head wrapped around getting the student ID from the Students Table into the foreign student ID on the Date and Time Table.

       

       

        • 1. Re: Students and Date & Time Records
          philmodjunk

          But you can switch layouts via script and the user will never see the layout change. This can be done without changing layouts and there are advantages to that approach--such as avoiding tripping script triggers, but the layout change script is a much simpler approach:

          Set Variable [$StudentID ; value: Students::IDStudent ]
          Go to Layout ["DateAndTime" (DateAndTime) ]
          New record/Request
          Set Field [DateAndTime::IDStudent ; $StudentID ]
          Go to Layout [Original Layout]

          The check in date and time can be "on creation" auto-enter field options and thus do not need to be scripted.

          Is there a particular reason why you want to avoid the above method?

          • 2. Re: Students and Date & Time Records
            ThomasShaw

            I'll create a script with the following steps you outlined.  I have no problem doing what's outlined above as long as the user never "witnesses" the layout changes.

            I'm trying to make the CheckIn and CheckOut process as simple as possible for the end user.

            I greatly appreciate the quick response.  I'll try it all out soon.  Thanks!

            • 3. Re: Students and Date & Time Records
              ThomasShaw

               

              Taking things a step further...

              The CheckIn button is set to be hidden after the Date and Time record creation/auto time entry.  How can I script the CheckOut button to find the record that was created with the CheckIn button?

              • 4. Re: Students and Date & Time Records
                philmodjunk

                I don't know that you do need to "find" that record.

                You should have a relationship defined between the two tables that links by student ID. You can double click that relationship line to open a dialog where you can sort the DateAndTime records in descending order by date or timestamp checked in. This makes the most recent check in record the first related record and:

                Set Field [DateAndTime::TimeOut ; Get ( CurrentTime ) ]
                Set Field [DateAndTime::DateOut ; Get ( CurrentDate ) ]

                or if you use a field of type timestamp to record date and time in the same field:

                Set Field [DateAndTime::TimeStamp ; Get ( CurrentTimeStamp ) ]

                And this can be the script performed from a button on your student list layout.

                Using the same relationship, this HIde Object When expression would automatically hide the check in button:

                IsEmpty ( DateAndTime;;TimeOut ) AND Not IsEmpty ( DateAndTime::TimeIn )

                • 5. Re: Students and Date & Time Records
                  ThomasShaw

                   

                  That did it.

                  I can see how it all works once I enter your suggestions but getting my head wrapped around all this leaves me struggling.  ;)

                  I've been reading the FileMaker Basics and FileMaker Advanced iBooks as well as FileMaker 13 Training over at Lynda.

                  Databases make my head hurt.  I know what I want FileMaker to do but getting it to do it is another story for me...

                  Thanks for all the help and advice.  I'm sure I'll pop in with something else soon the deeper I get into this solution.

                  • 6. Re: Students and Date & Time Records
                    ThomasShaw

                    What would be the best way to count the number of students presently "checked in?"

                     

                    I created a status field in the Date and Time table and when a student gets checked in, a script step will set the value of that field to 1 (checked in) in the Check In script.

                    When checking out I added a clear script step to clear the status field.  I've found that the field isn't always cleared out as it should be.

                    I have a summary field for the status field to count the number of records with a status of 1 (checked in)

                    Surely there is a better way to do this and get accurate results...

                    • 7. Re: Students and Date & Time Records
                      philmodjunk

                      Instead of clear, use set field to set the field to "". Clear requires that the field be present on the current layout at the time the step is executed. Set Field does not, though "context" is still important.

                      But simply performing a find for all records where the date, time or timestamp "out" field is empty would produce a foundcount that = the number of students currently checked in.

                      There are also ways to use either ExecuteSQL or a relationship to count records currently checked in but not checked out.

                      For more about "context" see this thread on table occurrences: Tutorial: What are Table Occurrences?

                      Warning, that thread may expand your horizons a bit if you haven't looked into how table occurrences are used in a FileMaker solution.

                      • 8. Re: Students and Date & Time Records
                        ThomasShaw

                        Made changes to my check out script and that works as expected...  Many thanks!

                        I dropped my CheckInSummary field from DATE_TIME_student table to my STUDENTS table and the summary field is not updating properly.  I would presume that you can not do that since it's not working as anticipated...

                        The idea I had was to have the checked in count visible at the top of the student list so that we can see how many students are presently checked in and that count automatically increase and decrease as kids are checked in and checked out.

                        • 9. Re: Students and Date & Time Records
                          philmodjunk

                          The summary field should be able to work and update just fine. I'd need to know more about how you set it up to know why it isn't--might need to add a commit records or Refresh window script step or there may be some other issue involved.

                          • 10. Re: Students and Date & Time Records
                            ThomasShaw

                            Here is images of the setting for the summary field I created.  The field is in the Date_Time table and it adds up properly there.

                            I used the field picker to add the summary field from Date_Time to my Student List layout in the header.

                            This small image may not be enough for you but the summary is Total of w/running total checked.

                            • 11. Re: Students and Date & Time Records
                              philmodjunk

                              In what table is this defined? Students or the dateandTime table?

                              In any case, this should not be defined as a running total table. But you have additional issues if it's defined in the related table instead of the Students table.

                               

                              • 12. Re: Students and Date & Time Records
                                ThomasShaw

                                The fields are in the Date and Time table.

                                Based off your comments maybe the CheckInStatus and CheckInSummary should be in the Students table?  I figured having them with the date/timestamps in the Date and Time table was the best place.

                                • 13. Re: Students and Date & Time Records
                                  philmodjunk

                                  The problem is context. When you reference a summary field from a related table. The value shown is based on what records in the related table are linked to the current record in the layout's table. This is still the case when you put the field in the header. In browse mode, the field in the header will report a summary value from the related table based on whichever record is the current record. Since your relationship is by studentID, this will be a count of the number of times a single student is checked in so you are unlikely to see any value other than 1 or no value at all.

                                  What you can do is make a new occurrence of your related table (select the table in Manage | database | relationships and click the button with two plus signs) and link it to students with the X operator selected instead of = (Double click the relationship line to get a dialog where you can change the operator). This relationship matches any record in students to all records in the dateTime table. You can then add your summary field from this new Tutorial: What are Table Occurrences? to your layout and get a summary value based on all DateTime records instead of just those linked to a single student record.

                                  • 14. Re: Students and Date & Time Records
                                    ThomasShaw

                                    Oddities abound...

                                     

                                    Created a new TO of Date and Time, linked to Students, and changed said relationship to "X" as suggested.  Dropped the summary field from the new Date and Time TO on my Student List layout.

                                    The summary field is not updating on a consistent basis.  Sometimes the value will not change but if you click in the field it will update to the correct value and/or the summary field will not show any value at all (be bank) and once clicked in the field the correct value will appear.

                                    (Summary field is not a running total.)

                                    Thoughts?...

                                    1 2 Previous Next