AnsweredAssumed Answered

Counting Sequential Repeats

Question asked by TheCount on Aug 26, 2014
Latest reply on Aug 26, 2014 by TheCount


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!