Historical data in a Portal

I have a sick leave solution which calculates sick leave taken and sick leave due.
I have these calculations
 Sick Leave cycle:Div ( Div( Get ( CurrentDate ) - S_ EMPLOYMENT  PERIOD::Employment Start Date ; 365 ) ; 3 )+1.........(Unstored)
Cycle Start Date:Let ( [ Cycle = Div ( Div( Get ( CurrentDate ) - S_ EMPLOYMENT  PERIOD::Employment Start Date ; 365 ) ; 3 )]; S_ EMPLOYMENT  PERIOD::Employment Start Date + ( 365 * ( Cycle * 3 ) )
The problem is that the cycle number changes as the date moves forward into the next cycle.
I want to keep historical data and have the cycle number change as the employee moves into the next cycles but also have the previous cycles on record.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

North2AlaskaSenior EngineerCommented:
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.
KrisraychrisAuthor Commented:
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
North2AlaskaSenior EngineerCommented:
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.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

North2AlaskaSenior EngineerCommented:
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.
KrisraychrisAuthor Commented:
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.
North2AlaskaSenior EngineerCommented:
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...
KrisraychrisAuthor Commented:
Thanks and no problem at all.
North2AlaskaSenior EngineerCommented:
OK, I think I have a solution that is pretty close to what you are looking for.  As you know, this is a complex problem that will require some complex answers. So, some things to be aware of / explore are:

1) Custom function to calculate the number of sick days of a given entry (you will enter start and end dates).  It takes into account work days and holidays in the time period.
2) Scripts to do two things set the SickEndDate for data entry purposes and to select the max cycle when you select a record.
3) Auto entry of the employment start date when entering a SickDay record

There needs to be some error checking done if you want someone, that does not understand the solution, to do data entry.  Like, what happens if the sick day is not within the employment range?

Also, the ui_Cycle popup where you select what cycle you want to see, is a function of the cycles in the SickDays table.  So if there is no sick day taken in a cycle, there will be no option to select it. So,  cycle 1 and 3 might be the only values you can select.  If you want to see all cycles, you will need to do something with the Value List used to generate the selection.

One big problem I haven't address is what happens if the sick dates cross over a cycle, i.e. start date is in cycle 1 and end date in cycle 2.

I haven't created all the columns you requested.  I'm not sure you will still need them with this solution or are a simple calculation based on other values.
I'm sure there will be other things as you go, but this is a good start.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KrisraychrisAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.