4 Replies Latest reply on Aug 4, 2014 2:57 PM by RossEwing

    Summarize Hours in portal with repeating calculation

    RossEwing

      Title

      Summarize Hours in portal with repeating calculation

      Post

           Hello all,

           This if my first post. I am very new to File Maker Pro 13, and I am having trouble with a solution i am working on. here are the Basics:

           This is a Schedule database for my department to use to schedule and track the hours of our part time employees, replacing an Excel spreadsheet I had made for the same purpose. I am switching to File Maker to add integration with one of our other solutions. The goal is to have this solution provide a Live and printable Calendar for employees to see when they work. my problem comes in when i try to replicate a must have feature of the Excel file; a total of the weeks scheduled hours.

           my latest attempt at this is I have created a repeating Calculation field in my Employee table that looks like this:

           Let (
            [ LowerDate =
            Case(
             Get ( CalculationRepetitionNumber )  = 1 ; 0  + Extend (  Interface::cDateOfFirstPortal) ;
             Get ( CalculationRepetitionNumber )  = 2 ; 7  + Extend (  Interface::cDateOfFirstPortal) ;
             Get ( CalculationRepetitionNumber )  = 3 ; 14+ Extend (  Interface::cDateOfFirstPortal);
             Get ( CalculationRepetitionNumber )  = 4 ; 21+ Extend (  Interface::cDateOfFirstPortal);
             Get ( CalculationRepetitionNumber )  = 5 ; 28+ Extend (  Interface::cDateOfFirstPortal)) ;

            UpperDate=

            Case(
             Get ( CalculationRepetitionNumber )  = 1 ; 6   + Extend (  Interface::cDateOfFirstPortal) ;
             Get ( CalculationRepetitionNumber )  = 2 ; 13 + Extend (  Interface::cDateOfFirstPortal) ;
             Get ( CalculationRepetitionNumber )  = 3 ; 20 + Extend (  Interface::cDateOfFirstPortal);
             Get ( CalculationRepetitionNumber )  = 4 ; 27 + Extend (  Interface::cDateOfFirstPortal);
             Get ( CalculationRepetitionNumber )  = 5 ; 34 + Extend (  Interface::cDateOfFirstPortal)) ];

            If (

             Shifts_To_Interface::Shift_Date_Start   ≥   LowerDate

             and

             Shifts_To_Interface::Shift_Date_Start   ≤   UpperDate;
             Sum (Shifts_To_Interface::Total_Shift_Hours; 0 )))

            

           The result of this calculation is displayed in a portal on the Calendar view at the end of each week, however in the current state of my calculation I only get the hours added from the last day of each week on the first week only.

           my question is; is there something that I am missing when I do a repeating Calculation, or is there a better way to show this information without a portal?

      Solution_Example_jpeg.jpg