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.
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.
Lets say following is your table structure
table 1: Employee
TotalLeaves (This is your table 1 Field1 = SUM(Table2::Field2) )
table 2: Leaves
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.
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.
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.