6 Replies Latest reply on Jun 2, 2014 3:45 PM by philmodjunk

    Automatically creating records in a vacation table

    jessieleah

      Title

      Automatically creating records in a vacation table

      Post

           I have an employee database.  It contains employees who are salary (paid on the 15th and last day of the month) and hourly employees who are paid bi-weekly.  People employed for 1-6 years and 364 days earn the same amount vacation time regardless of how they are paid.  People employed 7 years or more earn more per pay period regardless of how they are paid.
            
           I have a related vacation table (related by employee number) and currently records are being entered manually for the vacation time they earn each pay period.
            
           I need help.  I'm am sure there is a way to have this information entered automatically like from a script then have it run by FM Server.
            
           I'm not sure how to create the script, it seems very complicated.
            
           I would want a script to run for biweekly paid people
           add record automatically with the amount of time they are currently accruing (there is a field for that)
            
           I would want a script to run for semi-montly people they are paid on the 15th and last day of the month.
           same here, add record automatically with the amount of time they are currently accruing.
            
           Vacation table:
           It is a relatively new table, I have the following fields in the table
            
           StartAccured (as they all had some accrued prior to this table being built) this field is also used to enter the amount they get every pay period and there is a summary of what they have accrued all together
            
           VacationStart and VacationEnd which calculates the number of days taken (VacationTaken) which is subtracted from what they have earned (VacationAvailable)
            
           The table has a field for the amount they earn per pay period (VacationRate) and also does a lookup of their pay schedule (PaySchedule) when a record is created
           I had created the two field in trying to created the scripts to automatically add records.
            
           However, it is too complicated for me to even begin as I don't even know how to create a script that would run bi-weekly or semi monthly let alone add records and automatically enter their vacation accrual rate based on the VacationRate field
            
           Anyone up for the challenge of helping me with this one?
            
           Thanks and have a good day!

        • 1. Re: Automatically creating records in a vacation table
          philmodjunk

               You appear to have this relationship:

               Employees::__pkEmployeeID = Vacation::_fkEmployeeID

               Your script would seem to follow this basic outline:

               Go to Layout ["Employees" (Employees) ]
               #Perform find for all bi-weekly employees here
               Enter Find Mode []
               Set FIeld [Employees::some field ; "Some criteria goes here" ] ---> What you put here depends on what idenifies an employee as "bi-weekly"
               Perform Find []
               Go to Record/Request/Page [First]
               Loop
                  Set Variable [$EmpID ; value: Employees::__pkEmployeeID ]
                  Set Variable [$VacTime ; Value : // a calculation goes here to compute the correct amount of accrued vacation time]
                  Go to Layout [ "Vacation" (Vacation)]
                  New Record/Request
                  Set Field [Vacation::_fkEmployeeID ; $EmpID ]
                  Set Field [Vacation::AccruedTime ; $VacTime ]
                  Go to Layout ["Employees" (Employees) ]
                  Go to Record/Request/Page [Next ; Exit After Last ]
               End Loop

               The script for bi-monthly employees would be exactly the same except for the find criteria specified in the first set field step. You could even use the same script for both and pass the needed criteria as a script parameter for use with that set field step.

               From there, if you want this script to be performed automatically for each pay period for the two types of employees, there are a number of different ways to Schedule such a script to run. Which method is best depends on how your database system is set up:

               If hosted by FileMaker Server, you can schedule such a script to run daily. The script can check the date and exit without doing anything when it's not the correct date for adding these records.

               If not hosted by server, a "robot" file can be set up to be opened automatically by an OS based scheduling utility such as Windows Scheduled Tasks. The file can be set to run a script in your main file each time it is opened.

          • 2. Re: Automatically creating records in a vacation table
            jessieleah

                 I appreciate the help and I was right with you until the set variables (this is a weakness for me as I don't use them and don't understand them).  If there was a class that taught these and how they work, I would be down with going anyway.  Learning frustration, had to vent.

            Set Variable [$EmpID ; value: Employees::__pkEmployeeID ] I entered with in the calc dialogue box and get an error that I need more parentheses.  Also, I am not really sure what I am entering - does the ___pk have to do with the employee ID from the vacation table or a self-joined table?  The employee number in the various related tables are EMP~No.  Is $EmpID just a label or would it be $EMP~No and in the Vacation table it is VAC~EMPNo

                 What is this variable script step doing?

            Set Variable [$VacTime ; Value : // a calculation goes here to compute the correct amount of accrued vacation time]

            For this one, the calculation - there is a field that populates with the amount.  In my case, I accrue .625 hours per pay period

                 Sorry...

                  

            • 3. Re: Automatically creating records in a vacation table
              mgores

                   For the Variable $EmpID -  when you click on the calculation you can just select the field EMP~No.  Phil is using the convention of "_pk", meaning primary key.  Meaning to use the EMP~ID from the Employee table.

                    

                   For the $VacTime - you can set the variable to that field value or just use the field name instead of the variable.

              • 4. Re: Automatically creating records in a vacation table
                philmodjunk

                     Note that I had to make up names for the fields involved as I don't know the names of your fields. You can substitute your fields for mine.

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

                • 5. Re: Automatically creating records in a vacation table
                  jessieleah

                       I finally put this script into action.  It works - the variable part was scary as it was new to me - but seeing the script in action actually was enlightening.  

                       I added a step to include a note - Set notes field with a calc that enter the current date and some text.  And it worked, WOW!  

                       Thanks for your help!  You guys are just awesome - I wish I could give back in some fashion, you people are way smarter than me!  Thank you again!

                       Next is to make it run on the 15th and last day of the month for the semi-monthly people and every other Friday for the people paid by-weekly.  I'm going to take a shot at figuring that out.  I have an If statement that I use in a couple scripts for end of month, so I am going to try that. and get the 15 and by-weekly worked out.

                       Thanks again - sorry, just really excited!

                        

                  • 6. Re: Automatically creating records in a vacation table
                    philmodjunk
                         

                              I wish I could give back in some fashion

                         The best way to give back is to help someone else when they ask a question. Some questions asked here are very simple and basic. If you spot one and answer it, it saves others like myself the time to answer and we can click on to more involved questions. And helping others is a good way to solidify your own understanding of a concept. wink

                         If, however, you'd truly like to "give back" to a person that helped you out, click the Icon on their posted reply and send them a private message asking them that very question. I get that question every now and then in a private message...