2 Replies Latest reply on Jun 6, 2013 1:34 AM by cgallardo

    When / how  to use a self join?



      I am playing around with a self join table. Can you please have a look at my table design and advise if I have done it properly or if you have suggestions?


      This is for people sigining in and out of a building. Then if there is a fire or a fire drill, an Ipad with filemaker go is used as an attendance roll to ensure all occupants have left the building.


      The database is hosted in a remote server, data is accessed via an Ipad with 3G (so if there is a disaster data can still be accessed).


      Signing in and out will be done by another ipad or desktop (have not figured this out yet) secured to a wall.


      I used a self join table so that I could display the data in portal. I have never used a self join before.


      I created the ROLL CALL DAYS table just so I could have extra data on the layout that runs the attendance roll. I do not neet to save historical data.


      My concern is that I may not be using the self join table properly. As far as the operation goes it does what is expected, but I am concerned that later if they want data to be saved it will make it difficult to change.


      Should I break it in to multipletables instead? i.e. TODAY (holds the day's attendance roll), PEOPLE (holds people names and visitor/staff flag). Where would I save the time in and out, and InOut fields?


      Database Design.JPG

        • 1. Re: When / how  to use a self join?

          I am not entirely sure what your TODAY 2 is for...


          I really don't think you need the ROLL Call Days either.


          If you have the TODAY table and change it's name to ROLLCALL (just add the date fields in it) or similar.... you can have direct relationships with the PEOPLE to be able to add their clock in and out events.... in the ROLLCALL table.


          A self-join relationship would be where you have say the field DateIn related to DateIn in the same table (show me all those records with the same Date in as this record)... or perhaps G_Date related to DateIn (show me all those records where the date entered in the global field is the same as the DateIn)... or perhaps DateIn + PEOPLE_PK related to DateOut + PEOPLE_PK (show me the records where this same person logged out on the same date as the login record I am looking at)...


          There are lots of variations... but essentially a self-join is just matching the data in the same table.


          If you have a DateIn and a DateOut field and use the G_Date global scenario to relate to the data in ROLLCALL... you can first create a calculation which combines the DateIn and the DateOut into one field for matching. eg. =DateIn & "¶" & DateOut. Entering Todays date in G_Date would show (in a portal) all log IN and OUT records. You could have another field which calculates all the dates in and out for the last 7 days... it THIS WEEK  etc.




          • 2. Re: When / how  to use a self join?

            Hi Lyndsay,

            TODAY2 is how FIlemaker displays a Self Join.

            I am using ROLL CALL almost like dummy table so that I can display some other layouts.

            Thanks for your help, I ll make another version with your suggestions and see how it goes.