2 Replies Latest reply on Jun 1, 2009 10:53 AM by IanMac

    Help creating an attendance register - FM10

    IanMac

      Title

      Help creating an attendance register - FM10

      Post

      Hi All,

       

      This is my first post to the forum and can only thank you all for your various submissions, the forum has been a great source of information to me.

       

      I have been using FM9 & FM10 for about a year and have recently been going through the FM training series but am running into a problem with trying to create an attendance register to be used to show number of days worked, sick, holiday & absent. This is then used in a payroll system to calculate pay.

       

      I have created a table called Register which has the following fields;

      Employee (Text)

      Days (Repeating field x30) to capture specific values for each day - Value List : A = Absent, H = Holiday, W = Work & S = Sick. I work off a 30 day month.

      Days Worked (Calculation) - To total number of days worked.

      Days Absent (Calculation) - To total number of days Absent.

      Days Sick (Calculation) - To total number of days sick.

      Days Holiday (Calculation) - To total number of days holiday.

       

      I can't seem to get the calculation fields to show me the individual total number of days for each separate occurence within the repeating field. I have searched through the forum and know that some people suggest repeating fields should not be used. Can anybody help me with the calculation or suggest a different way for me to look at the register please? Thanks in advance.

       

      Ian. 

        • 1. Re: Help creating an attendance register - FM10
          TSGal

          Ian Mac:

           

          Thank you for your post.

           

          A repeating field is one field with many values.  Therefore, trying to find a specific occurrence in a repeating field can be difficult, since all values are in the single field.  This is one of the reasons why other people suggest to not use repeating fields.  However, let's get to the problem on hand.  Perform the following steps:

           

          1. Open the file with the repeating field.

          2. Pull down the File menu and select "Save a Copy As..."

          3. Name the file "Non-Repeat Report".

          4. For "Type", select "clone (no records)".

          5. Click Save. 

          6. Pull down the File menu and select "Open"

          7. Select the newly created file "Non-Repeat Report"

          8. Pull down the File menu and select "Import Records -> File..."

          9. Select the original file.

          10. Make sure the field names line up properly and click Import.

          11. You are now presented with a very important dialog box.  You want to split the repeating values into separate records.  Once selected, click Import.

           

          Now, you have a lot more records.  There will only be one value in each repeating field, but where you had one record with 30 repeating entries, you now have 30 identical records, but each repeating record is in its own record.  At this point, you can now perform a report to find each specific value.

           

          This should give you the results you want.  You can keep this file for future reporting, or you can read up on relating two tables together.  In your case, that would be your Employee table with a possible Attendance table, and have the two tables linked together via an Employee ID field.  I'll leave that for homework.

           

          If you need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Help creating an attendance register - FM10
            IanMac
              

            TS Gal,

             

            Thank you for your reply. I have done as you have suggested and now have one record per employee per day. I can now perform the required calculations which is a great help. I will play around with the layout and relationships and get back to you if I run into any other problems.

             

            Thanks again,

             

            Ian.