Does anyone have a "week of" formula that I can use as part of a weekly timesheet?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I would like the "week of' to go away once the time card for that week is submitted for that user.  My coding skills are very limited!


Thanks!

Photo of TIm

TIm

  • 90 Points 75 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of Jack

Jack, Champion

  • 50 Points
Hi,

Your detail is a little vague so doesn't give me very much to go on. So please if you can explain a little more about your app how the week of is currently populated and the type of relationship that the timesheets table has with any other tables (for example it may be linked to a staff members table i.e. one staff member can have many timesheets) and I'm sure one of the community can help you. If its an app from the app exchange if you can give us the name we can download it and guide you through the change.


Jack
Photo of TIm

TIm

  • 90 Points 75 badge 2x thumb
Yes, the Time In/Time Out table has a relationship to the Staff table.  The staff table contains all staff information for each staff member including salary, hours contracted, several different types of leave (such as personal leave, professional leave, sick leave...) Eventually I would like for the entries on the timesheet to automatically change certain fields on the staff table such as contracted hours remaining, but first I need to just get the basic timesheet built. I am wondering if I need to create a "week of" table and basically create every week of the year (start and end date)  and do a table to table relationship with the time card?  

I would like to be able to set the Time card up so the employee can select the week that their timecard represents, and for an employee not to be able to submit more than one timesheet for a given week.
Photo of Jack

Jack, Champion

  • 50 Points
Ok heres something you can try.

BEFORE EDITING YOUR FORM MAKE A COPY OF THE FORM, JUST IN CASE ANYTHING GOES WRONG.

I assume you have a start week (date field which is the start of the week) for example if it was this week it would show 08/02/2016 (European) or 02/08/2016 (US). I'll refer to it as [Week Date]

In your relationship create a summary field. Then select summary of a specific field and then select max and the date field for week commencing.
Call it Max Week Date. Then create a lookup of this field in the same relationship, this then pulls the value down to the timein/out table it will end up named Staff members - Max Week Date.

What this does it determines the most recent date record relating to the parent record (i.e. the latest time sheet date), by then passing this back as a lookup field to the timesheet table you can create some formulas to do some magic with.

In your timesheet table create a formula date field called Week Date Calc or something meaningful to you. Then write the formula [Max Week Date] + Days(7)

This then determines the date the next weeks timesheet should be based on the employees previous timesheet.

Ok then in the timesheets table create a formula check box called New Record, use the formula
If(not IsNull([Record ID#]),false,true)

This will check the box each time the record is new and not when editing an existing record, this prevents any existing dates being messed up. Now comes the magic.

Using a dynamic form rule make read only the field that you normally type the date into (Week Date) unless the user is not in the role administrator (or whatever your system admin role is called). Then setup a rule that if New Record is equal to checked then change week date to the value in the field Week Date Calc. This means your date will be auto populated each time a user creates a new timesheet.

The one thing you will have to do is setup their first timesheet for new users as they wont be able to add a date.