|
|
Applies to versions: 2.4
Supported Functions for Advanced Expressions
To use expressions in Custom Payroll and Custom Report:
- Select "Expression" from the drop down.
- Put the expression in the Default Column.
- All expressions begin with an "=" (equals sign). For example =utcdate("Y") would show the current 4-digit year.
- To use a variable provided by the report, enclose the variable's name in square brackets (for example, [variable_name]). Sometimes there are "multi-dimensional" variables that are accessed with two sets of square brackets (for example [job][code]). See the documentation for each report or page for a list of available variables in that context.
- In addition to functions, variables can be defined as part of an expression. For complex expressions, this can improve readability and can improve processing speed when a variable needs to be used multiple times. The syntax is to place one or more variable definitions prior to the main expression. They can then be accessed in the main expression. For example:
==hours=[duration]/3600
==workStartDate=utcdate("m/d/Y",[occurs])
=if([hours]>5,[workStartDate],"less than 5 hours this day")
Note that if existing variables are named, they will be overridden. For example, to change the duration from seconds to hours, use:
==duration=[duration]/3600
- When a row is summarized (the "Total (sum)" or other summarize function is selected for at least one column in the report) it's possible to have an expression that evaluates for each row and a separate expression that evaluates on the summarized row. In the summarized row, access columns with [n] where n is the column number. This is useful, for example, when you need to compare summarized values, or when you want to show something only once, such as the total worked hours in a year. To separate the per-row and summarized-row expressions, use this format:
=[row expression]
--summarizeExpression--
=[summarized row expression]
- Example: Total hours worked in the current calendar year, in column 3. Note that the summarized expression only sees the summarized rows, so it cannot directly access [usersID] needed by findWorkedTime. So, the first line puts the employee's ID in the column so it can be used as the first argument to findWorkedTime:
=[usersID]
--summarizeExpression--
=round(findWorkedTime([3],strtoutctime(utcdate("Y-01-01")))/3600,2)
- In addition, in the data style setting, a final expression can be run at post expression time to set the style on the cell, when using formatted output such as pdf or xls. For example:
=[row expression]
--summarizeExpression--
=[summarized row expression]
--postExpression--
=[post expression]
TimeIPS Data Access Fuctions
| Function | Effect | Example Expression | Example Return |
| 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" |
| employeeInfo(id, field) |
Get information about the employee specified by id. |
employeeInfo(3, 'nameLast') |
"Jones" |
| getJobIDByCode( code) |
Returns the unique database jobID for the job with the code in the context of the current employee. |
getJobIDByCode( "1000" ) |
36 |
| getJobIDByName( name ) |
Returns the unique database jobID for the named job in the context of the current employee. |
getJobIDByName( "Lunch" ) |
1 |
| 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. |
| 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" |
| pieceworkField(field) |
Returns the desired information for the piecework field specified by field. field is the name such as "Mileage".
|
pieceworkField("Mileage")
|
"24354" |
| 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" |
| 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) |
| 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) |
| 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 percentage 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%)" |
| 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" |
Standard Logic and Calculations
| Function | Effect | Example Expression | Example Return |
| 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) |
| floor(number) |
Returns the largest whole number less than number (rounds down).
You may also use FLOOR() instead of floor(). |
floor(2.3) |
2 |
| 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) |
| 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 |
| 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 |
String Functions
| Function | Effect | Example Expression | Example Return |
| . (dot) |
Joins two results or strings together. |
"First: ".[nameFirst] |
"First: Bob" |
| getfield(text,start_marker,end_marker) |
Returns the section of text between start_marker,end_marker. Case insensitive. |
getfield("$abcd~^efgh~","$","~") |
"abcd" |
| 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 |
| number_format(number, [decimals], [decimal_point],[thousands_separator]) |
Returns a number formatted with decimal point and thousands separator. |
number_format([duration],0,".",",") |
3,600 |
| splitfield(text,delimiter[,after]) |
Returns the section of text before or after the first occurrence of delimiter. Optionally, set after to "1" to get the section after. Case insensitive. |
splitfield("abcd~efgh","~",1) |
"efgh" |
| 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" |
| 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" |
| stristr(haystack, needle) |
Returns all of haystack from first occurrence of needle to the end. Case insensitive. |
stristr("abcdefg", "cd") |
"cdefg" |
| strlen(text) |
Returns the numeric length of text |
strlen("abcd") |
"4" |
| 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" |
| str_replace(before, after, subject) |
Replaces all occurrences of before that appear in subject with after. |
str_replace("a", "A", "bad") |
"bAd" |
| strstr(haystack, needle) |
Returns all of haystack from first occurrence of needle to the end. Case sensitive. |
strstr("abcdefg", "cd") |
"cdefg" |
| strtoupper(string) |
Returns the upper case version of a string |
strtoupper("aBcD") |
"ABCD" |
| 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" |
| substr_count(haystack, needle [,offset] [,length]) |
Returns the number of occurrences 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" |
Time Functions
| Function | Effect | Example Expression | Example Return |
| 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" |
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 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 considered 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 3rd day into the report (2 days past the first). |
calendarDayMatch([begTimestamp],2,
[occurs],"0900",[usersID]) |
True |
| fromHMS(timeString) |
Takes a string of H:M:S and returns the number of seconds) |
fromHMS("01:05:00")
|
3900
|
| 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" |
| time() |
Returns the current UNIX timestamp. |
time() |
1345582930 |
| toHM(duration) |
Formats a duration into H:M. |
toHM([duration]) |
08:10 |
| toHMS(duration) |
Formats a duration into H:M:S. |
toHMS([duration]) |
08:10:15 |
| toISO8601PT(duration) |
Formats a duration into PTnHnMnS. |
toISO8601PT([duration]) |
PT8H10M15S |
| 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) |
Work and Schedule Access Functions
| 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" |
| 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" |
| 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" |
| 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"
|
| 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" |
| getBenefitsUsed(employeeID, begTimestamp, endTimestamp) |
Returns the duration in seconds of all benefit usages for the specified employee between the two timestamps provided. Includes all benefit 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 |
| getBenefitBalance(employeeID, timestamp, benefitTypeCodename) |
Return the amount of benefit available for the employee at the timestamp in seconds. The benefitTypeCodename is the code name for the benefit.
|
getBenefitBalance([usersID], [reportEnd], "sick") |
7200 |
| 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 |
| 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" |
| 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 clock in 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" |
| 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 "beginning" 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 |
In addition to the functions above, there are several special commands that will result in actions taken by the custom payroll/report engine. To use these, simply have the column evaluate to the Special Command and the Result will happen.
| Special Command |
Result |
Example Command | Example Return |
| action_remove_this_row |
The entire row will be removed from the report. Note: The report engine checks for this after evaluating each column, left to right. If it is found, additional columns are not evaluated. This can be helpful, for example, when a column to the right does something, such as setting a custom tracking field.
|
| action_new_page |
A new page will be created. Requires the report to be run in a mode that supports pages, such as PDF. |
| 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"
|
- 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" |
|
Information