1 2 Previous Next 18 Replies Latest reply on Jul 31, 2017 12:22 PM by philmodjunk

    Payroll record

    waynemartin

      Good morning all,

       

      I'm working on a script to automatically create a payroll record for all employees that had hrs. for the selected week, if the employee didn't have any hrs. than no payroll record will be created.

       

      I know this is probably something simple but i can't seem top wrap my little brain around it. Any help would be greatly appreciated.

       

      Thanks in advance!

        • 1. Re: Payroll record
          beverly

          I might get a distinct list of employee IDs where there are hrs in a range of dates. With this return delimited list, I could loop/create or import to create new records. There are various ways to get the list, but I prefer using ExecuteSQL(). You can also find the records and loop/gathering the unique ids into a variable (return delimited) which can be used to create the new records.

           

          Beverly

          • 2. Re: Payroll record
            waynemartin

            Thanks for responding Beverly,

             

            I do have a list, or a report with all the hr. records for the week, and i am planning to loop/create to create the payroll records, but I'm getting hung up how to get a "distinct" list of the employees. If "John" worked 3 days his name shows up 3 times in this list, "Joe" worked 5 days, his name shows up 5 times in this list. I need to make a list with their names showing up one time.

             

            You mentioned "delimited list", i'm not familiar with that.

            • 3. Re: Payroll record
              beverly

              Found set? then:

                   Sort (by the employeeID.

                   Go to record ( first )

                   Loop

                        Set Variable ($uniqueID ; $uniqueID & IF ( patterncount ($uniqueID & Char(13) ; employeeID & Char(13) ) ; "" // skip adding again ; employeeID & Char(13) // add because it's not there )

                        Exit loop if ( Get (RecordNumber) = Get ( FoundCount ) // stop after last )

                        Go to record (next) # do NOT use exit after last)

                   End Loop

               

              These will be "distinct" because you are only pushing into the variable the employeeID (& a return) if it does not already exist there.

               

              The ExecuteSQL method does not work on a "found set", so the loop may work just fine.

              Beverly

              • 4. Re: Payroll record
                philmodjunk

                You really need an employee table with one record for an employee that you link to your table of hours by employee ID. Not only would this simplify your current task, it would be useful for many other purposes related to HR and payroll.

                1 of 1 people found this helpful
                • 5. Re: Payroll record
                  waynemartin

                  Thanks for the reply Philmodjunk,

                   

                  I do have an employees table, each employee clocks in/out each day so creates a record in Hours table for each day they work. However some employees might be on vacation and haven't logged any hrs for that week so i don't want to be creating a payroll record for that employee.

                   

                  I think Beverly answer is what i was looking for in the "distinct" list. I was not sure how to come up with this list. I'm working on this now and will see where this goes.

                   

                  Thanks

                  • 6. Re: Payroll record
                    waynemartin

                    Beverly,

                     

                    I'm having a really hard time figuring out the script steps you proposed, I'm not understanding your set variable, i'm not sure what value to base the variable off of.

                     

                    Thanks very much for your time

                    • 7. Re: Payroll record
                      beverly

                      Stepping the how/why:

                      Set Variable ( $uniqueID; 1 )

                      Set Variable ( $uniqueID; 2 )

                      Now what value does $uniqueID have?

                      You cannot over-write, so you append: & add a carriage return between, Char(13).

                      Set Variable ( $uniqueID; $uniqueID & 1 & Char(13) )

                      Set Variable ( $uniqueID; $uniqueID & 2 & Char(13) )

                      Now what value does $uniqueID have?

                      1

                      2

                      Well, we don't want to add 1 twice or 2 twice or...

                      So we test to see if the $uniqueID has the value and a return:

                      PatternCount ( $uniqueID ; employeeID & Char(13) )

                      If that is TRUE, we don't want to add it. Otherwise:

                      $uniqueID & employeeID & Char(13) is added (appended to the end) to the list

                       

                      You should then have a return-separted list of employeeIDs.

                       

                      HTH,

                      Beverly

                      • 8. Re: Payroll record
                        fitch

                        If you already have a found set of payroll records for the week, and you want to get to a list of employees that match those records, it seems to me you could simply use the script step:

                        Go to Related Records( employees ; show all related )

                        • 9. Re: Payroll record
                          Jens Teich

                          We need to talk about the data model. Does the OP have a separate table with employees (where each empl. is unique)? Tom seems to believe this while Beverly does not.

                          My recommendation is: if there is no table for employees yet, please create it as philmodjunk already suggested.

                          • 10. Re: Payroll record
                            beverly

                            No, I believe these are unique Employee records. I'm suggesting the loop in the "work schedule" table to get a particular date. Then the list of employeeIDs (foreign key) can be GTRR as Toms says. However, the point is to then create NEW records in the payroll based on those Employees. Why go to the related and still have to loop create?

                            Beverly

                            • 11. Re: Payroll record
                              waynemartin

                              Yes I have a Employees table where each employee has it's own unique ID.

                               

                              I have attached a screen shot of my report list by employee.Capture.PNG

                              • 12. Re: Payroll record
                                Jens Teich

                                Very good!  The payrole report can be generated by searching for the period (month), sorting by employee and summing working times with a summary field.

                                Better idea is a separate table for these payrole entries and script:

                                _ find records of period (last month)

                                _ gtrr employees (like tom suggested)

                                _ loop by record creating new payrole entries

                                • 13. Re: Payroll record
                                  philmodjunk

                                  I would use a relationship that matches by ID and Pay Period to your Hours table to create the needed records from the context of the employees table. You can either find all employees that are "active" or all active employees that have a matching record in Hours and then use your loop to create the needed records from that context.

                                   

                                  That, to me, seems simpler than some of the other approaches discussed here.

                                   

                                  Exactly how you match by pay period depends on how you manage the data in your hours table. You might have a date field with a "week of" date or there are other methods. In the employee table, you can use a global field to match to this date to link to only relevant hours records.

                                   

                                  I'd also consider creating this record at the time the employee clocks in rather than doing a "batch job" to do it at a later time.

                                  • 14. Re: Payroll record
                                    fitch

                                    I'm going to suggest another approach:

                                     

                                    1. in the employee table, create two global date fields, gStart and gEnd

                                    2. create a relationship employees::hrs, let's call it EmployeePayPeriod, with three predicates:

                                    - employee ID = employee ID

                                    - gStart <= date

                                    - gEnd >= date

                                    3. create a calculated field in employees, Sum( EmployeePayPeriod::hrs )

                                    4. put the two global fields on your employees layout, and the new calc field, and if you wish, a portal to show the related hourly records

                                     

                                    Got that? Now, all you do each week is enter the start and end dates, and you have the total weekly hours for each employee.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next