That's a bit of a challenge. But first we have to nail down the precise rules that would be used via paper and pencil methods to figure out how to "charge" paid time off. (I used to work for an aerospace company and while we had to charge all our working time against specific project account numbers, we never charged time off against them so this seems a bit odd here.)
Over what time interval do you have to total up time worked in each account "charged" by that employee? Last two weeks? Last N days? Preceding month? all hours worked since the last time the employee took time off? or ???
What I am thinking in terms of is a script that creates additional records in the hours table charged against the appropriate accounts but tagged in an additional field or two as 'leave' and "type of leave" so that you can compute appropriate subtotals against each account ID.
It seems odd to me that paid leave would be charged, whatever the percentage, to what I assume are client accounts. If I were the client I'd certainly object to this. Should not there then be an account that is actually the employer? My 2 cents. Not my area of expertise.
Of course as the "database geek" we don't make policy (or aren't supposed to anyway) we just get stuck with how to make the electronic tools work in support of whatever policy is established by the powers that be.
(Been trying to help kick one project back to the powers that be for policy decisions over the last few months so we can determine if we can shrink the "scope" down to a more easily implemented size...
First of all on the policy... the contract is spelled out this way. The client knows and agrees to this scenario.
Second, the calculations are run against a month of data, both for the individual employee, and for all the employees combined. Whatever happens inside a month doesn't get averaged into a quarter, so if the employee's leave gets computed against the accounts he worked in April, it won't affect the way it gets computed for May, even though those months are in the same fiscal quarter.
On our end, we work some accounts that get charged "directly" meaning, the account has no corresponding "leave" account. In those cases, the overhead gets added to actual hours worked. Otherwise, the project has several sub-accounts, one of which is a leave account.
So in this scenario, there must be a decision made by the system before the leave can be applied correctly.
In a given week, if the employee worked 8 hours on a "directly charged" account (that has no corresponding leave account), and then worked 24 hours on another account that does have a corresponding leave account, and then finally taking a day ( 8hrs) of paid leave - they have:
25% (8 of 32 worked) of their work time going to the "direct" account, and
75% (24 of 32 worked) going to the "with leave" account.
So then the 8 hours leave gets split 75/25 between the two accounts.
Therefore, for the "with leave" account the 75% of 8 leave hours results in 6 hrs, which go to that accounts corresponding leave sub-account.
The other 25% (2 hours) of remaining leave get ADDED to the 8 hours they worked directly, so that account gets billed 10 hours.
Again this is all agreed to amongst the stakeholders, and has been a standard practice in our industry for many years.
When I set up a field in the Project table which is a radiobutton field for "Has Leave?" Y/N. There is also in the Accounts table (child to Projects) a radiobutton field for "Is Leave?" Y/N.
I was hoping to use that in the logic for the script and/or calculations. If Has Leave=N in Project then its subaccount(s) get charged leave directly, as described above. If Has Leave? =Y then it goes to check which of its subaccounts Is Leave=Y, and thus makes the other type of leave distribution. It would need to summarize the employee's hours charged to the project (combining the project sub-accounts) over a month, figure the percentage distribution for the month, and then dole out any leave that may be present according to the rules.
We have an old system that handles this, and is all done in Excel. Each employee has their own excel file for each month. We are trying to get away from this, since it doesn't have cumulative data for a fiscal year.
One way this system designates work hours from the "billed" hours is by calling the work hours "Work" and the resulting calculated hours (after leave is calculated) "Effort." Salaried employees have to view and approve an "Effort Report" at the close of every quarter. This FileMaker system should reflect what is in that Effort Report.
This is a very complex set up. It can be handled in FileMaker, but you may need to hire a consultant that can work this out for you in your actual file.
Off hand, it sounds like you need a relationship that will match to hours worked by Month/year and EmployeeID. This then gives you the total hours worked by an employee for a given month. Add one more match field in a different relationship, the account ID and you get the monthly total for a specific account.
Here's a calculation that may prove useful for the "month/year" part of the matching process:
Datefield - Day ( DateFIeld ) + 1
This takes any date in date field and computes the date for the first day of the same month and year. Since this is still a date, you can also use it for sorting to group records by month in reports as well as a match field value to match to all records of a given month and year.
This is pretty complex the more I look at it. I thought it would be simpler, since it is already working in the Excel world. So, at your suggestion for a consultant, I have reached out to a local developer to see what he thinks. Waiting to get an answer on a time estimate.
Still working on this...the consultant seems to think it has to be done via a routine that runs at night, because he thinks it will take significant time to run it. I fail to see where it is that complicated, and am still plugging away at an answer myself.