3 Replies Latest reply on Dec 20, 2016 4:29 PM by user910

    Can a repeating field be used in this relationship?

    user910

      I'm trying to display 7 days of time clock summary data for each employee without creating 7 sets of fields and relationships. I can get the repeating values of dates by using this calculation field:

       

      Extend ( Start Date ) + Get ( CalculationRepetitionNumber ) - 1

       

      but I haven't found a way to use those dates in a relationship (matching dates and Employee IDs with time log records) that gets 7 calculation results, as in total hours worked for a given employee on a given day.

       

      I know this may take a more detailed discussion, but does anyone know whether repeating fields can be used in this way at all?

       

      I used to think repeating fields were just an ugly holdover from FileMaker's early years and should be avoided in relational design. Then it looked like maybe they provide the power of vector operations and are ideal for calendar-like or spreadsheet-like reports. I've spent hours hacking my code and searching the web for clarification.

       

      Which is it?

        • 1. Re: Can a repeating field be used in this relationship?
          erolst

          user910 wrote:

           

          I used to think repeating fields were just an ugly holdover from FileMaker's early years and should be avoided in relational design. Then it looked like maybe they provide the power of vector operations and are ideal for calendar-like or spreadsheet-like reports. I've spent hours hacking my code and searching the web for clarification.

           

          Which is it?

          They are great for cell-based operations, crosstab-reports and as poor man's recursion, as well as for storing graphics etc., but one shouldn't use them instead of a proper relational design.

           

          What you're looking for is a multiline-key - and that can not be implemented with a repeating field. Use a regular text field and a script, a recursive Custom Function and a calculation field, or just a calculation field and a recursive 'hack' to create a list dynamically.

           

          Also don't forget that you could use a single relationship with >= and <= operators, then use filtered portals to break the matching set by day.

          1 of 1 people found this helpful
          • 2. Re: Can a repeating field be used in this relationship?
            philmodjunk

            What you can use is one relationship and 7 single row portals that filter for specific dates.

             

            With a bit of creativity, this might be done with a sorted relationship and no portal filters if you create a record for every day for a given employee and leave fields blank for days that they didn't work.

            1 of 1 people found this helpful
            • 3. Re: Can a repeating field be used in this relationship?
              user910

              Thanks for replying, erolst and philmodjunk. I ended up using 7 non-repeating date fields and 7 relationships to access the log records for each employee-day. Then I was able to get my multi-day data into a repeating field by using a Choose function; e.g.,

               

              Choose ( Get ( CalculationRepetitionNumber ) - 1 ; Logs1::Total Paid Hours; Logs2::Total Paid Hours; Logs3::Total Paid Hours; Logs4::Total Paid Hours; Logs5::Total Paid Hours; Logs6::Total Paid Hours; Logs7::Total Paid Hours )

               

              where Total Paid Hours is a calculation based on a self-join in the time-logs table.

               

              This approach gives me the elegance that repeating fields can provide: simple calculations using Sum and simple summaries using Total for weekly and daily totals, respectively.