2 Replies Latest reply on Aug 26, 2014 11:31 AM by TheCount

    Counting Sequential Repeats



      Counting Sequential Repeats



           I'm new to FileMaker and could use some help with counting sequential repeats.  Here's what I mean: I have a table called "Daily Work" which has the following fields:  Date, Client ID, Employee ID (note that there is a many-to-many relationship between Client ID, and Employee ID)

           I need to generate a report for each employee which shows how many days in a row the employee worked for a particular client before there was a break in service for that client.  Example:


      Client ID    Last Date in Sequence      Number of Times in a Row before break

           ClientA,    1/5/2013,    4 [e.g., Joe worked for ClientA on 1/2/2013, 1/3, 1/4, and 1/5 but not 1/6]

           ClientA,     1/14/2013,   5 [e.g., Joe took a break from ClientA then did work again for ClientA on 1/10, 1/11, 1/12,1/13, and 1/14 but not 1/15]

           ClientB, 1/7/2013, 2 [e.g., Joe worked for ClientB on 1/6 and 1/7 but not on 1/8]

           ClientB, 1/29/2013, 1   [e.g., Joe took a break from ClientB then did work again for ClientB on 1/29 but didn't work for ClientB on 1/30]

           I also need to do the same thing but this time by week.  In otherwords, so long as if the employee did any work for the client during any calendar week, then the report would count that calendar week as a "week worked" for that Client.  Then, I need to list the number of sequential weekly repeats [ending at the week where the employee did nothing for that client].

           Can anyone give me an idea how do I do this? Thanks!


        • 1. Re: Counting Sequential Repeats

               (note that there is a many-to-many relationship between Client ID, and Employee ID)

               Sorry, but that makes no sense. Client and Employee ID's should be completely different values. One should uniquely identify clients and one should uniquely identify employees. EmployeeID 1 is NOT Client 1... Do you mean that you have a client table and an employee table linked to Daily Work? That would set up a typical many to many relationship with Daily Work serving as the Join table between them.

               I've bee mulling over multiple options that come to mind for generating this report. Unfortunately, none are particularly simple to describe by me or set up by you.

               The method that looks "least bad" to me would be to set up a related table, "report_groups" with an auto-entered serial number field. Each time an employee logs a new record in Daily Work, a script could use a self join relationship to a different occurrence of DailyWork to check the ClientID and Date of the previous Daily Work record. If it's a different client or date that is not today's date -1, it creates a new record in report_groups and links it the current record in Daily_Work by the ID number in the new report_groups record. If there isn't a break in service, the same script copies the report Group ID from the preceding record by this same employee. This then assigns a unique ID to all records that represent continuous service to the same client. You can then Sort your records by this ID field to group your Daily Work Records by ID and a summary field used with a sub summary layout part can now count the number of records in each group to show the number of days of continuous service.

               Still think that there ought to be a simpler possible approach but this is the best that comes to mind at this moment...

          • 2. Re: Counting Sequential Repeats

                 Thanks for the idea, and I apologize for my misuse of the phrase "many-to-many".  You are correct that I have a client table and an employee table linked to Daily Work.

                 I am thinking about your suggestion, but the one tricky thing is that it necessitates a script after a new Daily Work entry.  The problem is that I am going over thousands of historical records so there really won't be a chance for a script to run after each new entry.  Is there a way around that?