1 2 Previous Next 21 Replies Latest reply on Jul 23, 2016 6:46 PM by keywords

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

    clayhendrix

      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.

        • 1. Re: Is self-join the correct solution for showing related records from same table?
          Mike_Mitchell

          Have you considered using global fields (in or out of a popover)? Have the user populate the globals, validate their contents via script or relationship, and use a script to insert the new record into the table.

          • 2. Re: Is self-join the correct solution for showing related records from same table?
            clayhendrix

            Yes, I have. Yes, I want to. I do not know how to create a new record in HR_Punches with the values from the global fields. I have had problems in the past with global fields in scripts.

             

            What do you mean by validate their contents via relationship? Do you mean allow users to see the Employee Name associated with the ID they enter so they know the entered the correct ID?

             

            In what table would I put the global fields? HR_Punches?

             

            Thanks, Mike!

            • 3. Re: Is self-join the correct solution for showing related records from same table?
              Mike_Mitchell

              1) Use a relationship and the “magic key” approach. Create a relationship from a global field in the current context (whatever that is) to the unique ID of the HR_Punches table (any TO). Set Field script steps targeting that TO will create a new record as long as the global field pointing to the ID is empty. After you’re done, null out the global ID field.

               

              2) Yes, allow them to see the related record. And perform whatever other checks you want to perform.

               

              3) The global fields can go in any table, except for the one that points to the ID. That one has to be in the current context to create a valid relationship. (See 1.)

              • 4. Re: Is self-join the correct solution for showing related records from same table?
                clayhendrix

                Mike,

                I hesitate to abuse your willingness to help, but I am searching online for magic key. I believe it is something that I am aware of, just not by that name.

                 

                Create a relationship from a global field in the current context (whatever that is) to the unique ID of the HR_Punches table (any TO).

                What is the definition of "current context?" (I have two TOs for HR_Punches and one layout based on HR_Punches. I'm guessing that information is what I need to determine the current context.)

                 

                3) The global fields can go in any table, except for the one that points to the ID. That one has to be in the current context to create a valid relationship. (See 1.)

                I'm assuming when I can understand your number 1 and the definition of current context, then I'll understand number 3 too.

                • 5. Re: Is self-join the correct solution for showing related records from same table?
                  Mike_Mitchell

                  Current context is the TO on which the current layout is based. Where you are on the Relationships Graph.

                   

                  The global field to the PK does not have to be on the layout. Just use Set Field to clear it when you're done.

                  1 of 1 people found this helpful
                  • 6. Re: Is self-join the correct solution for showing related records from same table?
                    clayhendrix

                    Below is the portion of the relationships graph. The layout is based upon HR_Punches.

                    Screen Shot 2016-07-03 at 7.17.12 AM.png

                     

                    Then I just added another table with the global field, shown below. I think next, that I need to add additional global Screen Shot 2016-07-03 at 7.30.23 AM.pngfields to some table. Those global fields will receive all information is needed for the record in HR_Punches.

                    I'm thinking I'll add 'g_id_personnel, 'g_punch_in, and 'g_punch_out. Then figure out how to script the transfer of data from those global fields to the fields in the record just created via magic key and clear 'g_id_HR_Punches.

                    • 7. Re: Is self-join the correct solution for showing related records from same table?
                      keywords

                      I'm not convinced globals is the way to go here. The issue, it seems to me, is that at any given time you will have many incomplete Punch records—employees who are currently at work, and hence have clocked on but not yet clocked off. If you use globals you have the issue of connecting to an incomplete record if there already is one. That could still be done, but consider the following alternative approach:

                       

                      Create two relationships between Employee and HR_Punch, one for incomplete cards and one for completed ones. The method could then be:

                      1.     Employee enters name or ID

                      2.     Is then taken to an employee layout with only his/her own record in view

                      3.     On this layout will be a portal showing just one record in the incomplete punchcard relationship—there should only ever be either one (clocked on but not clocked off) or none (new day)

                      4.     If there is an incomplete record showing, employee completes it, at which point the record will meet the relationship criteria for a complete record, and will disappear from the portal (you could have another portal showing completed records, to improve user experience, but only allow those records to be viewed, not edited)

                      5.      If there is no incomplete record then there will be a button to create one, which ideally you would hide otherwise—in other words, employees would see on this layout either and incomplete record awaiting completion, or a button to create a new punchcard record, but not both.

                      • 8. Re: Is self-join the correct solution for showing related records from same table?
                        clayhendrix

                        Yes, keywords, it is kind of working as you describe, as you can see from the screenshots. It's just the empty records that are getting created. I understand your number list, but I do not understand how to:

                        Create two relationships between Employee and HR_Punch, one for incomplete cards and one for completed ones.

                         

                        There is currently one relationship between those tables for all punches (records). How do I add a relationship that will filter complete and incomplete punch records?

                        • 9. Re: Is self-join the correct solution for showing related records from same table?
                          Mike_Mitchell

                          Yes, that's the issue the global strategy solves: Dead records.

                           

                          I'm not where I can look at your screen shots at the moment. Will get back when I can.

                          1 of 1 people found this helpful
                          • 10. Re: Is self-join the correct solution for showing related records from same table?
                            Mike_Mitchell

                            keywords has a good point about the incomplete records. We have a system similar to this, and you have to do some housekeeping to manage the possibility of someone not clocking out (for whatever reason). In the case of the incomplete records here, I would simply add a criterion to your self-join that looks for a complete date / time. You can do this by inserting either another calculation field in the table that always evaluates to something like 1/1/0001 and look for records less than that (which will be only empty records) or just add another global that you set to that value.

                             

                            If there's a record that matches those criteria, then you capture that ID in your global field and modify that record. If not, then create a new one. It's worth noting that you still need to look for any incomplete records, since you can suffer a logic failure that causes multiple records to be there. That can be done with a maintenance script at night.

                             

                            To avoid all that stuff, you can change your fundamental setup to have only one timestamp on each record, and just mark them as in or out. That way, every time a user interacts, you always create a record and avoid the housekeeping issue. (You may have other housekeeping issues with that setup, though.)

                             

                            Anyway, there are some options out there.

                            • 11. Re: Is self-join the correct solution for showing related records from same table?
                              siplus

                              You have employees, and you have events.

                               

                              An event is an employee entering or exiting, so it has the employee FK and an EventType: Enter or Exit.

                               

                              It obviously has a timestamp too - the record creation TS.

                               

                              Employee comes and inputs her/his ID. A portal shows all events sorted by timestamp, a second portal shows the same thing, but it's a one-line portal sorted by timestamp descending so it will show the last movement. something like

                               

                              7-3-2016      ENTER     07:49 AM

                               

                              the user has the opportunity to create a new event record (which will be an exit record) or delete the last record (which is an enter record), create a new record (will be enter record) then create an exit record.

                              • 12. Re: Is self-join the correct solution for showing related records from same table?
                                clayhendrix

                                Exactly. I have worked to keep an enter and an exit in one record. I helps a lot in the calculations for time sheets. Interestingly enough, getting scripts to recognize whether there is a missing piece of the record (either enter or exit) was not the hardest thing.

                                 

                                The hardest thing and what I am struggling with are generally summed up in the following list:

                                1. What layout based upon what table will allow the employee to enter his/her id and see his/her name before clicking Clock In or Clock Out
                                2. Creating a self-join so there could be a portal with data from the same table as the layout
                                3. Getting the employee's id and name off of the screen so the next employee would come to a screen ready to enter his/her id

                                 

                                Generally because of #3, there are blank records in the table because when an employee clicks Done, it is just creating a new record because the timestamp data is committed when the employee click Clock In or Clock Out.

                                 

                                It's one of those projects that is simple so far as my understanding of the data and how the data needs to be processed once collected, but has proven to be a pain.

                                 

                                I thought about basing the time clock layout on the Employees table, but it seemed that I shouldn't have all of the employees having access to the table.

                                 

                                Hey, I kind of vented a little there. Thanks so much for your time and help! I'm looking forward to what you might respond with.

                                • 13. Re: Is self-join the correct solution for showing related records from same table?
                                  siplus

                                  IMHO this is where we diverge.

                                   

                                  An event is an event.

                                   

                                  You tried to squeeze 2 events into the same record, hurting some normal form rule, and you got problems.

                                   

                                  Try to work on single records with a single event on them.

                                  • 14. Re: Is self-join the correct solution for showing related records from same table?
                                    clayhendrix

                                    Thanks for the advise. I appreciate your input, as I always do. I'll work to see how hard it will be to calculate the amount of time worked if the timestamps are in two separate records.

                                    1 2 Previous Next