1 2 3 Previous Next 30 Replies Latest reply on Nov 8, 2013 1:28 PM by philmodjunk

    Using fuctions to add certain quantities

    EdwardAlvarez

      Title

      Using fuctions to add certain quantities

      Post

           <!--StartFragment-->Hi,
           
           I am trying to simplify our life at our office. The way we do payroll is totally awful! We need a quick solution to this issue. We have a lot of employees and many of those employees work different positions throughout the pay period. Some positions pay $11 some positions pay $8 and some pay $14, $15 or $16 (this is not including over time). By the time our payroll department has to submit payroll to ADP the person in charge of doing payroll needs to submit all these rates by hand. The method we are currently using is very prone to human error. We are trying to avoid both errors and most important simplify our payroll guru’s life.
           
           I feel that the formulas use on FileMaker Pro are very similar to ones used in Excel. I am a little rusty and I cannot remember which function I need to use to reach my goal.
           
           What I am looking to do is to add a function that will only add certain rates. For example if an employee work 10 days out of the pay period totaling 80hrs, where 4 days (8hrs each day) he worked at $11/hr, 4 days he worked at $16/hr (8hrs each day) and 2 days he worked at $8/hr (8hrs each day). I want a function to tell this new TOTAL HOURS at 11, to ONLY add the total hours this employee worked at $11/hr. Then I want to add another TOTAL HOURS at 8, and I want this cell to ONLY add the total hours this employee worked at $8/hr
           
           I have attached a picture so that you can get a better idea on how we are working with payroll.
           
           Can someone please help me!
           
           Regards,
           
           Ed Alvarez 
      <!--EndFragment-->

            

            

           <!--EndFragment-->

      payroll.png

        • 1. Re: Using fuctions to add certain quantities
          philmodjunk

               Before you try setting up a calculation for an employee's pay, you need to design a workable data model.

               Am I correct that you have placed repeating fields on this layout with each column a different repeating field?

               If so, I strongly recommend that you redesign things so that each row in the above image is a different record without any repeating fields. Your calculations and reporting will be much easier to set up.

          • 2. Re: Using fuctions to add certain quantities
            EdwardAlvarez

                 Hi Phil,

                 I am not sure what do you mean by repeating fields. which field are you referring to? they are all it's own record. each date would represent one shift worked. i just wrote office as an explample but that can be change to anything (we use the address of where the employee worked). each one is separate from the otherthe only thing where they come together is at the bottom, on the cell where it says total hours. which is the sum of the total worked hours. 

                 What i am trying to do is have a similar formula like the one that adds the hours but only add certain ones. 

                  

            • 3. Re: Using fuctions to add certain quantities
              philmodjunk

                   If your above screen shot is not from a FIleMaker layout disregard my comments about repeating fields.

                   What I am trying to emphasize is that each row in the table in your screen shot should be an individual record linked via relationship to a specific employee record.

                   The following is just a general outline of the basic approach that you can set up with a relational database such as FileMaker. Many, many implementation details needed to make this work have been left out due to the limits of time and space.

                   The following:

                   Positions----<Employees---<TimeCards>------Payroll

                   Positions::__pkPositionID = Employees::_fkCurrentPositionID

                   Employees::__pkEmployeeID = TImeCards::_fkEmployeeID

                   Payroll::PayPeriodStartDate < TimeCard::Date AND
                   Payroll::PayPeriodEndDate > TimeCard::Date AND
                   Payroll::_fkEmployeeID = TImeCard::_fkEmployeeID

                   (For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained)

                   Is a possible data model for managing payroll where one record in Employees represents a specific employee. A record in TimeCards represents a specific interval of time (depending on how you set it up, it could be one shift, one day, part of a day....) worked at a specific rate by one employee. A record in Payroll would represent a single paycheck issued to an Employee. Each position that might be worked by an employee has a record in Positions with the current hourly rate for regular time work stored in a field in that record.

                   This would appear to be the crux of the matter:

                   

                        We have a lot of employees and many of those employees work different positions throughout the pay period. Some positions pay $11 some positions pay $8 and some pay $14, $15 or $16

                   When you start a new TimeCard record and select an employee for it, the relationships shown can enable auto-entering the hourly rate from Positions into a field in TimeCards to be used to compute the employees wages for time earned on that time card. When an employee's position changes, someone with the access privileges to do so (Such as a manager with a different assigned privilege set) pulls up the employee record for that employee and selects the new position. Then the next time card created for that employee will automatically use the new hourly rate.

                    

              • 4. Re: Using fuctions to add certain quantities
                EdwardAlvarez

                     <!--StartFragment-->Hi,
                     
                     thank you so much for his info. The record shown on the picture are from file maker pro. Each row is one record for one specific employee. I named this employee as TEST TEST for testing proposes. What you have suggested is great but i would have to reprogram the entire data base. We need a quick solution because the holiday season is approaching fast and we are going to be slam. Our payroll guy invests too much time entering the date manually and that is what we are trying to avoid.
                     
                     I am looking for a simple formula that will add the total hours work at $11, the total hours work at $8 etc. is that something doable? I keep trying to write a function that will do this but it keep on adding up the TOTAL hours and it does not break it down as I intend 
                <!--EndFragment-->

                • 5. Re: Using fuctions to add certain quantities
                  philmodjunk

                       Given the catastrophic consequences that can hit your business due to payroll errors, I do not suggest you try for a "Quick" solution.

                       Since you haven't described the current structure of your database, I supplied one of my own creation as an example. To adapt your current design into something that does what you need requires that you describe your current design. If simply entering the date accurately is a key issue, there are any number of ways to design your interface that improve both speed and accuracy. And there are ways to run checks on the dates to spot inconsistencies such as two time card records for the same employee with the same date.

                       And you may find that the "complete reprogramming" wouldn't take as long as you think....

                  • 6. Re: Using fuctions to add certain quantities
                    EdwardAlvarez

                         <!--StartFragment-->Hi,
                         
                         The date base we currently have gets things done the way we need them to be done for now. We are planning to creating a new data base at the beginning of the year (we just can’t do it now due to the amount of work we have for the holidays and it will be too hard to explain our employees how the system will work).
                         
                         What we are trying to do is simplify what we are currently doing. The way our payroll system is set up right now is prone to human error and very tedious.
                         
                         The formula I am trying to figure out will simplify the way our payroll manager enters payroll. I have attached a new picture so that you can a better idea how our payroll records work.
                         
                         This employee work different shifts throughout the pay period and most shifts are different pay rates. Some of them are at $8 and some of them are at $11.  When submit this information to ADP our payroll manager has to enter each individual ‘rate total’ manually. We know how many hours he worked in total for this pay period ‘77.25’ but we have to manually calculate how many hours he worked at $11, how many he worked at $8 and how many he worked at $16.50 (Over time). What I am trying to create is another TOTAL HOURS cell where there will the sum of the total hours worked at $11, at $8 etc. as you can see I already created that cell but the formula is wrong, it keeps adding the total hours, which I already have.
                         
                         This quick fix that we are trying to do will jeopardize anything, in fact this will help our payroll manager’s life so much easier!
                         
                         Thank you for your help!
                         
                         EA
                    <!--EndFragment-->

                    • 7. Re: Using fuctions to add certain quantities
                      philmodjunk

                           I am assuming facts about your business that may or may not be the case. In particular, I am assuming that all employees on a given shift are paid the same rate. If this is no the case, the following method will need to be modified.

                           Add a field to the table on which this layout is based for selecting the shift and thus the pay rate for that shift. Add a related table for Shifts, just one record for each shift and with the pay rate for that shift recorded in a number field in that table.

                           Define a relationship linking the new Shifts table to your existing table by this newly added field for identifying the shift.

                           Set your hourly rate field to auto-enter the rate from this related table.

                           Format your new "shift" field with a value list so that all your payroll person has to do is select the shift and the correct hourly rate will automatically appear, looked up from the new Shifts table.

                      • 8. Re: Using fuctions to add certain quantities
                        EdwardAlvarez

                             <!--StartFragment-->I clearly see what you are saying. But I do not think you understand what I am trying to do. Everything you have told so far is great info and I will apply some of those concepts when creating the new data base.
                             
                             Let’s focus on the ‘work hours’ and ‘rate’ rows. I want to create a formula where every time this formula sees a shift that is being pay at $11 it will add those hours. So for example if I work 5 days in the pay period and 3 of those days I worked at a rate of $11 and I worked 5 hours each day I want the formula to add 15 hours in a separate ‘total hours” (at the bottom) cell. Then I want to create a similar ‘total hours’ cell but this one will only add what ever hours worked at $8. So in the example the formula will add the hours of the 2 days work at $8 (let’s say 4 hours each day), so that would give another total which would be 8 hours. For a grand total (this will be in the TOTAL HOURS cell I already have) of 23 hours for that given pay period.
                             
                             I think this can be done by creating a formula similar to the formula I already have to the total hours. But in this case I need a more complicated formula so that it will only add the hours worked at $11 separate from the hours worked at $8.
                             
                             Thank you again for your time
                             
                             EA
                        <!--EndFragment-->

                        • 9. Re: Using fuctions to add certain quantities
                          EdwardAlvarez
                          /files/84436f956f/Screen_Shot_2013-11-06_at_3.19.37_PM.png 714x612
                          • 10. Re: Using fuctions to add certain quantities
                            philmodjunk

                                 Option 1:

                                 Subtotals for hours worked at each rate are quite possible.

                                 By adding a sub summary layout part to your layout and sorting your records by the rate field, you could group your records by rate and show subtotals for each using the same set of  summary fields.

                                 Option 2:

                                 And While it's a lot more work and time to set up, you can also define one calculation field for each possible rate that might be selected for an employee's work:

                                 One such might look like this:

                                 If ( Rate = 8 ; Work Hours )

                                 That calculation will return the work hours but only when the employee is paid at the rate of 8 dollars per hour. A summary field that computes the total of this field will then report the total hours worked at that rate. This method can also be used to compute total wages at each rate.

                                  

                            • 11. Re: Using fuctions to add certain quantities
                              EdwardAlvarez

                                   that's great! that's exacly what i need! however it only applies it to the first row. i had a similar formula but it adds everything. 

                                   I had: 

                                   if(rate = 11; sum(work hours + extra work hours)

                                   that would add the entire row no matter what pay rate was selected.

                                   the formula you gave me only adds it when is 11 (which exacly what i want) however it does not add anything byt the first row.

                                   EA

                              • 12. Re: Using fuctions to add certain quantities
                                philmodjunk

                                     It does not apply only to the first row. Both methods produce totals over the entire found set of records.

                                     You can perform a find for the records for one employee for a specified pay period and then, if using sub summary layout parts, sort your records by hourly rate.

                                     The second option does not require the sorting.

                                • 13. Re: Using fuctions to add certain quantities
                                  EdwardAlvarez

                                       for some reason the formula is acting correctly when it comes to add the quantity only if it is certain rate. however it only adds it to the first row. Please see the picture below.


                                       Thank you


                                       EA

                                  • 14. Re: Using fuctions to add certain quantities
                                    philmodjunk

                                         Showing me the layout does not show me what you have set up in manage|database | Fields.

                                         Please note that you need to add two fields for every rate. The calculation with the If function and the summary field that totals it. The summary field is the field that should be placed at the bottom of the layout to show the total for that rate.

                                         And if you want to show total hours AND total $$ earned at each rate, you'll need 4 fields for each possible rate, two calculation fields and two summary fields.

                                    1 2 3 Previous Next