14 Replies Latest reply on Apr 3, 2017 5:51 AM by pademo57

    Checking Next Day Schedule

    pademo57

      In the place where I work we have a different schedule depending on the job you are currently working. So

      "Schedule A"  4 days on, 4 days off.

      Schedule B" 3 days off 5 days on

      etc.

      Currently the person who wrote the program before me set it up like this:

      Day_Name, Day_Work are the fields

      Sched. A

      Day1 ON

      Day2 ON

      Day3 ON

      Day4 ON

      Day5 OFF

      etc ..

      Sched B

      Day1 OFF

      Day2 OFF

      Day3 OFF

      Day4 ON

      etc...

      Each day you have to put in a report in Filemaker  and at the end of your "Days ON" you then have to write an End of Cycle report in Filemaker.

       

      What is the best way to get Filemaker  to check if after writing a report if the next day is an "OFF" day, and if so, tell the user to write an "End of Cycle" report? I assume this would be in a script.

        • 1. Re: Checking Next Day Schedule
          philmodjunk

          How does what you show us break down int fields and records?

           

          Is sched A in one table and Sched B in another?

          Is the vertical list of "day" entries that you show all fields on one record or a list of records?

           

          I suspect that the best approach will be to reorganize your data into a more sensible data model. But that will not be simple to do so you may need to look at an interim fix that solves the immediate issue while figuring out a scheme to transition to a better design.

           

          But I'm getting ahead of the process here as I don't really know how this is currently set up. I'm just warning you that you'll likely get advice to redesign as well as how to resolve the immediate problem.

          • 2. Re: Checking Next Day Schedule
            Jason Wood

            I think you should tell us more about what tables are involved, and what fields they contain, and examples of data in these fields.

             

            I could make some assumptions based on what I see so far but I don't like my chances!

            • 3. Re: Checking Next Day Schedule
              pademo57

              Hi There,

              Yes, I should have given more details. Plus the fact I'm taking this job over from someone else who has left the company.

              Table: Schedule

              __pk_SchedID

              SchedName

               

              Table: SchedDetails

              __pk_SchDetailsID

              _fk_SchedID

              SchedDetails_Day_Name

              SchedDetails_Day_Work

               

              Table: Report

              __pk_ReportID

              _fk_SchedDetailsID

              ReportName

              ReportDate

              etc.

               

              On the layout: Schedule

              A portal has SchedDetails with the following fields:

              SchedDetails_Day_Name, SchedDetails_Day_Work

               

              On the layout: Report

              ReportName

              ReportDate

              SchedDetails_Day_Name

              Button which asks if you want a new Report

              If yes, on the new Report it takes the SchedDetails_Day_Name and adds 1

               

              But my issue is, it doesn't check to see if the next day is a work day or not.

               

              I hope this makes sense?

               

              • 4. Re: Checking Next Day Schedule
                Jason Wood

                You say, "on the new Report it takes the SchedDetails_Day_Name and adds 1"

                 

                And what is it doing with this information?

                 

                Sounds like you create a report for "Day3" and it also puts "Day4" in a field so you know what tomorrow is? And so all you need to know is if SchedDetails_Day_Work is "ON" or "OFF" where the SchedDetails_Day_Name is "Day4"?

                • 5. Re: Checking Next Day Schedule
                  philmodjunk

                  it helps a lot and the data model looks good. No need for a redesign that I see.

                   

                  So each record in schedule is one employee's work schedule?

                   

                  How does this get used?

                   

                  Does a record get created in schedule details each day when an employee records data for the day?

                   

                  Are the detail records created as a group each time a new schedule record is created?

                   

                  And then those records get updated each day?

                   

                  Or do they only get updated at the end of the cycle?

                  • 6. Re: Checking Next Day Schedule
                    pademo57

                    Not to bore people but the schedule is for workers at extrusion moulding plant and each day when a worker checks in he/she is assigned a job for the day. Or could be assigned to teach another worker a particular job.

                     

                    The schedule works great on a day by day basis but the guy before me was designing this Filemaker program to schedule into the future (more than a month at a time)

                     

                    Each record is one employee's work schedule for one day.

                    Each record shows the employee's name, his level of expertise, the jobs he can do with that level or the jobs he can teach based on that level.

                     

                    The Schedule and Schedule Details tables were designed so that Employee 1 when he finished for the day would enter the details of the job and where it progressed to.

                    If the job wasn't finished (I'm hoping we can use the Schedule Details to look at each Report and decide whether that worker can continue the next day on the job or if the worker is on a day off, it will put the job up for reassignment to another worker.

                     

                    I hope that was clear? Does it make sense?

                    • 7. Re: Checking Next Day Schedule
                      philmodjunk

                      In other words, the schedule detail records are created in a batch, but are updated on a daily basis to record what that employee did during that day. Or do they actually update a report record to record the work done?

                       

                      It's not totally clear to me why you have one report record for each schedule detail record. Do you record more than one item in "report" for a given employee for the day?

                       

                      But exactly when in this process do you need "know that the next day is 'off'"?

                      At the time that the report last report record for the day for that employee is completed?

                       

                      or what exactly? (there are many possibilities and the context at the precise point in time that you need this is important.)

                      • 8. Re: Checking Next Day Schedule
                        pademo57

                        We need to know when the next day off is for an employee fills in the report. A report must be completed either at the end of each shift or at the end of a job.

                         

                        If it is the end of the job, at the shift is not over then the employee could be assigned to another job.

                         

                        If it is the end of the shift, then the report will show whether the job was completed or requires further action.

                        And then (hopefully) whether that employee can continue the job on the next shift or not.

                         

                        Currently we have a report printed out of each job and another report printed out of each employee.  Which are then matched (paper clipped together) and the job # is handwritten on to the employee report. The employee number is then written on the job report. 

                         

                        We are trying to automate this process.

                         

                        Does that help?

                        • 9. Re: Checking Next Day Schedule
                          philmodjunk

                          Which are then matched (paper clipped together) and the job # is handwritten on to the employee report. The employee number is then written on the job report. 

                           

                          We are trying to automate this process.

                          I don't follow that that has to do with knowing whether or not the employee is off the next day. In many layout designs, that would be very obvious just by looking at the data on the layout, but in any case that does seem to have anything to do with including job and employee numbers on the reports.

                           

                          From your earlier description of the tables and match fields, you appear to have these relationships:

                           

                          Schedule----<ScheduleDetails----<Reports

                           

                          But there is nothing about a table of Jobs nor a table of  employees. If you have these relationships:

                           

                          Employee----<Schedule----<ScheduleDetails----<Reports>-----Jobs

                           

                          Then every report record can show both an employee number and a job number. This assumes an _fk field in reports for the job ID and an _fk field in Schedlue for the Employee ID.

                          • 10. Re: Checking Next Day Schedule
                            pademo57

                            Ok, I think we are getting a little off topic or probably I don't understand exactly what is required here.

                             

                            The script on the Report takes the current Report::fk_SchedDetailsID and puts it in a variable called $SkedDay

                            Then it goes to the Table called SchedDetails and performs a find on the var $SkedDay

                            If it finds it, then the script does a Show all Records and goes to the next record where it looks at whether the day is ON or OFF.

                            If it is ON it comes back to the Reports Table and assigns the worker

                            If it is OFF it doesn't do anything.

                             

                            So should I change this script at all or continue with it and have it go to another table where it would show the end of cycle report?

                             

                            I'm sorry I haven't been clear, I hope this helps.

                             

                            • 11. Re: Checking Next Day Schedule
                              philmodjunk

                              The script on the Report takes the current Report::fk_SchedDetailsID and puts it in a variable called $SkedDay

                              Then it goes to the Table called SchedDetails and performs a find on the var $SkedDay

                              A not unusual way to do this, but Go TO Related Records could produce the same result in a single script step.

                              If it finds it, then the script does a Show all Records and goes to the next record where it looks at whether the day is ON or OFF.

                              But that makes no sense. Doing that accesses a record assigned to a different schedule record--which would seem to be a schedule details record that might have been created for a different employee. Wouldn't it make more sense to perform a find for all schedule details records linked to the current employee instead of showing all records?

                               

                              If it is ON it comes back to the Reports Table and assigns the worker

                              By that do you mean, it creates a new reports record and links it to the schedule details record that is "ON".

                               

                              If it is OFF it doesn't do anything.

                              And is this where you want something different?

                               

                              And is this an accurate picture of the tables and relationships or not?

                               

                              Employee----<Schedule----<ScheduleDetails----<Reports>-----Jobs

                              • 12. Re: Checking Next Day Schedule
                                pademo57

                                Hi philmodjunk,

                                I agree with everything you said in the last post.

                                 

                                1) GTRR seems like a better way to approach this but, how I change the current script, I'm not sure.

                                2) I think he (the other programmer) assumed that once it found the record in SchedDetails it would only show what was related to that employee for his schedule and by going to the next record (Show all records) it would still be part of the employee schedule.

                                3) Yes it creates a new Report record

                                4) Yes that is an accurate picture of the tables.

                                • 13. Re: Checking Next Day Schedule
                                  philmodjunk

                                  2) This may have worked for you because of those "Off" records that always seem to appear at the end. They may serve as "separators" since the script apparently exits once it hits an "off" schedule record. But I'd not do it this way.

                                   

                                  Going back to your original post:

                                   

                                  What is the best way to get Filemaker  to check if after writing a report if the next day is an "OFF" day, and if so, tell the user to write an "End of Cycle" report?

                                  Much depends on your interface design of which I know nothing.

                                   

                                  Let's see, I'd pull up a found set of just schedule detail records for a specific employee. If there were a date field in each record to record the date of that "detail" record, we could find all records for the current employee with a date >= to today's date and sort that found set in ascending order. GetNthRecord in a script could then check the next detail record to see if the employee is "on" or "off".

                                  • 14. Re: Checking Next Day Schedule
                                    pademo57

                                    thanks philmodjunk,

                                    I did as you suggested and rewrote part of his script and it is working really well.