| Function | Effect | Example Expression | Example Return |
| if(condition, trueValue, falseValue) |
If the condition is true, returns trueValue, otherwise returns falseValue.
You may also use IIF() or iif() instead of if(). |
if([x] > 0, "positive", "negative") |
"negative" (if x is -2) |
| round(number[,precision]) |
Returns the number rounded to decimal points specified in precision. Negative precision rounds ahead of the decimal.
You may also use ROUND() instead of round(). |
round(5.2345,2)
round(123445,-2) |
5.23
123400 |
| floor(number) |
Returns the largest whole number less than number (rounds down).
You may also use FLOOR() instead of floor(). |
floor(2.3) |
2 |
| ceil(number) |
Returns the smallest whole number greater than number (rounds up).
You may also use CEIL() instead of ceil(). |
ceil(2.3) |
3 |
| empty(variable) |
Returns true if the variable does not exist or is equal to zero, false, or an empty string, otherwise returns false.
You may also use ISBLANK() instead of empty(). |
empty([x]) |
1 (if x is -2) |
| min(x, y) |
Returns the smaller of x and y.
You may also use MIN() instead of min(). |
min(2, 1) |
1 |
| max(x, y) |
Returns the larger of x and y.
You may also use MAX() instead of max(). |
max(2, 1) |
2 |
| time() |
Returns the current UNIX timestamp |
time() |
1345582930 |
| str_replace(before, after, subject) |
Replaces all occurrences of before that appear in subject with after. |
str_replace("a", "A", "bad") |
"bAd" |
| substr(subject, start, length) |
Returns length characters out of subject starting at start. Note that the first character is 0, not 1. |
substr("abcd", 1, 2) |
"bc" |
| stristr(haystack, needle) |
Returns all of haystack from first occurrence of needle to the end. Case insensitive. |
stristr("abcdefg", "cd") |
"cdefg" |
| strstr(haystack, needle) |
Returns all of haystack from first occurrence of needle to the end. Case sensitive. |
strstr("abcdefg", "cd") |
"cdefg" |
| stripos(haystack, needle, search_start_position) |
Returns the numeric position of the first occurrence of needle in haystack starting at search_start_position. Case insensitive. Needle may be one or more characters. Search_start_position is optional. |
stripos("abcd abcd", "bc", 4) |
"6" |
| strpos(haystack, needle, search_start_position) |
Returns the numeric position of the first occurrence of needle in haystack starting at search_start_position. Case sensitive. Needle may be one or more characters. Search_start_position is optional. |
strpos("abcd abcd", "bc", 4) |
"6" |
| strlen(text) |
Returns the numeric length of text |
strlen("abcd") |
"4" |
| substr_count(haystack, needle [,offset] [,length]) |
Returns the number of occurences of needle in haystack. You can optionally specify the offset where to start counting. You can also optionally specify the maximum length after the specified offset to search for the substring. |
substr_count("11:12:13", ":") |
"2" |
| strtoupper(string) |
Returns the upper case version of a string |
strtoupper("aBcD") |
"ABCD" |
| getfield(text,start_marker,end_marker) |
Returns the section of text between start_marker,end_marker. Case insensitive. |
getfield("$abcd~^efgh~","$","~") |
"abcd" |
| splitfield(text,delimiter[,after]) |
Returns the section of text before or after the first occurence of delimiter. Optionally, set after to "1" to get the section after. Case insensitive. |
splitfield("abcd~efgh","~",1) |
"efgh" |
| in_list(needle,[haystack 1],[haystack 2],[haystack n]) |
Returns true or false if the needle is found in the list of items passed in as haystack. Any number of items can be passed in after the needle to make up the haystack. If only the needle is passed in then the expression will return false. |
in_list([payrollEventType],"Standardtime","Overtime") |
False |
| utcdate(format[, timestamp][, employee ID]) |
Formats the timestamp based on the format string (compatible with PHP's date() function) in the employee's time zone, or the company's time zone if the employee ID is omitted. If the timestamp is omitted, the current time is used (useful when you just want the current year or month). |
utcdate("m/d/Y", 1230832800) |
"01/01/2009" (for US time zones) |
| strtoutctime(datetime string[, employee ID]) |
Returns a UTC timestamp for the provided time in the employee's time zone, or the company's time zone if the employee ID is omitted. |
strtoutctime("2010-01-01 12:15:00 PM") |
"1262366100" |
| adjustRangeDstUser(start timestamp,seconds[,employee ID]) |
Returns a UTC timestamp adjusted by the selected number of seconds for the specified employee. If the employee is not specified, the timezone of the division is used. If the adjustment spans a DST transition, the transition is added/subtracted automatically. |
adjustRangeDstUser(1230832800,86400,[usersID]) |
"123919200" |
| scheduleDayTimestamp(employeeID, timestamp, startOrEnd) |
Returns a UTC timestamp that represents either the beginning or end of the schedule day that contains the specified timestamp. The last parameter can be one of "start", "beg", "begin", or "begining" to get the start of the schedule day or one of "end" or "ending" to get the end of the schedule day. |
scheduleDayTimestamp([usersID], [occurs], 'start') |
1234567890 |
| sprintf("format string"[, data, data]) |
Formats the data into the format string (compatible with PHP's sprintf() function). |
sprintf("%s %s",[nameFirst],[nameLast]) |
"Bob Smith" |
| custom(custom key name, custom value name, custom key, division id) |
Returns the "custom value" matching the "custom key" for the custom field named "custom key name" of the type "custom value name". Optional divisionID can be provided to select data for the employee's division (use 0 for all divisions). In the example, a custom data table connects zip codes to territories. The function finds the territory of an employee, based on their zip code. |
custom("Zip Code","Territory",[zip],[divisionID]) |
"Southern" |
| findLastClock(usersID,startTimeStamp, endTimeStamp,clockType) |
Returns the timestamp of the last clockType (either "in" or "out") for the employee with usersID that occurs between startTimeStamp and endTimeStamp inclusive. In the example, the timestamp of the last clock out up to an hour prior to the current clock in will be returned for the employee on the row. |
findLastClock([usersID],[adjTimeIn]-3600,[adjTimeIn],"out") |
"1265222010" |
| findIn(position ,startTimeStamp, endTimeStamp,[usersID] ) |
Returns the timestamp of a clock In during the time range. The timestamp returned is determined by the value of position. If position is 1 then we return the first In. A position of 2 would return the second In. Negative values for position are also supported. To find the last clock In during the range use a position of -1. A position of -2 would give the second to last clock In and so on.
usersID is an optional parameter that limits the scope of the search to just one user.
If there are no events in the range for the user the expression returns -1. |
findIn(1,1230832800,123919200,[usersID]) |
"123913600" |
| findOut(count,startTimeStamp, endTimeStamp ,[usersID] ) |
Returns the timestamp of a clock Out during the time range. The timestamp returned is determined by the value of position. If position is 1 then we return the first Out. A position of 2 would return the second Out. Negative values for position are also supported. To find the last clock Out during the range use a position of -1. A position of -2 would give the second to last clock Out and so on.
usersID is an optional parameter that limits the scope of the search to just one user.
If there are no events in the range for the user the expression returns -1. |
findOut(-1,1230832800,123919200,[usersID]) |
"123917200"
|
| findMaxBreak(usersID,startTimeStamp, endTimeStamp) |
Returns the duration in seconds of the largest range of non-worked time between two timestamps. |
findMaxBreak([usersID],[adjTimeIn],[adjTimeIn]+18000) |
"1800" |
| schedule(usersID,dayOfTimeStamp, schedulePoint[, format]) |
Returns the format time (or timestamp if not specified) of the schedule point specified by schedulePoint (either "firstin" "firstout" "secondin" or "secondout") for the employee with usersID that occurs on the calendar day that includes the timestamp dayOfTimeStamp in the employee's time zone. In the example, the timestamp of the first scheduled clockin time will be returned for the employee on the row. If no schedule is present, "No Schedule" will be returned instead. |
schedule([usersID],[adjTimeIn],"firstin","Y-m-d g:i:s a") |
"2010-04-20 8:00:00 am" |
| getUncoveredScheduleTime(employeeID, timestamp, [honorGracePeriods]) |
Look at an employees worked time and schedule for the day including timestamp and return the duration, in seconds, of the uncovered portions of their schedule. Uncovered time is time that was scheduled to work but was not. The third parameter honorGracePeriods can be with a 1 or 0 (the default). When enabled the system will not require employees to be on the clock during the allowable minutes before/after their schedule points. |
getUncoveredScheduleTime([usersID], [occurs], 1) |
10845 |
| getBenefitsUsed(employeeID, begTimestamp, endTimestamp) |
Returns the duration in seconds of all benefit usages for the specified employee between the two timestamps provided. Includes all benfit types. |
getBenefitsUsed([usersID], [begTimestamp], [endTimestamp]) |
7200 |
| getBenefitTypesUsed(employeeID, begTimestamp, endTimestamp) |
Returns an array of all distinct benefit types used by the employee between the two timestamps provided. |
implode(",", getBenefitTypesUsed([usersID], [begTimestamp], [endTimestamp])) |
Sick, Vacation |
calendarDayMatch(begin,day,
occurs[,daybreak][,usersID]) |
Starting at "begin" (timestamp), consider a calendar day that is "day" (integer) days from then in the context of employee with "usersID." Returns true if the event at time "occurs" falls on on the day when considering "daybreak" (HHMM) to be the start/stop of a day. If daybreak is not specified, it is considered "0000" or midnight. If usersID is not specified, timezone is consitered to be the company's time zone. Example: If a report contains a column for each day, and time needs to be put into columns based on schedules that start at 9AM each day, call as shown in the example to identify time on the 3nd day into the report (2 days past the first). |
calendarDayMatch([begTimestamp],2,
[occurs],"0900",[usersID]) |
True |
| findWorkedTime(usersID[,startTimeStamp][, endTimeStamp]) |
Returns the number of seconds of worked time between the startTimeStamp and endTimeStamp. If set to 0 or left blank, startTimeStamp and endTimeStamp are considered unbounded. |
findWorkedTime([usersID],0,0) |
"86400" |
| findWorkweekInfo(usersID, timestamp, information) |
Return the desired information for the workweek the employee is a member of at the specified timestamp. Valid parameters for information are employeeBeginTime, employeeEndTime, workweekGroupName, and workweekGroupID. |
findWorkweekInfo([usersID], time(), 'workweekGroupName') |
"Direct" |
| trackingField(field[,column][, forClockIn]) |
Returns the desired information for the tracking field specified by field. field can be either the database ID number of the tracking field or can be the name such as "Mileage" if I have a tracking field called mileage. If the field is of the "Select From List" type then you provide one of the following: "val" "code" "paycode" to get either the value, code, or pay code associated with the event. The default is "val." Finally you can specify true or false for forClockIn to gather the tracking field value from either the clock in or clock out of a worked time event. Defaults to true. |
trackingField("Position", "code", true) |
"ACCTNG" |
| updateTrackingField(field,value[, forClockIn]) |
This function will set the tracking field field to the value value for the current event. If the forClockIn variable is omitted or provided as a true value, and the current row is worked time, then the field on the clock in will be updated. If it is provided as false then the field on the clock out will be updated. |
updateTrackingField("Reported Time", time()) |
(No output) |
| pieceworkField(field) |
Returns the desired information for the piecework field specified by field. field is the name such as "Mileage" |
pieceworkField("Mileage")
|
"24354" |
| . (dot) |
Joins two results or strings together. |
"First: ".[nameFirst] |
"First: Bob" |
| updateEmployeeCustomField(field, value, [output]) |
This function will update the appropriate custom employee field with the given value. In addition, this function will return output, or if output has not specified, it will return value. |
updateEmployeeCustomField("testing", "17", "16") |
"16" (And the custom field testing is set to 17) |
| getJobIDByName( name ) |
Returns the unique database jobID for the named job in the context of the current employee. |
getJobIDByName( "Lunch" ) |
1 |
| getJobIDByCode( code) |
Returns the unique database jobID for the job with the code in the context of the current employee. |
getJobIDByCode( "1000" ) |
36 |
| getUserSalaryRateAtTime( userID, timestamp ) |
Returns a decimal rate for Salary employee's for the time of the timestamp. This is compared to [yearly_rate] but can be called when no time is associated with the employee. |
getUserSalaryRateAtTime( [usersID], [begTimestamp] ) |
22760.54 |
| getUserSalaryTypeAtTime(userID, timestamp) |
Returns the employees Pay Type (Salary, Hourly) if they have one set during the entered timestamp. If no pay type is setup the function will return a blank string "". This function can be called with no time associated with the employee. |
getUserSalaryTypeAtTime( [usersID], [begTimestamp] ) |
"Hourly" |
| wo_segments() |
If used in the context of Custom Payroll or Custom Reporting on an event that has a Work Order Job this function will return an array of the names of all Work Order Segments that contain that Work Order Job. |
implode(", ", wo_segments()) |
"Manufacturing, Shop" |
| wo_segment_info(formatString, timestamp[, segmentName]) |
If used in the context of Custom Payroll or Custom Reporting on an event that has a Work Order Job this function will return a string of data about a Work Order Segment if appropriate.
If a value is provided for segmentName then the function will attempt to find a Segment for the Work Order Job by that name. If segmentName is blank then the first Segment alphabetically is used.
The argument timestamp is required to define the scope of the lookup used to calculate the segment's worked hours or percentage of budget worked.
The argument formatString defines both the data that is being requested and the format of the function's response. It should be a string with one or more tags wrapped in curly braces { and } such as "Segment: {name}".
The list of supported tags and their meanings is:
{name} The name of the Work Order Segment. The budgeted hours for the segment, expressed in seconds. Will be "N/A" if there is no budget. {budgeted-hours-HMS} The budgeted hours for the segment, expressed in HH:MM:SS format. Will be "N/A" if there is no budget. {budgeted-hours-HH.HH} The budgeted hours for the segment, expressed in H.HH format. Will be "N/A" if there is no budget. {hours-worked-seconds} The number of hours worked in the segment, expressed in seconds. {hours-worked-HMS} The number of hours worked in the segment, expressed in HH:MM:SS format. {hours-worked-HH.HH} The number of hours worked in the segment, expressed in H.HH format. {percentage} The perctange of the budget worked. Will be "N/A" if there is no budget. {deny-clocking} The current state of the segment's option to deny clocking. Will be either "Accept" or "Deny". (Note that this is as of the current time, not the time passed in through timestamp.) |
wo_segment_info("Segment {name}: {hours-worked-HH.HH} / {budgeted-hours-HH.HH} ({percentage}%)", [occurs]) |
"Segment Manufacturing: 7.00 / 10.00 (70.00%)" |
- reportSourceIsCustomReports
- reportSourceIsPayrollExport
- reportSourceIsAutomaticReports
- reportSourceIsPAPI
|
For Custom Payroll and Custom Reports these functions will tell if the report is being ran from Custom Reports, a Payroll Export, Automatic Reports, or PAPI respectively. This is used when you want to show different information or take different actions for a report being ran as a payroll export, for example, that you do not normally want to do. |
reportSourceIsCustomReports() |
"1" |
| jobInfo(id, field) |
Get information about the job specified by id.
Supported fields are:
name, lcdName, code, category,
isActive, manager, customer, ctrlNum,
glNum, billingRate, and description
|
jobInfo(4, 'name') |
"Support" |
| employeeInfo(id, field) |
Get information about the employee specified by id. |
employeeInfo(3, 'nameLast') |
"Jones" |
| duplicate(list) |
Causes the current row to be duplicated. The first row gets the value at the beginning of the list. Subsequent rows will be created and given values from the comma (or array) of values passed in. If a list, format as "row1,row2,row3"
Columns without duplicate will simply have the data copied.
|
duplicate("Labor,Machine,Setup")
duplicate([payrollEventType].",Bonus")
|
Three rows, the first with "Labor" and the second with "Machine" and last with "Setup"
Two rows, the first with the payroll type, a second with "Bonus"
|
| jobAllocationList(key[,delimiter]) |
Retreives a list of job allocations connected to the worked time. The "key" is one of 'code' 'amount' or 'note'. The "delimiter" defaults to a comma, but if set to NULL will return an array that can be processed by the duplicate() function. See the Duplicate Row and Dynamic Job Allocation Mapping article for more details. |
jobAllocationList(code) |
Returns a comma separated list of all job allocation codes for the time worked. |
Information