Historical data in a Portal

I think I have a solution, but it gets a little tricky.  I've modified the example we played with before to include the following:

1) create a new field in SickDays to hold the EmploymentDate.  We have to have this field local so we can index the calculation below.  To populate this field, I used a auto entry calculation to copy the date from the employee record.
2) create a new field, Cycle, in SickDays.  This calculates the cycle this SickDay record falls into.  We need this field to be indexable, because we will use it for the join.
3) create a value list to contain all of the current employees cycles from the SickDays table.
4) create a new global field in Employee to capture the selected cycle.  This is a user interface column just for this purpose.
5) create new field to calculate the number of sick days remaining when you select a cycle.
6) create a relationship from Employee to SickDays based on the ID and between the new global field and the new Cycle field.

Now we tie it all together on the Employee layout.  Give it a look and see if this will work for you.
SickDaysCalc.fp7

I am battling a bit with combining your solution to what I need.
I have attached my version .I am trying to get the cycle numbers to store the sick leave data in each cycle.
There are some fundemantal errors as well and would aprrecaite it if you woudl have a look in the hopes it gives you a clearer picture of what I am trying to do.
Thanks. SickLeaveKR.fp7 SickLeaveKR.fp7

First observations.  In your ESickDays table you have a begin and end date.  I would not choose to this method.  It would be significantly more difficult to manage the number of days.  If you have a record entered in the table, one for each sick day taken then other problems (like trying to figure out weekends and holidays) might be a lot easier.  If you need to build a user interface where the user enters a begin and end date, then I would do something via a script to create the individual records.

Describe for me what your design goal is for the S_MAIN Table.

Looking at the field definitions in the EEmployee table, you have several columns that may or may not be needed.  Could you give me an overall requirements for the different values needed?

I will be glad to work with you as we build your solution, I will just need the bigger picture.  Then we can break it down to specific questions.

So, I'm thinking more about the different levels of information you might need.  I'm thinking we can drive everything off the Cycle.  The user is presented with a method of selecting which cycle and all calculations are driven from that selection.  So, what fields do we need?

Days taken (for a selected Cycle)
Days remaining (for a selected Cycle)
Days over (if too many days taken)
Total days taken (since employmentDate)

The selected cycle would always be set to the last cycle as you navigate from record to record so that the current cycle is the default.

Just some thoughts.

Yes I agree .I think the cycle number is a key factor here but the records..This is soemwhat like an attendance register so that if there were ever any dispute as to how leave was calculated we need to have the facts avialbel for scrutiny and beable to go back in time by Cycle number.
The way in which you set up the first solution and my subsequent modification seemed to work.The only problem was the cycle number changed as the time moved forward.
What I need is a method of recording staff absenteeism within a 3 year cycle.Each 3 year cycle allows 30 days then all leave is cancelled.If over it does nto carry over to the new cycle.If the 30 days is exceeded before the 3 year cycle is completed then any leave which they take over and above will be unpaid .
So in essence the user needs to record all sick days taken..Start Date and EndDate.I need the start date and end date as employees may take a months leave all at once, within the cycle number

If my layout could look like this I would be happy:
EmployeeId..name etc
:Date Start (Sick day on) User
:Date End  (Return to work On ) User
:Days Taken in the above period ( basically DateEnd-DateStart)
:Daye Remaining in the 30days Cycle over Three Years (30 days - Total Days Taken  since employment within the three year cycle number 1 2 3  etc)
:Days Exceeding the 30 day cycle within the 3 year cycle.(Total Days Taken  since employment within the three year cycle)- 30 days
:Total Days Taken since employemnt within the three year cycle number 1 2 3 etc)
:Cycle Number (Cycle 1=Year 1,,Year 2,,,Year 3 ..Stop calculating but retain all records in the cycle add 30 dyas to leave due and start recirding  Cycle 2( Year 1..Year 2...Year 3 Stop Calculatuiing and add 30 days and so on to Chycle 3,4,5, etc)
:Cycle start date
S_Main is the table which links all the reklatted files to time and attendance..eg Late arrivals..Fixed term Contracts...Period Of Employment..which has fields like Employment start Date..Number of Days:Months:Years Employedetc etc.They are all related by staffID.I have removed the other fields that were not relevant in this table.
If this is not clear I can set it up on excel or soemthing like that.Many Thanks for your assistance so far.

Sorry Krisraychris.  I've been out of pocket this week with the final push on a project at work.  I won't be able to address this until the weekend.  I do have a couple of ideas, though...

Thanks and no problem at all.
Chris

Thank you .It took soem time to adapt .It is working well !!

This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.