2 Replies Latest reply on Feb 10, 2011 11:30 AM by DanielClark

    Timesheet Number Creation

    DanielClark

      Title

      Timesheet Number Creation

      Post

      I'm trying to create a calculation field that will automatically generate Timesheet ID numbers. I want the calculation field to use the employeeID field then a "-" then a serial number that gets generated and increments in relation to that employeeID and followed by the year's two last digits (2011 would be 11, so like 1-1-11, 1-2-11,... when employee #1 creates timesheets, and 2-1-11,2-2-11,... when employee #2 creates timesheets and so on)

      I would be satisfied with that - however, if its possible, I was wondering if I could instead of just using a incrementing serial number for the middle digit, if I could automatically generate the week number of the year that the timesheet began on (there is a Week Begin field that is a date field). And if that's possible, how would you do that? Thanks.

        • 1. Re: Timesheet Number Creation
          philmodjunk

          I like your second option better. The first method requires either a calulation or a script and if there is any chance that two users might attempt to create a new timesheet record for the same employee at the same time, there's small chance of getting duplicate numbers that is difficult to avoid.

          There's a function you can check out: WeekofYear ( date ) that may do what you want. You'll have to decide what to do though, if The week starts in the last week of December and concludes in the first week of January. If you use the date of the week start, this function will return the week number of the last week of the past year, not 1 as being the first week in the new year.

          And to get a number that combines other values with a serial number, I suggest defining the serial number part in a separate field and using it as your primary key for this table. You can then define a calculation field such as what you have here that combines the other values with it for use as a label, but not in any relationships except possibly one used strictly to find specific records.

          • 2. Re: Timesheet Number Creation
            DanielClark

            Ahhh sweet. Thanks again!