Looking to design a Calendar to tabulate working hours per day for employees.

[fmow]

(apologies in advance if this is in the wrong sub forum. I tried my best to figure out where to post the topic.)

 

I 'd like to design a table where I tabulate a project number, an employee id, and have them input their work hours there, on a daily basis. 

 

So the layout will be 

 

project id | employee id | Week No | Monday | Tuesday | (etc.) | Sunday |

 

Week no, auto generated, and one will just input hours per project each day, null values for no work on the project. 

 

But how do I auto generate new records each week with the week number increasing? I 'd prefer that over the employee selecting the week of the year themselves to avoid errors. 

 

Thanks!

[Ron Cates]

I built a timecard feature in my database that creates a new record for each week in much the same way as you are describing. The logic is all based on the beginning date of the week. I have fields for week begin date and week end date. On login as part of an opening script, the database checks the date and looks to see if there is an existing record for that user for the current week. If not it creates the new week record. I have a field that is populated by  WeekOfYear ( date ) to get the week number for reference but instead of viewing and working with the weeks by week number, I view them and script all the functions based on the week beginning date.

[Lee Smith]

Do a Google search for timesheets and you will find several treads about timesheets. Here is a quick link TIMESHEETS

[fmow]

Hi Lee, unfortunately not many results come up that are relevant, for example no free small scale applications to use as an example. Thanks though.

 

Hi, Kirk, I took your lead and I am following almost the same concept.

 

Set Error Capture [ On ]
#If it's not the first day of the week, that is Monday exit the Script
If [ DayOfWeek ( Get(CurrentDate) ) ≠ 2 ]
Exit Script [ ]
End If
#Setting Variables for current Year and Week No. Serial, and for AccountName
Set Variable [ $VAR0; Value:Year(Get(CurrentDate)) & Right( "0" & WeekOfYearFiscal ( Get(CurrentDate) ; DayOfWeek(Get(CurrentDate) ) ) ;2) ]
Set Variable [ $Var1; Value:Get ( AccountName ) ]
#Finding the fk of the Employee via their AccountName
Go to Layout [ “TimeSheet” (TimeSheet) ]
Go to Record/Request/Page [ Last ]
Perform Find [ Specified Find Requests: Find Records; Criteria: TimeSheet::_fkSerialNumberEmployees: “=$VAR2” AND TimeSheet::_fk_YearWeekNumber: “=$VAR0 - 1” AND TimeSheet::Complete: “=No” AND TimeSheet::NextWeek: “=No” ] [ Restore ]
Sort Records by Field [ Ascending; TimeSheet::_fkProjectSerialNumber ]
Go to Related Record [ From table: “Employees”; Using layout: “Employees” (Employees) ] [ Show only related records ]
Set Variable [ $VAR2; Value:Employees::_pkSerialNumberEmployees ]
Go to Layout [ “TimeSheet” (TimeSheet) ]
#Looping through the found set, duplicating records from last week, incrementing them, and changing the next week flag to No for the new records, and to Yes for the old ones
Loop
Perform Find [ Specified Find Requests: Find Records; Criteria: TimeSheet::_fkSerialNumberEmployees: “=$VAR2” AND TimeSheet::_fk_YearWeekNumber: “=$VAR0 - 1” AND TimeSheet::Complete: “=No” AND TimeSheet::NextWeek: “=No” ] [ Restore ]
Set Variable [ $RunCount; Value:Get ( FoundCount ) ]
Go to Record/Request/Page [ First ]
Set Field [ TimeSheet::NextWeek; TimeSheet::NextWeek = “Yes” ]
Duplicate Record/Request
Commit Records/Requests
Set Field [ TimeSheet::_fk_YearWeekNumber; $VAR0 ]
Set Field [ TimeSheet::NextWeek; TimeSheet::NextWeek = “No” ]
Commit Records/Requests
Perform Find [ Specified Find Requests: Find Records; Criteria: TimeSheet::_fkSerialNumberEmployees: “=$VAR2” AND TimeSheet::_fk_YearWeekNumber: “=$VAR0 - 1” AND TimeSheet::Complete: “=No” AND TimeSheet::NextWeek: “=No” ] [ Restore ]
Exit Loop If [ Get(FoundCount)=0 ]
End Loop
Go to Layout [ “Employees” (Employees) ]
Go to Record/Request/Page [ First ]
Exit Script [ ]