4 Replies Latest reply on Jul 1, 2012 5:56 PM by AlexR

    Snapshot of contents of a portal's contents

    AlexR

      Title

      Snapshot of contents of a portal's contents

      Post

      I have a table of Employees. This table contains data relating to their conditions of employment (salary, hours per day, ....). I have a table of Payslips (related to Employees) which contains the monthly payslips of employees. When I create a new Payslip, a script copies over the relevant data from Employees table to the Payslip table (so that if the employee's salary changes, for example, it doesn't change past payslips). So far, so good.

      The problem is that some of the data relating to an employee is in another table calles Allowances. The reason that there is a separate table for this is that there can be a large number of allowances (housing, transport, ....) and that new types of allowances can be created at any time, so I can't really hard code these allowances as fields in the Employee table. When I create a new Paysiip for an employee I need to know what his allowances were at that point in time and create a snapshot of them, so that if the allowances change, it doesn't change past payslips.

      Any ideas how to do this elegantly? 

        • 1. Re: Snapshot of contents of a portal's contents
          philmodjunk

          What will you do with that 'snapshot'? In other words, how does that data affect what you put on the payslip?

          Do you need the actual data from allowances or do you need some type of total from them?

          There are two different auto-enter field options that can copy data from either the employee or allowances data into your payslip data. The looked up value option copies the contents of a field in the related table into the field in the payslip table. The auto-entered calculation can do that also, but it can also enter the results of a calculation such as Sum ( allowances::amount ) into a single field in payslips.

          • 2. Re: Snapshot of contents of a portal's contents
            AlexR

            Thanks for your response PhilModJunk.

            I need the actual data from the "snapshot", not a total, for the following reasons:

            1- I want a trace of what allowances an employee was entitled to that particular month, in case of disputes etc...

            2- Some allowances are taxed at different rates, and at the end of the year I want to be able to look at the employee's previous 12 months' worth of payslips and prepare tax automatically.

            3- Some past allowances are used in the calculation of severance pay, some are not...

            • 3. Re: Snapshot of contents of a portal's contents
              philmodjunk

              1) If you use an auto-enter calculation such as:

              List ( allowances::Field )

              You will auto-enter a return separated list of values, one for each related record in to a single field.

              2 & 3) the list of values from 1 can be used in a relation ship or you can perform a find on it to find all records that contain a specific value in the list. Return separated values in a field used in a relationship set up an "or" type of match where any one of the values in the list can match to the field on the other side of the relationship.

              Another option is to generate allowance records with effectivity dates. If they are always in place for a given year, you might just need to record the year for each one. In other cases, you may need two dates to document the date range over which the allowance record applies.

              • 4. Re: Snapshot of contents of a portal's contents
                AlexR

                Thank you PhilModJunk. 

                That works.