Historical data in a Portal

Posted on 2011-02-24
Medium Priority
Last Modified: 2012-05-11
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.
Question by:Krisraychris
  • 5
  • 4
LVL 12

Expert Comment

ID: 34978889
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.

Author Comment

ID: 35006801
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
LVL 12

Expert Comment

ID: 35007059
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 12

Expert Comment

ID: 35007215
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.

Author Comment

ID: 35009234
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.
LVL 12

Expert Comment

ID: 35028195
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...

Author Comment

ID: 35028524
Thanks and no problem at all.
LVL 12

Accepted Solution

North2Alaska earned 2000 total points
ID: 35057109
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.


Author Comment

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

Expert Comment

ID: 35410118
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.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question