8 Replies Latest reply on Jul 18, 2014 8:34 PM by WeirdMan

    Showing Dates not recorded in attendance

    WeirdMan

      Title

      Showing Dates not recorded in attendance

      Post

           Hi, I created a database for attendance with this table:

           Employees --< Attendance Form --< daily attendance

      Attendance form is used to create weekly wage calculation and data entering is based on this table.
      daily attendance records is created from attendance form portal, and created when an employee is known working normally, or not showing up in his shift. Employee is not required to work everyday, but an employee may take 2 shift in a day, thus creating 2 records in a day. Not showing employee is flagged as absent in this record.

      QUESTION:

           How can I show if an employee is not showing up on specific days in a week, and show if that day is not his shift.

           here is what I want to be shown in list view:

            

                                                                                                                                                                                                                                                                                                                                                                                                                                   
                          Employee Name1                                                                                                                                   Employee Wage
                          Sunday                     Monday                     Tuesday                     Wednesday                     Thursday                     Friday                     Saturday
                          W                     H                     A                     W                     W                     H                     W

           ________________________________________________________________________

            

                                                                                                                                                                                                                                                                                                                                                                                                                                   
                          Employee Name2                                                                                                                                   Employee Wage
                          Sunday                     Monday                     Tuesday                     Wednesday                     Thursday                     Friday                     Saturday
                          H                     H                     H                     W                     W                     H                     A

           ____________________________________________________________________etc

           where "H" means not his shift, "W" means he is working (in 1 or many shift he take), "A" means he is not showing up in his shift.

           I am thinking if I need to force to create record in daily attendance table if it is not his shift too, flagging it is his holiday. If this harsh, forceful, and not efficient method is not necessary, can you help me how?

        • 1. Re: Showing Dates not recorded in attendance
          philmodjunk

               What does each column represent in terms of your tables and fields? Is each column a different record in daily attendance or is it a different field in the same record?

          • 2. Re: Showing Dates not recorded in attendance
            WeirdMan
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                Name                     Day                     Job desc                     Time in                     Time out
                                Employee1                     Sunday                     Cashier                     08.00                     15.00
                                Employee1                     Sunday                     Waiter                     15.00                     21.00
                                Employee1                     Tuesday                     Absent/missing                     08.00                     08.00
                                Employee1                     Wednesday                     Waiter                     08.00                     15.00
                                Employee1                     Wednesday                     Cashier                     15.00                     21.00
                                Employee1                     Thursday                     Cashier                     15.00                     21.00
                                Employee1                     Saturday                     Waiter                     08.00                     15.00

                 Lets say we want to represent employee 1 from my previous post, daily attendance records may look like this:

                 my policy is when employee is absent in one shift, he should not work for entire day.

                 note that "H" days are not created in daily attendance records. Should I make a record for "H" days as workaround, or is there a better approach?

                  

                  
            • 3. Re: Showing Dates not recorded in attendance
              philmodjunk

                   I'm still confirming your current design. Are the records in this table, records from daily attendance? And each row here is a different record? That's what appears to be the case, but since you didn't explicitly answer my question, I'm making sure that this is the case.

                   And what kind of field is "day"? is this a text field or a date field formatted to show the day of the week?

                   Does your relationship match fields like this? (but with your field names used in place of mine...)

                   Employee::__pkEmployeeID = AttendanceForm::_fkEmployeeID

                   AttendanceForm::__pkAttFormID = DailyAttendance::_fkAttFormID

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              • 4. Re: Showing Dates not recorded in attendance
                WeirdMan

                     I'm sorry for not answering your question briefly, Phil.

                     Yes, each row in that table on my post is a different record from daily attendance

                     day field is a date field, formatted to show dayname only

                     I believe relationships in my database matched yours, but just to be sure I'll attach a screenshot of my database relationship.

                • 5. Re: Showing Dates not recorded in attendance
                  philmodjunk

                       Thank you for providing the translation and yes, it appears that the relationships are the same.

                       

                            note that "H" days are not created in daily attendance records.

                       How do you then record such an absence if this is not a field in Daily Attendance?

                       Do employees work 7 days a week? I would think that this is not the case. How do you distinguish in your records between days where an employee is not present due to absence and not present due to this being a holiday or a regular day off?

                       If such "excused absences" are not a readily predictable schedule, you may need to log such absences by creating a record in Daily Attendance.

                        

                  • 6. Re: Showing Dates not recorded in attendance
                    WeirdMan
                         
                              

                                   note that "H" days are not created in daily attendance records.

                         
                         

                              How do you then record such an absence if this is not a field in Daily Attendance?

                         That is what I want to ask, do I need to create record for every "H" day? I am thinking if filemaker can filter dates that does not contain daily attendance records, then it can save a lot of time for not creating records.

                         Employees not working 7 days a week, but they may work 7 days on a very rare event.

                         I would like to let filemaker search for dates on day field on daily attendance table,
                         if a date have a record with "absent/missing" as job description then it will result as "A",
                         if a date have a record other than "absent/missing" as job description it will resulted as "W",
                         and if a date is not presented on a week period (specified by period_begin and period_end field), it would show "H" on the list.
                         I am wondering if it can be done.

                         if "excused absence" means what will show up as "A" on my list, then yes it does not have any pattern, and I'm logging it as a record on daily attendance table as you can see on my example records above on Tuesday. If it means what will show up as "H" it does not have pattern too, but it is not logged (yet).

                         EDIT: added some details

                    • 7. Re: Showing Dates not recorded in attendance
                      philmodjunk

                           My point is that if you are going to log absences and distinguish between "excused" and "unexcused" absences, you'll need to use one of two approaches:

                           As you have suspected, you can create a record in Daily attendance and enter a value in a field marking them as absent, excused, absent unexcused or present... That will then make pulling up an attendance report on each worker very simple.

                           A second option is to use a table where the dates for which an employee is scheduled to work is recorded in one part of your database and then compared to the actual dates that the employee worked. Any scheduled date that does not have a corresponding record in the Daily Attendance table would then, presumably, be an unexcused absence.

                           All in all, the first option would be the simpler method to implement.

                      • 8. Re: Showing Dates not recorded in attendance
                        WeirdMan

                             Thanks Phil! Now, how can I make it possible to show that shows "A", "H", "W" on list view like I mentioned on first post?

                             

                                   

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                                                 Employee Name1                                                                                                                                                                 Employee Wage
                                                 Sunday                          Monday                          Tuesday                          Wednesday                          Thursday                          Friday                          Saturday
                                                 W                          H                          A                          W                          W                          H                          W
                             

                                  ________________________________________________________________________

                             

                                   

                             

                                   

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                                                 Employee Name2                                                                                                                                                                 Employee Wage
                                                 Sunday                          Monday                          Tuesday                          Wednesday                          Thursday                          Friday                          Saturday
                                                 H                          H                          H                          W                          W                          H                          A
                             

                                  ____________________________________________________________________etc

                             I want it to show horizontally, so portal records may not be used.

                             Do I need to create calculation field for each day? How to make it check for date records with specified day name only for each column?