9 Replies Latest reply on Sep 22, 2013 6:59 PM by SeanMcCluskey

    Need a script to add customer checks to employee payroll when collected

    SeanMcCluskey

      Title

      Need a script to add customer checks to employee payroll when collected

      Post

           I have been messing around with this for quite some time now can't get it right...

           When a salesperson collects a customer's check they click the "check collected" button on the customers record. This leads them to my "checks collected details" layout where they enter the amount, check type, and a picture of the check. When they do this the check is automatically added to the "checks collected" layout which simply displays all checks collected by each salesperson in the entire time they have been employed.

           I have created three new layouts; "employee payroll," "employee payroll details," and "employee payroll data" the details page is the printable page which is to list all of the checks collected during that week(sat. - friday) by that salesperson and automatically determines their commission for each check. The "employee payroll" layout is simple a layout for me to view all of the current and past payrolls, it shows basic details of each weeks payrolls for each salesperson. Each salesperson's payroll for each week has a status which is either "pending" or "paid." The "employee payroll data" layout is non-viewable I used this to create a portal on my "employee payroll details" layout which displays the info. for each check collected that week.

           I would like to make a script that runs when the "check collected" layout is exited to transfer the information of the check to the salesperson's pending payroll. I need it to determine if there is a pending payroll already created for this salesperson for this week and add the check info to it. If no pending payroll exists it needs to create a new payroll and then add the check data as well as set the status to "pending."

           Keep in mind that the check data has to be transferred to fields located in the portal on my "employee payroll details" page.

           I hope I have given enough information please let me know if you need anything else. I appreciate anyone's help on this matter.

        • 1. Re: Need a script to add customer checks to employee payroll when collected
          philmodjunk

               Sorry, but this really isn't enough information. We need to know what tables you have defined and how they are related. Ideally, once you have entered this info on each check, it should not need to be copied anywhere. Instead a relationship should link the correct checks to the correct sales person and the correct payroll record for that sales person.

          • 2. Re: Need a script to add customer checks to employee payroll when collected
            SeanMcCluskey

                 Here is a screen shot of the tables I have. When a check is collected and entered into the database it automatically collects which customer it belongs to and which sales rep the commission goes to. Currently I have setup a layout based on the Employee Payroll table which has a filtered portal from the Documents Checks Collected table. The portal shows only checks with the status of "pending." Any new check collected is scripted to give it a status of "pending." 

                 The problem with the portal is this; Each check has a status and each payroll for each employee for each pay period has a status of either "pending" or "paid." I would like to change the status of the payroll to "paid" and have the database automatically update all of the collected checks in the payroll to "paid" as well. Right now I have a portal which simply filters for all checks where AccountName = current user and Check Status ="Pending." The problem then becomes that once the checks are changed to "paid" they no longer appear in the payroll portal.

                 I need every payroll to keep the current info. when the status is changed to "paid" so that I have a log of all payrolls ever paid to every sales rep.

            • 3. Re: Need a script to add customer checks to employee payroll when collected
              philmodjunk
                   

                        The problem then becomes that once the checks are changed to "paid" they no longer appear in the payroll portal.

                   Then why not add a different portal with a different filter? You aren't limited to just the one portal after all.

                   And there are other options that can be tried as well.

                   But let's take a closer look at the payroll table. What does one record in this table represent. Could it represent one paycheck for one employee?

                   I'm hoping your answer is yes, because then we can take steps to modify your script that changes the checks records from pending to paid so that it also enters  Payroll ID value in to a field in the checks table so that you can link these records directly to Payroll records.

              • 4. Re: Need a script to add customer checks to employee payroll when collected
                SeanMcCluskey

                     Correct, records based on the Employee Payroll table represent ONE payroll which may consist of multiple customer checks collected and ONE paycheck for a single sales rep.

                     I would like to link checks to payrolls via the Payroll ID, my question is this; When a check is collected how do I write a script to find the sales rep's CURRENT payroll (payroll status = "pending") when they have multiple payrolls (there should always be 1 "pending" and the rest "paid") and add the check to that payroll. I'm thinking a portal maybe isn't the best way to display this???

                     Also, what if there is not currently a "pending" payroll for this sales rep. I was trying to create a script which will either add the check to the current payroll or if non exists create a new payroll. And of course link the two together via the Payroll ID as you mentioned. The script I had created did not work, I'm sure I did something wrong but I can't figure out the right way to get this to work.

                • 5. Re: Need a script to add customer checks to employee payroll when collected
                  philmodjunk

                       Take a look at this relationship:

                       Employees----<Employees Payroll

                       Employees::__pkEmployeeID = Employees Payroll::_FkEmployeeID

                       Let's assume that you have __pkPayrollID defined as a number field with an auto-entered serial number. You can sort Employees Payroll in this relationship to be sorted by __pkPayrollID in descending order. Then the most recently created payroll record is the first related payroll record.

                       When logging the receipt of a customer check. You can use

                       IF [ Employees Payroll::Status = "Paid" ]

                       to determine that a new pending payroll record needs to be created. Then:

                       Freeze Window
                       Set Variable [$EmpID ; value: Employees::__pkEmployeeID ]
                       Go to layout ["Payroll" (Employees Payroll)]
                       New Record/Request
                       Set FIeld [ Employees Payroll::_fkEmployeeID ; $EmpID ]
                       Go to Layout [original layout]

                       Is then all the code needed to create that new payroll record and link it to the current employee. ("Pending" is a status you can set up in field options to be automatically entered for each new payroll record.)

                  • 6. Re: Need a script to add customer checks to employee payroll when collected
                    SeanMcCluskey

                         Okay, I'm getting close here...

                         I have a script that runs when a check is collected, it takes the sales rep to a layout which allows them to enter information about the check, I'm editing my script to also link the check to the correct payroll record via the Payroll ID as you stated but I'm still having trouble. 

                         I understand how to get it to create a new payroll and set the payroll id as a variable but what if a pending payroll already exists? How do I get it to go to the correct pending payroll and set that payroll id as the variable?

                         Heres the script i'm working on:

                          

                    *****This is my original script for simply collecting a check*****

                         Set Variable [$__pkCustomerID; Value:Customers::__pkCustomerID]

                         Go to Layout ["Checks Collected Details" (Documents Checks Collected)

                         New Record/Request

                         Set Field [Documents Checks Collected::_fkCustomerID; $__pkCustomerID]

                         Set Field [Documents Checks Collected::CheckStatus; "Pending"]

                    ******Below is what I have added******

                         Freeze Window

                         Go to Layout ["Employee Payroll" (Employee Payroll)]

                         If [Employee Payroll::Status = "Paid"]

                         New Record/Request

                         Set Variable [$PayrollID; Value:Employee Payroll::__pkEmployeePayrollID]

                         Else

                         Go to Related Record [From table: "Employee Payroll"; Using Layout: "Employee Payroll Details"]

                    *****Will this go to the correct "pending" record?*****

                         End If

                         Go to Layout [orignal layout]

                         Set Field [Documents Checks Collected::_fkEmployeePayrollID; $PayrollID

                    • 7. Re: Need a script to add customer checks to employee payroll when collected
                      philmodjunk

                           but what if a pending payroll already exists? How do I get it to go to the correct pending payroll and set that payroll id as the variable?

                           I am assuming that you are on a layout to Documents Checks Collected when this script is performed:

                           IF [ Employees Payroll::Status = "Paid" ]
                              Freeze Window
                              Set Variable [$EmpID ; value: Employees::__pkEmployeeID ]
                              Go to layout ["Payroll" (Employees Payroll)]
                              New Record/Request
                              Set FIeld [ Employees Payroll::_fkEmployeeID ; $EmpID ]
                              Go to Layout [original layout]
                           End
                           Set Field [Documents Checks Collected::_fkPayrollID ; Employees Payroll::__pkPayrollID ]

                           By the time the last set field step is performed, the relationship from Documents Checks Collected to Customers to Employess to Employee Payroll should link to all of the payroll records linked to that employee and the first related payroll record will be the most recently created record and the If block ensures that it is a Pending payroll record.

                      • 8. Re: Need a script to add customer checks to employee payroll when collected
                        SeanMcCluskey

                             I tried scripting it exactly as you showed above and it still is not working properly. I changed the script to try and figure out which parts of the script were and weren't working below is what I've been testing. I got rid of the IF to see if something was wrong there. 

                             I deleted all of the payroll records and clicked the "check collected" button which ran this script. It created a new payroll record but did NOT set the _fkEmployeePayrollID as it should. No matter what I change it will not set the payroll ID...

                        • 9. Re: Need a script to add customer checks to employee payroll when collected
                          SeanMcCluskey

                               I got it!!! Thank you very much for your help. I think I have everything figured out now. Here's the script that I got to work properly...