8 Replies Latest reply on Jul 14, 2015 12:09 PM by jdevans

    Stumped on design/calculation problem, need help

    jdevans

      Title

      Stumped on design/calculation problem, need help

      Post

      I have a solution that collects time-charges for a timekeeping system. The data-entry part is working well. There is a table that collects all the individual time charges against an account by an employee. It is just called "Hours." Each record contains date created, account created, hours, account charged, employee id.

      Now, I'm tasked with creating reports based on this data. One aspect of this is to calculate how Leave hours (vacation, sick, observed holidays, bereavement, etc) are divided amongst the accounts actually worked on.

      For example, employee works 16 hours (2 days) on account1, and 16 hours (2 days) on account2. Then on one day in the same week she takes a paid day off (Leave) for 8 hrs. So now the system must divide her 8 hour vacation 50/50 between the two accounts she worked on before she took off a day, based on the fact that 50% of her actual work time went towards each account.

      I am puzzled as to where this calculation field must go. Or if more fields are necessary to set it up and report it. Eventually I need to be able to report each employee's total time worked for a month, what percentage of their time was spent on each project/account, and finally an aggregate of all employees and how all of their time was split amongst the different accounts. Not sure if it should be global, a variable in a script, a summary, or what exactly.

      Right now I established a total_time summary field which is a total of hours worked. hours_worked is a number field used by the employee for them to enter their own hours worked against a single account, on an individual day, the basis for the records in Hours.

      And I have a pct_of_total_hours which is a summary field set to fraction of total of hours-worked.

        • 1. Re: Stumped on design/calculation problem, need help
          philmodjunk

          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.

          • 2. Re: Stumped on design/calculation problem, need help
            RickWhitelaw

            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.

            • 3. Re: Stumped on design/calculation problem, need help
              philmodjunk

              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...

              • 4. Re: Stumped on design/calculation problem, need help
                jdevans

                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.

                • 5. Re: Stumped on design/calculation problem, need help
                  jdevans

                  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.

                  • 6. Re: Stumped on design/calculation problem, need help
                    philmodjunk

                    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.

                    • 7. Re: Stumped on design/calculation problem, need help
                      jdevans

                      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.

                      Thanks Phil

                      • 8. Re: Stumped on design/calculation problem, need help
                        jdevans

                        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.