5 Replies Latest reply on Aug 12, 2013 5:38 PM by TaukoririMeita

    Total Portal Row calculation, but not the whole row from top to bottom....only from where it is...

    TaukoririMeita

      Title

      Total Portal Row calculation, but not the whole row from top to bottom....only from where it is needed.

      Post


           So I've uploaded an image so you can better understand what I'm trying to accomplish. Two related Tables, Table1 has Field1 and Table2 has Field2 in a portal row.

      Field1 is = Leave Days taken this year

      Field2 is = Total No of Days taken

           I want Field1 to show the Total of Field2 easy as SUM ( Table2::Field2 ) which only works if you want the total of the row from top to bottom. But I only want it to total up from where I want it. For example check this year and refer to the portal then calculate the total of Field2 for this year only. And since Field2 will continue to change, I want Field1 to auto update with it.

           I know it must be difficult to understand what I'm trying to say here but maybe if you take a look at the picture below you'll get my point.

           Thanks.

      sample.png

        • 1. Re: Total Portal Row calculation, but not the whole row from top to bottom....only from where it is...
          philmodjunk

               This actually has very little to do with the fact that you have a portal. The key detail is that you have a table of related records and you want a total computed selectively from a subset of those related records.

               Here's a thread that describes two commonly used methods for getting such a sub total: Sum_Calculation based on condition

               ExecuteSQL() can also be used to selectively sum records.

          • 2. Re: Total Portal Row calculation, but not the whole row from top to bottom....only from where it is...
            TaukoririMeita

                 I'm a beginner and would really need a detailed dummiest instruction as possible. Looking at someone else's problem which is identical to mine...mhhh they might be the same problem but I don't see the similarities hehehe I just get more confused. So I guess I still don't know how to solve this.

            • 3. Re: Total Portal Row calculation, but not the whole row from top to bottom....only from where it is...
              shilpas@metasyssoftware.com

                   Lets say following is your table structure

                   table 1: Employee
                   Fields
                   EmployeeID
                   EmployeeName
                   TotalLeaves (This is your table 1 Field1 = SUM(Table2::Field2) )
                    

                   table 2: Leaves
                   Fields
                   LeaveID
                   FK_EmployeeID
                   StartDate
                   EndDate
                   NoOfDays (This is your table 2 Field2)

                   The current database has PK-FK relationship between table 1 & table2. Now to fetch the 'leave days taken this year', use these steps
                   1. In Employee table, create two global date fields gYearStartDate & gYearEndDate
                   2. set values of current year (manually or through script/button click)  in above two global fields, gYearStartDate = 1/1/2013 and gYearEndDate = 12/1/2013

                   3. Now set new relationship between Employee (table1) & Leaves (table 2) tables. Create new table occurrence of Leaves Table - says "CurrentYearLeaves". Set relationship based on fields


                   Employee::EmployeeID = CurrentYearLeaves::FK_EmployeeID AND
                   Employee:: gYearStartDate =< CurrentYearLeaves::StartDate AND //Leaves StartDate greater than equal to current year start date
                   Employee:: gYearEndDate => CurrentYearLeaves::EndDate //Leaves EndDate less than equal to current year end date

                   4. create 'CurrentYearLeaves' calculation field in 'Employee' table with formula SUM(CurrentYearLeaves::NoOFDays).
                   This will give sum of leaves in date range set in global date fields either for current year/last year or six months.

                    

              • 4. Re: Total Portal Row calculation, but not the whole row from top to bottom....only from where it is...
                philmodjunk

                     MetaSys is on the right track but there are some leaves that won't match to the relationship specified when they should. An example would be a leave that starts before gYearStartDate and ends after gYearStartDate. A leave that starts inside the specified interval and ends after the specified interval would also not be counted.

                     Such leaves will represent a special case where only part of the leave should count.

                     A table where a single record is created for each day an employee is on leave with the date for that day would make this method fully accurate for all leaves.

                • 5. Re: Total Portal Row calculation, but not the whole row from top to bottom....only from where it is...
                  TaukoririMeita

                       Thanks alot MetaSys and PhilModJunk for your help. I didn't get to try your methods but I finally figure out a simpler way. I noticed can just add the (Annual Leave Entitlement) with the Last (Leave B/F) and less with Last (Balance) in the Balance row. So I came up with this formula and its working as expected at the moment.

                       Leave Taken this year =

                       Table2::Annual Leave Entitlement + Last (Table2::Leave B/F) - GetNthRecord (Table2::Balance ; Count (Table2::Balance))

                       Thanks alot again.