Date printed: 12-11-2018   Last updated: 02-16-2012

To view our complete support knowledge base and most current version of this article visit support.timeips.com.
316-264-1600
Information
Sales
Support

Applies to versions: 1.10



Supported Functions for Advanced Expressions



To use expressions in Custom Payroll and Custom Report:

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
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. stristr("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. stripos("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"
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"

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"
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"
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"


See Also:
Expression Variables for Custom Report / Custom Payroll Export (1.10)

©2004-2018 TimeIPS, Inc. All Rights Reserved - TimeIPS and the TimeIPS logo are registered trademarks of TimeIPS, Inc.
"Intelligent Personnel System" "Run your Business. We'll watch the clock." are trademarks of TimeIPS, Inc.
TimeIPS is protected by one or more patents. Patent No. US 7,114.684 B2.