# Time Clock - Basic Time Tracking

**URL:** <https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149>\
**Category:** V5 Tutorial\
**Created:** [September 17, 2015, 7:25pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149 "2015-09-17T19:25:02Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 7:25pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/1 "2015-09-17T19:25:02Z")

</div>

##Time Clock - Basic

Simple Time Clock that allows users (Employee Entities) to Clock In and Clock Out. No custom flow or Navigation mechanisms. Supports Time Tracking, [Punch Editing](https://forum.sambapos.com/t/punch-editor-for-timeclock/9534), and Payroll mechanisms (separate Tutorials).

 ![](https://forum.sambapos.com/uploads/default/original/2X/e/e0252e593e77c7cb46ba618472b3c7f4474ac3c4.png)

###Setup Quick Links

[States for PunchIn and PunchOut](http://forum.sambapos.com/t/time-clock-basic/7149/3)  
[Employee Entities](http://forum.sambapos.com/t/time-clock-basic/7149/4)  
[Time Clock Entity Screen](http://forum.sambapos.com/t/time-clock-basic/7149/5)  
[Automation Commands](http://forum.sambapos.com/t/time-clock-basic/7149/6)  
[Actions](http://forum.sambapos.com/t/time-clock-basic/7149/7)  
[Rules](http://forum.sambapos.com/t/time-clock-basic/7149/8)  
[Rules (extended functionality)](http://forum.sambapos.com/t/time-clock-basic/7149/9)  
[Reports](http://forum.sambapos.com/t/time-clock-basic/7149/10)  
[DB Tools Import Files](http://forum.sambapos.com/t/time-clock-basic/7149/11)

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 7:25pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/2 "2015-09-17T19:25:06Z")

</div>

Unlisted on September 17, 2015, 7:25pm UTC



---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 7:30pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/3 "2015-09-17T19:30:19Z")

</div>

##States for PunchIn and PunchOut

These States are used to track when an Employee is Clocked In or Clocked Out.

 ![](https://forum.sambapos.com/uploads/default/original/2X/a/a97def7c7b559e6f7e33ced431f32f88b29d7d81.png)

**Entity State for `Clocked In`**  
Name: `PunchIn`  
State Name: `TCStatus`  
State Type: `Entity State`

**Entity State for `Clocked Out`**  
Name: `PunchOut`  
State Name: `TCStatus`  
State Type: `Entity State`

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 7:49pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/4 "2015-09-17T19:49:42Z")

</div>

## Employee Entities

###Employee Entity Type

 ![](https://forum.sambapos.com/uploads/default/original/2X/4/4fa73f6f3c2291c5b3c8bd46be8cebfa7ebfb281.png)

> **V5 Only**

> Version 5 supports **`Default States`** for Entity Types. This sets a “starting State” when a new Entity is created. Set the Default States as follows…

> Default States: `TCStatus=PunchOut;TCReport=Unloaded`

* * *

###Employee Entity (example)

 ![](https://forum.sambapos.com/uploads/default/original/2X/6/60cb4542138cb49a829e59ba3da969ee6de9a452.png)

> 💡 You can do mass edits to Employee Entities using **Batch Entity Editor**.

 ![](https://forum.sambapos.com/uploads/default/original/2X/1/104895f9880cbc72e5714189d0bcc7e7ba345213.png)

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 7:59pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/5 "2015-09-17T19:59:25Z")

</div>

##Time Clock Entity Screen

> ❗ Important Settings

> - **Entity Screen Name:** `Time Clock`
> - **Display State:** `TCStatus`
> - **State Filter:** `blank`
> - Select the Employee Entities you want to appear on the screen

 ![](https://forum.sambapos.com/uploads/default/original/2X/4/450ab9d0fa99825e16c081d4f62546b0f6b00795.png)

* * *

##Design Mode Elements

We have a single **`Entity Grid`** and several **`Report Viewers`** (one Report per Employee) on the Time Clock Entity Screen.

### Entity Grid Widget

**Entity Grid Properties**

 ![](https://forum.sambapos.com/uploads/default/original/2X/a/a0037d61874398671128697bd33a463ae806943c.png)

Name: `EmployeeStatus` _(arbitrary)_

**Entity Grid Settings**

 ![](https://forum.sambapos.com/uploads/default/original/3X/7/8/78d4e9336aea91eae67129e519b89e6ea2e6dc70.png)

Entity Type: `Employees`  
Display State: `TCStatus`  
Automation Command: `TC_LoadEntity`  
Command Value: `{ENTITY NAME}`

* * *

### Report Viewer Widgets

**Report Viewer Properties**

 ![](https://forum.sambapos.com/uploads/default/original/2X/7/7a70bcf264053fc1c56726b947aae8d6bdf22c4f.png)

Name: `Hours Ovania`

**Report Viewer Settings**

 ![](https://forum.sambapos.com/uploads/default/original/3X/5/b/5b62a5deaecb924ecd86392200e992b0e572b025.png)

Report Name: `Employee Hours`  
Parameters: `Employee,Ovania`

> 💡 The employee name ‘Ovania’ in the Parameters list needs to be set to the proper Entity in each of the Report Viewer Widgets

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 7:59pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/6 "2015-09-17T19:59:40Z")

</div>

## Automation Commands

###Time Clock Button / Navigation Tile

 ![](https://forum.sambapos.com/uploads/default/original/2X/7/7ec280399255999aa2cb1fbb3f1c6189c88a95bb.png)

Name: `TC_Show Timeclock`  
Category: `Navigation`  
Header: `Time Clock`

**Navigation Tile Template:**

```auto
[People:1]
<size 44><bold>Time Clock</bold></size>

@Ovania,Merlin,Jordani,Jenery,Sonia
[='{SETTING:TCStatus $1}'=='Clocked In' ? '$1 ::: ' + '{ENTITY STATE MINUTES BY NAME:Employees.$1:TCStatus}' : '']

```

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 7:59pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/7 "2015-09-17T19:59:57Z")

</div>

## Actions

### Show Time Clock Screen

We use this Action to refresh the Time Clock Screen.

![](https://forum.sambapos.com/uploads/default/original/2X/4/4d4cd95296fe6fa94a546403846dad1c155ae478.png)

Name: `TC_Show Timeclock Screen`  
Action Type: `Navigation Module`  
Module Name: `Entity`  
Parameter: `Time Clock`  
Hide Header: `True`

* * *

###Execute Automation Command

We use this Action to execute other Actions.

![](https://forum.sambapos.com/uploads/default/original/2X/3/37633d7d7c90542069cb9b4d1ce8fb79bec03cd9.png)

Name: `TC_ExecAMC`  
Action Type: `Execute Automation Command`  
Command Value: _(blank)_  
Automation Command Name: `[:AMCname]`  
Background: `False`  
Delay: `0`

* * *

###Show Message

We use this Action to show Messages.

![](https://forum.sambapos.com/uploads/default/original/2X/b/b28f0d91b166a7105ea742de047905aa6f6d6e9e.png)

Name: `TC_ShowMessage`  
Action Type: `Show Message`  
Message: `[:message]`

* * *

###Ask Question

We use this Action to ask the user to **Clock In** or **Clock Out**.

![](https://forum.sambapos.com/uploads/default/original/2X/2/2461204945a377241d8c630de700a971a5043a49.png)

Name: `TC_Punch (ask)`  
Action Type: `Ask Question`  
Question: `[:Question]`  
Buttons: `[:Buttons]`  
Automation Command Name: `[:AMCname]`  
Background Color: `[:BGcolor]`  
Transparent Color: _(blank)_

* * *

###Load Entity

We use this Action to load an Employee Entity so we can read and set the Entity Data and State.

![](https://forum.sambapos.com/uploads/default/original/2X/9/94925d391264c3d23a39c30c0b6ba3eb2e7ce952.png)

Name: `TC_LoadEntityBySearch`  
Action Type: `Load Entity`  
Entity Type Name: `[:entityTypeName]`  
Entity Name: _(blank)_  
Entity Search Value: `[:searchValue]`

* * *

###Log Entity State

We use this Action to log the Employee Entity State as **`PunchIn`** (Clocked In) or **`PunchOut`** (Clocked Out).

![](https://forum.sambapos.com/uploads/default/original/2X/1/1ca9e2080f733d6696cfc56b4ed6fbcbfc16213f.png)

Name: `TC_LogEntityState`  
Action Type: `Log Entity State`  
Name: `[:logName]`  
Entity Id: `[:entityId]`  
State Name: `[:SGroupName]`  
Start State: `[:StateBeg]`  
Start State Date: `[:SDateBeg]`  
End State: `[:StateEnd]`  
End State Date: `[:SDateEnd]`  
Custom Data: `[:SCustomData]`  
Async: `[:Async]`

* * *

###Update Entity State

We use this Action to set the Employee Entity State to **`PunchIn`** or **`PunchOut`**.

![](https://forum.sambapos.com/uploads/default/original/2X/b/b0a0f2b2770c8a3bf91c30c2af8e724a024ac0ca.png)

Name: `TC_UpdateEntityState`  
Action Type: `Update Entity State`  
Entity Type Name: `Employees`  
Entity State Name: `TCStatus`  
Entity State: `[:newState]`

* * *

###Update Program Setting

We use this Action to update various Program Settings used to track certain parameters.

![](https://forum.sambapos.com/uploads/default/original/2X/e/e61b805979e8d4caa1ab7381fa073203b73f5476.png)

Name: `TC_Update Program Setting`  
Action Type: `Update Program Setting`  
Setting Name: `[:SettingName]`  
Setting Value: `[:SettingValue]`  
Update Type: `Update`  
Is Local: `False`

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 8:00pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/8 "2015-09-17T20:00:08Z")

</div>

## Rules

###Show Time Clock Screen

 ![](https://forum.sambapos.com/uploads/default/original/2X/6/616fa8ab7056d282e453ba814f1bf6c050c3d996.png)

Name: `TC_Show TimeClock Screen`  
Event Name: `Automation Command Executed`  
Constraints:  
Execute Rule if: `Matches`  
`Automation Command Name` _Equals_ `TC_Show Timeclock`

**TC\_Show Timeclock Screen**  
Constraint: _(none)_

* * *

###Load Entity

![](https://forum.sambapos.com/uploads/default/original/2X/a/a9cff683fe549107ff7b33bcce632412c405e9de.png)

Name: `TC_Load Employee Entity`  
Event Name: `Automation Command Executed`  
Constraints:  
Execute Rule if: `Matches`  
`Automation Command Name` _Equals_ `TC_LoadEntity`

**TC\_LoadEntityBySearch**  
Constraint: _(none)_  
entityTypeName: `Employees`  
searchValue: `[:CommandValue]`

**TC\_ExecAMC**  
Constraint: _(none)_  
AMCname: `TC_Punch`

* * *

###Time Clock Punch (ask)

 ![](https://forum.sambapos.com/uploads/default/original/2X/6/626a3d5069dacdddb8da29c053066e03507f9a9c.png)

Name: `TC_Punch (ask)`  
Event Name: `Automation Command Executed`  
Constraints:  
Execute Rule if: `Matches`  
`Automation Command Name` _Equals_ `TC_Punch`

**TC\_Punch (ask)**  
Constraint: _(none)_  
Question: `Select a TimeClock Action for {ENTITY NAME}:`  
Buttons: `Clock In=PunchIn:Orange;Gray,Clock Out=PunchOut:White;Gray,Cancel=Cancel:Red;Gray`  
AMCname: `TC_Update Entity State`  
BGcolor: `DarkBlue`

* * *

###Update Entity State

 ![](https://forum.sambapos.com/uploads/default/original/2X/f/ffb7da65661ffad91711e0e47f9856cafbd8cd48.png)

Name: `TC_Update Entity State`  
Event Name: `Automation Command Executed`  
Constraints:  
Execute Rule if: `Matches All`  
`Automation Command Name` _Equals_ `TC_Update Entity State`  
`Command Value` _Not Equals_ `Cancel`  
`Command Value` _Not Equals_ `{ENTITY STATE:TCStatus}`

**TC\_UpdateEntityState**  
Constraint: _(none)_  
newState: `[:CommandValue]`

**TC\_Update Program Setting**  
Constraint: _(none)_  
SettingName: `TCStatus {ENTITY NAME}`  
SettingValue: `[=('[:CommandValue]'=='PunchIn' ? 'Clocked In' : 'Clocked Out')]`

**TC\_ExecAMC**  
Constraint: _(none)_  
AMCname: `TC_Show Timeclock`

* * *

###Log Entity State

![](https://forum.sambapos.com/uploads/default/original/2X/2/2273f4bcebe1d28eb7f9b76f1e54277b87149d91.png)

Name: `TC_LogEntityState`  
Event Name: `Entity State Updated`  
Constraints:  
Execute Rule if: `Matches`  
`Entity Type Name` _Equals_ `Employees`  
`State Name` _Equals_ `TCStatus`  
`State` _Not Equals_ `Cancel`

**TC\_LogEntityState**  
Constraint: _(none)_  
logName: `TimeClock`  
entityId: `[:EntityId]`  
SGroupName: `[:StateName]`  
StateBeg: `[:StartState]`  
SDateBeg: `[:StartStateDate]`  
StateEnd: `[:EndState]`  
SDateEnd: `[:EndStateDate]`  
SCustomData: `ManualPunch~{SETTING:HolidayFlag}`  
Async: `False`

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 9:46pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/9 "2015-09-17T21:46:18Z")

</div>

## Rules (extended functionality)

###Set Report Date Filter on Application Started

This Rule is for supporting the Payroll setup and Punch Editor setup, which are separate Tutorials.

 ![](https://forum.sambapos.com/uploads/default/original/2X/e/edb7ebb51634f7bed85ecca7bb67a76d687018de.png)

Name: `TC_Set Report Date Filter on App Started`  
Event Name: `Application Started`  
Constraints: _(none)_  
Execute Rule if: `Matches`

**TC\_UpdateProgramSetting**  
Constraint:  
SettingName: `TCDateFilter`  
SettingValue: `This Month`

* * *

###Set Holiday Flag to TRUE

This Rule is for supporting the Payroll setup, which is a separate Tutorial.

 ![](https://forum.sambapos.com/uploads/default/original/2X/4/40afbaf4530eaa453256121ad5ba178f33488918.png)

Name: `TC_SetHolidayFlag TRUE`  
Event Name: `Application Started`  
Constraints:  
Execute Rule if: `Matches Any`  
`[=FD('{DATE}','MM-dd')]` _Matches_ `01-01`  
`[=FD('{DATE}','MM-dd')]` _Matches_ `12-25`

**TC\_UpdateProgramSetting**  
Constraint:  
SettingName: `HolidayFlag`  
SettingValue: `Holiday`

**TC\_ShowMessage**  
Constraint:  
Message: `Today ([=FD('{DATE}','yyyy')]-[=FD('{DATE}','MM-dd')]) is a Holiday! Employees receive Double Pay!`

* * *

###Set Holiday Flag to FALSE

This Rule is for supporting the Payroll setup, which is a separate Tutorial.

> 💡 This Rule is identical to the previous Rule except for the _ **Constraint** _. So you can **Clone** the above Rule, and simply change the Constraint in this Clone.

> The previous Rule has constraint: **`Matches Any`**  
> This Rule has constraint: **`Not Matches All`**

 ![](https://forum.sambapos.com/uploads/default/original/2X/3/390a77981ce1a547b3dd1568ca8be3926c9cbdf2.png)

Name: `TC_SetHolidayFlag FALSE`  
Event Name: `Application Started`  
Constraints:  
Execute Rule if: `Not Matches All`  
`[=FD('{DATE}','MM-dd')]` _Matches_ `01-01`  
`[=FD('{DATE}','MM-dd')]` _Matches_ `12-25`

**TC\_UpdateProgramSetting**  
Constraint:  
SettingName: `HolidayFlag`  
SettingValue: _(blank)_

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 9:46pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/10 "2015-09-17T21:46:57Z")

</div>

##Reports

###Hourly Report

The `$variables` in the Report are passed from the Report Viewer Widget Parameter list. The Parameters are assigned as follows:

```auto
$1 : Entity Type (Employee)
$2 : Employee Name (i.e. Ovania or Jenery or Courtney or ...)
$3 : Date Filter Start (optional)
$4 : Date Filter End (optional)
$5 : unused

```

![](https://forum.sambapos.com/uploads/default/original/3X/3/1/311e5cd857220c5532384ec1c812f1bda72f84d1.png)

```auto
[Current Hours $2:21,10, 10, 10, 18, 15, 6]
@@EmployeePunchInLatest:$1,$2,$3,$4,$5

[Previous Hours $2:21,10, 10, 10, 18, 15, 6]
>Date|Day|Start|End|Hours|OT|H
@@EmployeeHoursDaily:$1,$2,$3,$4,$5
>@@EmployeeHoursTTL:$1,$2,$3,$4,$5

```

* * *

###SQL Scripts

The SQL Scripts are called from within the Report shown above. These scripts need to be set up in

```
Manage > Automation > Scripts

```

**EmployeeHoursDaily**  
Handler: `@@EmployeeHoursDaily`

```sql
-- PARM for Employee Entity Type
declare @EntityType varchar(20) = '@1'
-- PARM for Employee Name
declare @Employee varchar(20) = '@2'
-- PARM for Date Filter Start
declare @StartDateIn varchar(25) = '@3'
-- PARM for Date Filter End
declare @EndDateIn varchar(25) = '@4'

-- if Employee Entity Type is invalid, set default as 'Employee'
IF (@EntityType = '') OR (@EntityType is null) OR (@EntityType = '$1') SET @EntityType = 'Employee'

-- if Date Filter START is invalid, set default to beginning of Current Month
IF (@StartDateIn = '') OR (@StartDateIn is null) OR (@StartDateIn = '$3') SET @StartDateIn = left(CONVERT(VARCHAR(25), GETDATE(), 126),7)+'-01T00:00:00'

-- if Date Filter END is invalid, set a default
IF (@EndDateIn = '') OR (@EndDateIn is null) OR (@EndDateIn = '$4') SET @EndDateIn = dateadd(Month,1,@StartDateIn)

-- set START and END date for Report Period
declare @StartDate datetime = convert(varchar(25),@StartDateIn,126)
declare @EndDate datetime = convert(varchar(25),@EndDateIn,126)
declare @EndDateInc datetime = convert(varchar(25),dateadd(day,-1,@EndDate),126)

-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE)

SELECT
 left(convert(varchar(19),sl.[StartStateDate],120),4)
 +' '+
 left(convert(varchar(19),sl.[StartStateDate],107),3)
 +' '+
 substring(convert(varchar(19),sl.[StartStateDate],120),9,2) as [Date]
,Left(format(sl.[StartStateDate],'D'),3) as [Day]
,substring(convert(varchar(19),sl.[StartStateDate],120),12,5) as [Start]
,substring(convert(varchar(19),sl.[EndStateDate],120),12,5) as [End]
,format((DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0),'0.00') as [Hours]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (format((DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit),'0.00')) ELSE '0' END as [OTH]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [H]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate] < @EndDate
and et.[EntityName] = @EntityType
and sl.[StartState] = @Clockin
and e.[Name]=@Employee
ORDER BY e.[Name], sl.[StartStateDate]

```

* * *

**EmployeePunchInLatest**  
Handler: `@@EmployeePunchInLatest`

```sql
-- PARM for Employee Entity Type
declare @EntityType varchar(20) = '@1'
-- PARM for Employee Name
declare @Employee varchar(20) = '@2'
-- PARM for Date Filter Start
declare @StartDateIn varchar(25) = '@3'
-- PARM for Date Filter End
declare @EndDateIn varchar(25) = '@4'

-- if Employee Entity Type is invalid, set default as 'Employee'
IF (@EntityType = '') OR (@EntityType is null) OR (@EntityType = '$1') SET @EntityType = 'Employee'

-- if Date Filter START is invalid, set default to beginning of Current Month
IF (@StartDateIn = '') OR (@StartDateIn is null) OR (@StartDateIn = '$3') SET @StartDateIn = left(CONVERT(VARCHAR(25), GETDATE(), 126),7)+'-01T00:00:00'

-- if Date Filter END is invalid, set a default
IF (@EndDateIn = '') OR (@EndDateIn is null) OR (@EndDateIn = '$4') SET @EndDateIn = dateadd(Month,1,@StartDateIn)

-- set START and END date for Report Period
declare @StartDate datetime = convert(varchar(25),@StartDateIn,126)
declare @EndDate datetime = convert(varchar(25),@EndDateIn,126)
declare @EndDateInc datetime = convert(varchar(25),dateadd(day,-1,@EndDate),126)

-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE
declare @Now datetime = CONVERT(VARCHAR(25), GETDATE(), 126)

SELECT
 left(convert(varchar(19),sl.[EndStateDate],126),4)
 +' '+
 left(convert(varchar(19),sl.[EndStateDate],107),3)
 +' '+
 substring(convert(varchar(19),sl.[EndStateDate],120),9,2) as [Date]
,Left(format(sl.[EndStateDate],'D'),3) as [Day]
,substring(convert(varchar(19),sl.[EndStateDate],120),12,5) as [Start]
,substring(convert(varchar(19),@Now,120),12,5) as [End]
,format((DateDiff(SECOND, sl.[EndStateDate], @Now)/60.0/60.0),'0.00') as [Hours]
, CASE WHEN (DateDiff(SECOND, sl.[EndStateDate], @Now)/60.0/60.0 > @HourLimit) THEN (format((DateDiff(SECOND, sl.[EndStateDate], @Now)/60.0/60.0-@HourLimit),'0.00')) ELSE '0' END as [OTH]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [H]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
and sl.[Id]=(select max([Id]) from [EntityStateLogs] where [EntityId]=sl.[EntityId] and [EndState]=@Clockin)
and [EndStateDate]>(select max([StartStateDate]) from [EntityStateLogs] where [EntityId]=sl.[EntityId] and [StartState]=@Clockin)
and sl.[StartStateDate] >= @StartDate -- something wrong here
and sl.[EndStateDate] < @EndDate
and et.[EntityName] = @EntityType
and sl.[EndState] = @Clockin
and e.[Name]=@Employee
ORDER BY e.[Name], sl.[StartStateDate]

```

* * *

**EmployeeHoursTTL**  
Handler: `@@EmployeeHoursTTL`

```sql
-- PARM for Employee Entity Type
declare @EntityType varchar(20) = '@1'
-- PARM for Employee Name
declare @Employee varchar(20) = '@2'
-- PARM for Date Filter Start
declare @StartDateIn varchar(25) = '@3'
-- PARM for Date Filter End
declare @EndDateIn varchar(25) = '@4'

-- if Employee Entity Type is invalid, set default as 'Employee'
IF (@EntityType = '') OR (@EntityType is null) OR (@EntityType = '$1') SET @EntityType = 'Employee'

-- if Date Filter START is invalid, set default to beginning of Current Month
IF (@StartDateIn = '') OR (@StartDateIn is null) OR (@StartDateIn = '$3') SET @StartDateIn = left(CONVERT(VARCHAR(25), GETDATE(), 126),7)+'-01T00:00:00'

-- if Date Filter END is invalid, set a default
IF (@EndDateIn = '') OR (@EndDateIn is null) OR (@EndDateIn = '$4') SET @EndDateIn = dateadd(Month,1,@StartDateIn)

-- set START and END date for Report Period
declare @StartDate datetime = convert(varchar(25),@StartDateIn,126)
declare @EndDate datetime = convert(varchar(25),@EndDateIn,126)
declare @EndDateInc datetime = convert(varchar(25),dateadd(day,-1,@EndDate),126)

-- set name of HolidayFlag
declare @HolidayFlag varchar(20) = 'Holiday'
-- set name of Clock-In State
declare @Clockin varchar(20) = 'PunchIn'
-- set Overtime Hour Limit
declare @HourLimit decimal(4,2) = 8.00

-- NOTHING TO SET BEYOND HERE

SELECT
 left(convert(date,[Date],120),7)
,left(convert(datetime,[Date],107),3)
,'',''
, format(sum([Hours]),'###.00') as [Hours]
, format(sum([OTH]),'###.00') as [OTH]
, sum([H]) as [H]
FROM (

SELECT
 sl.[StartStateDate] as [Date]
,Left(format(sl.[StartStateDate],'D'),3) as [Day]
,substring(convert(varchar(19),sl.[StartStateDate],120),12,5) as [Start]
,substring(convert(varchar(19),sl.[EndStateDate],120),12,5) as [End]
,(DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0) as [Hours]
, CASE WHEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0 > @HourLimit) THEN (DateDiff(SECOND, sl.[StartStateDate], sl.[EndStateDate])/60.0/60.0-@HourLimit) ELSE 0 END as [OTH]
, CASE WHEN (CHARINDEX(@HolidayFlag,sl.[CustomData]) > 0) THEN 1 ELSE 0 END as [H]

FROM [EntityStateLogs] sl
left join [Entities] e on e.[Id]=sl.[EntityId]
left join [EntityTypes] et on et.[Id]=e.[EntityTypeId]

WHERE 1=1
and sl.[StartStateDate] >= @StartDate
and sl.[EndStateDate] < @EndDate
and et.[EntityName] = @EntityType
and sl.[StartState] = @Clockin
and e.[Name]=@Employee
) Daily
WHERE 1=1
GROUP BY left(convert(date,[Date],120),7), left(convert(datetime,[Date],107),3)
ORDER BY 1

```

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 9:47pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/11 "2015-09-17T21:47:11Z")

</div>

##DB Tools Import Files

Automation (Rules/Actions): [!TimeClock\_Automation.zip](https://forum.sambapos.com/uploads/default/original/3X/c/0/c0c814b30574ebfc28334e4649a493230a5c724d.zip) (2.5 KB)

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 17, 2015, 10:11pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/12 "2015-09-17T22:11:38Z")

</div>

… reserved for updates …

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [September 18, 2015, 5:18am UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/13 "2015-09-18T05:18:53Z")

</div>

Listed on September 18, 2015, 5:18am UTC



---

<div class="post-metadata">

**Author:** ![devloic](https://forum.sambapos.com/user_avatar/forum.sambapos.com/devloic/32/10735_2.png) [@devloic](https://forum.sambapos.com/u/devloic)\
**Post date:** [October 12, 2015, 8:21pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/15 "2015-10-12T20:21:23Z")

</div>

> [@QMcKay](#):
>
> ## Rules
> 
> ###Update Entity State
> 
> ![](https://forum.sambapos.com/uploads/default/original/2X/f/ffb7da65661ffad91711e0e47f9856cafbd8cd48.png)
> 
> Name: `TC_Update Entity State`  
> Event Name: `Automation Command Executed`  
> Constraints:  
> Execute Rule if: `Matches`  
> `Automation Command Name` _Equals_ `TC_Update Entity State`  
> `Command Value` _Not Equals_ `Cancel`  
> `Command Value` _Not Equals_ `{ENTITY STATE:TCStatus}`

**=\>** Screen capture says _Matches All_ while description says _Matches_

> [@QMcKay](#):
>
> Time Clock Punch (ask)
> 
> Pasted image927x543 21.6 KB
> 
> Name: TC\_Punch (ask)Event Name: Automation Command ExecutedConstraints:Execute Rule if: Matches Automation Command Name Equals TC\_Punch
> 
> TC\_Punch (ask)Constraint: (none)Question: Clock In=PunchIn:Orange;Gray,Clock Out=PunchOut:White;Gray,Cancel=Cancel:Red;GrayButtons: [:CommandValue]AMCname: TC\_Update Entity StateBGcolor: DarkBlue

**=\>** Description is not correct.

---

<div class="post-metadata">

**Author:** ![devloic](https://forum.sambapos.com/user_avatar/forum.sambapos.com/devloic/32/10735_2.png) [@devloic](https://forum.sambapos.com/u/devloic)\
**Post date:** [October 12, 2015, 10:43pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/16 "2015-10-12T22:43:36Z")

</div>

> [@QMcKay](#):
>
> Automation Command: TC\_LoadEntity

@QMcKay  
I haven’t setup the Automation Command TC\_LoadEntity because it is not defined in this tutorial. Could that be the reason why the reports don’t show up in my Time Clock Screen?

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [October 27, 2015, 4:58pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/17 "2015-10-27T16:58:22Z")

</div>

@devloic, thanks for catching the errors… corrections have been made.

> [@devloic](#):
>
> I haven’t setup the Automation Command TC\_LoadEntity because it is not defined in this tutorial.

It is not required.

> [@devloic](#):
>
> Could that be the reason why the reports don’t show up in my Time Clock Screen?

It should have no effect on reporting.

---

<div class="post-metadata">

**Author:** ![gokulhirani](https://forum.sambapos.com/letter_avatar_proxy/v4/letter/g/97f17d/32.png) [@gokulhirani](https://forum.sambapos.com/u/gokulhirani)\
**Post date:** [November 4, 2015, 2:46am UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/18 "2015-11-04T02:46:50Z")

</div>

If I want User List to employee entity

Then Is there any way to link both thing

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [November 4, 2015, 1:02pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/19 "2015-11-04T13:02:07Z")

</div>

Yes, you can link Users to Entities, but that is not what this Tutorial is about. The method you are looking for is in another V4 Topic.

> [@Time Clock: Linked to SambaPOS Users with Custom Reports](http://forum.sambapos.com/t/time-clock-linked-to-sambapos-users-with-custom-reports/2884):
>
> bangbang It is wise to backup of your database and test this first before putting into production Download Link: Operational Flow: Clock In: Employee enters pin or scans badge to log in to SambaPOS4 Time clock greets employee by name and gives option to Clock In or Cancel If employee chooses Clock In it records a punch and logs them into SambaPOS If employee chooses Cancel it takes them back to the User Log In screen of SambaPOS4 Clock Out: Employee navigates to the Time Clock e…

---

<div class="post-metadata">

**Author:** ![rightguys](https://forum.sambapos.com/letter_avatar_proxy/v4/letter/r/ad7895/32.png) [@rightguys](https://forum.sambapos.com/u/rightguys)\
**Post date:** [December 28, 2015, 6:31pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/21 "2015-12-28T18:31:26Z")

</div>

Sorry but I think I am having trouble with setting up the Time Clock.

I go to the Navigation screen, I press Time Clock,

 ![](https://forum.sambapos.com/uploads/default/original/3X/1/a/1a8f357ad858a6b1623a8b5d38ecf1b33fa2c00f.png)

and I am immediately led to this screen:

 ![](https://forum.sambapos.com/uploads/default/original/3X/8/b/8bbe342c49c60e8fc6f50a4b5fbafe942ec72cdc.png)

The report does NOT display and when I click on the name, I get sent directly to the Order screen.

I do NOT see this message:  
 ![](https://forum.sambapos.com/uploads/default/original/3X/c/1/c1cde1b2f734cc07a86e0457dd2f25b1624457a8.png)

I will show screenshots of my progress (rules, actions, etc.) if you’d like me to.  
Thank you.

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [December 28, 2015, 8:00pm UTC](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149/22 "2015-12-28T20:00:58Z")

</div>

Show your Rules (with all Actions fully expanded) for:

TC\_Load Employee Entity  
TC\_Punch (ask)

[Next page](https://forum.sambapos.com/t/time-clock-basic-time-tracking/7149.md?page=2)