AnsweredAssumed Answered

Is self-join the correct solution for showing related records from same table?

Question asked by clayhendrix on Jul 2, 2016
Latest reply on Jul 23, 2016 by keywords

I have not used self-joins before. I am not certain that is the type of relationship that I need for what I am trying to accomplish.


I am making an Employee Time Clock Kioski layout. The table HR_Punches contains fields created by employees when the clock in or clock out. The fields are populated with scripts that are ran when employees click one of the three buttons: Clock In, Clock Out, or Done.


Desired Functionality A: I want the employee to be able to see their last punch when they come to the kiosk for their next punch. That way they will know if they missed clocking in, clocking out, how many hours they have worked since they clocked in, etc.


To accomplish Desired Functionality A, I created another Table Occurrence (TO) for HR_Punches and created a one-to-one relationship between the _id_employee_fk fields in the two TOs.


The user experience is acceptable, but I think the design is poor. The layout is based on HR_Punches, so in order to have an empty field for the employee to key his/her ID# into, the last step of the previous employee is to click the Done button which simply creates a new record in HR_Punches. It's functional, but sometimes employees key an id wrong or click the wrong button and they feel stuck until they can get the ID# field to be blank again. So, I had to create a button titled Help that creates a new record in HR_Punches. They employee thinks everything is fine and resumes his/her punch process.


That creates blank records in HR_Punches, so I wrote a script to delete blank records. I do not like that script to run automatically and I really do not like the idea that I have created a system whereby the design is so poor that blank records are regularly created in the table.


Having said all of that, my thinking is that I need to have FileMaker receive the keyed ID# from the employee and display his/her name and the clock in or clock out buttons prior to creating a record in HR_Punches. That seems like a better system than creating a new record at the end of the last employee's clock in or clock out and if an employee entered the wrong ID#, they could re-key or cancel prior to committing the record and without creating a new record just to get a blank ID# field so they can start again.


Perhaps, I should give up on Desired Functionality A, in which case I would not need the self-join, but I would still have the other issues related to creating new records at the end of the clock in or clock out for the next employee and creating new records as a method of resetting the process (from the employee's perspective).



Below is the default kiosk screen ready for an employee to key his/her ID into that blank field. This is a new/empty record being displayed from HR_Punches

1 Primary Kiosk Screen.png


After ID# keyed, name is displayed for confirmation. Also, buttons, and portal appears. This employee doesn't have a clock in that is missing a clock out, so the portal is not showing anything (and that is correct by my design). By entering their ID, their name is displayed from HR_Employee because those tables are linked in a one-to-many relationship.

2 Name Confirmation after keying ID#.png


After clicking the Clock In Button, the Employee gets to see the timestamp for the clock-in punch. The clock-in button runs a script that looks to see if the FIRST portal fields of punch_in and punch_out are empty for the active row in the portal and if they are both empty, it inserts the current timestamp into the punch_in field and the employee it in the portal. The portal always displays row 1. If either punch_in or punch_out is NOT empty, then the script activates the LAST row of the portal (which is a/the new record) and inserts the timestamp on that row, then commits the record/refreshes the portal so that the employee can see the timestamp in row 1.

3 After Seeing Name and Clicking Clock In.png


When the employee clicks the Done button on the previous image, the button creates a new record in HR_Punches and the employee sees the layout as it appears below: ready for the next employee to enter his/her ID#.

4 After Clicking Done.png



When the employee returns to clock out, after his/her ID# is entered and he/she sees his/her name and clicks the Clock Out button, he/she sees this.

The Clock-Out button runs a script and looks at the most recently created record in HR_Punches. If the punch_out field is empty, then it insterts the current timestamp in the punch_out field on the newest record. If the punch_out field in the newest record in HR_Punches is NOT empty, then the script sets the field to punch_out in the last record of the portal, thus creating a new record. The latter happens if an employee fails to clock in. See their clock out time next to a blank clock in time lets them know they missed a punch and they need to notify their supervisor.

He/she clicks done. Then the kiosk looks as it does immediately above.

5 After Clock Out.png


For the portal to work correctly in displaying that timestamp information I want the employee to see, it is sorted descending by creation timestamp that is auto entered when the record is created.



In the statements above as I was referring to the images, when I mention the most recently created record, I am referring to the most recently created record in HR_Punches of the records that match the employee ID#.


Furthermore, I know that I need to add labels, and make things appear and disappear, but I've stopped with the design prettiness because of my concerns about functionality.


It seems that a popover button or new window or something could be opened by a script triggered by a button. They employee could enter his/her ID# and lookup could be used to display his/her name for confirmation as well as display the current timestamp and then when that information is correct and confirmed by the employee, it could be inserted into a new record at that time, instead of the new record being created prior.


It always helps me when I see screenshots on questions/discussions, so I took the time to make a few. I hope it prompts you to help me out a bit.