|
|
|
|
This page includes a listing of all expressions currently available, along with syntax and examples for each.
Notes:
-- Literal values are defined by any value placed within double quotes (a, 4, etc.).
-- In general, numeric values do not need to be enclosed in double quotes.
-- A database field is defined as any valid field that the export/extract process understands. Users can look at the drop-down list of valid fields in the Expression Wizard when building expressions to see appropriate values. If you create an expression with invalid database fields, the system will give you an error explaining which field is not correct.
-- Nested expressions are expressions inside of other expressions. Some expressions cannot be nested, that is, they cannot be included inside another expression. This is indicated for each expression below.
-- White space and carriage returns are allowed when constructing an expression to allow for improved readability.
Click on a link below to learn more about a particular expression. See the example template below which also has a number of expression examples.
Syntax: APPEND (arg1)
Description: This Expression is used when you want to have one field contain multiple values from the database (which would normally be on separate records). The values will be comma delimited and enclosed in double quotes. The best way to understand this expression is to look at the example below.
Arguments: arg1 A database field that you wish to append.
Nestable: No.
Example: Joe Smith has three roles in the Unanet system; Manager, TimesheetUser, and ExpenseUser. If a People Export is run and the template contains Username and Role, the resulting output would be three records, one for each role. However, if you change the Role field to be the expression:
APPEND(ROLE)
the result would be one record with the role field containing the string Manager,TimesheetUser, ExpenseUser.
As another example, you could sum all time for a user for a timesheet and concatenate all of their timesheet comments into a single field by using:
APPEND (TSCELL_COMMENTS)
Syntax: AUTOHEADER (recordtype)
Description: This expression is used in the HEADER record type to automatically populate the header using the field names on the specified record.
Arguments:
recordtype:
To automatically include a header record that will reflect column names matching the records specified in the current template (on one of the record types), you can use one of the following tags (in double quotes):
GROUPHEADER, DETAIL, GROUPTRAILER, TRAILER
--OR--
To automatically include a header record that will reflect column names matching one of our built-in Import record layouts, supply one of the following values (in double quotes):
ApprovalGroupImport, AssigmentImport, CreditCardImport, ExpenseImport, LaborCategoryImport, LaborCategoryAssignmentImport, PerDiemMieImport, OrganizationImport, OrgAccessImport, PerDiemRateImport, PersonImport, PersonAccrualsImport, PlannedWorkImport, ProjectImport, ProjectControllerImport, TaskImport, TimeImport.
Nestable: No.
Example:
The following example would create a file header made up of the field names from the detail record:
AUTOHEADER(”DETAIL”)
The following example would create a file header made up of the field header for the default person import layouts:
AUTOHEADER(”PersonImport”)
Syntax: CONCAT (arg1,arg2 [,arg3,&argn])
Description: This Expression is used to concatenate two or more values together. There must be at least two arguments, but there may be as many as the expression field itself has room to hold (2000 characters).
Arguments: arg1..n Any literal values, database fields or expressions that you wish to concatenate.
Nestable: Yes.
Example:
The following example would result in the person's last name followed by a comma, a space and then the person's first name --- all within a single output field.
CONCAT ( PERSON_LAST_NAME, ", " ,PERSON_FIRST_NAME)
(extra white space added for illustration purposes -- you do not need the extra spaces though it will function with or without them)
Syntax: DATE(arg1,arg2)
Description: This Expression is used to format a date field from the database within an expression. It is very similar to using DATE() in the Format field when creating a template, except that you may need the same functionality in the middle of an expression such as within an IF or a CONCAT.
Note that if you are creating an expression and want to format a date, you must use the date syntax within the expression and cannot use the Format field.
Arguments:
arg1 Any date database field that you wish to format.
arg2 Any format mask allowed by JAVA. For more information about the format click here.
Nestable: Yes.
Examples:
CONCAT ( "The date is:" , DATE( TSCELL_DATE_WORK, "MM/dd/yyyy" ) )
DATE( PROJ_DATE_ORIG_END, "MM/dd/yyyy" )
Note: An optional modifier (BOT-EOT) can be appended to the DATE expression that will substitute any resulting value that matches the Unanet equivalent of BOT or EOT (ie 1/1/1900 or 12/31/2099) with the textual string BOT or EOT.
DATE( PROJ_DATE_ORIG_END, "MM/dd/yyyy" ) BOT-EOT
Syntax: DATEMATH(date [ + | - ] [ date | int ] )
Description: This Expression can be used for basic date arithmetic. This function does not currently consider the time component of a date. The three scenarios that are supported include:
DATEMATH(date + int) --- returns a date
DATEMATH(date - int ) --- returns a date
DATEMATH(date1 - date2) --- returns a number (this will be a positive number if date1 is a date that falls after date2, and negative if date1 is an earlier date than date2).
For example, 12/10/2010 - 12/15/2010 yields a -5, while 12/15/2010 - 12/10/2010 yields a 5
Arguments:
arg1 Any database date field or a date value entered in the following format (YYYY-MM-DD)
operator + or - (plus or minus )
arg2 If the operator is a +, then only and integer value can be supplied as the second argument. If the operator is a -, then the second argument can be a date or an integer.
Nestable: Yes.
Examples:
DATEMATH(TSCELL_DATE_WORK - 7)
DATEMATH(TSCELL_DATE_WORK + 30)
DATEMATH("2012-12-01" + 15)
IF (DATEMATH(TIME_PERIOD_DATE_END - TIME_PERIOD_DATE_BEGIN) > 10 ) { " > 10" } ELSE { " < 10" }
Syntax: FIELD (arg1,arg2)
Description: This Expression is used to reference another field on a particular record. This Expression is treated as a POST type expression (meaning it is not resolved until the time it is being written out).
Arguments:
arg1 Any of the valid record types used in your template. Could be any of the following:
HEADER
GROUPHEADER
DETAIL
GROUPTRAILER
TRAILER
arg2 -- the number of the field you are trying to reference.
Nestable: Yes (with caveats). You should not include a FIELD expression inside a SUM.
Example:
FIELD(DETAIL,10)
POST(DIVIDE(FIELD(DETAIL,10),FIELD(DETAIL,12)))
Usage Limitations
Unsupported
IF (PAY_CODE = "RT") {
FIELD ( DETAIL,27)
}
As an alternative, you could avoid mixing the two expression types by creating a separate field (which could be hidden) that contains PAY_CODE (row 30 in our example below), then use an expression such as:
IF (FIELD ( DETAIL,30) = "RT") {
FIELD ( DETAIL,27)
}
Syntax: FIELDSUM (recType, number)
Description: This Expression is used when you want to sum a field that you have calculated in your template. This expression is a post-type expression, meaning that it is calculated right before the row is written out rather than each time a database row is read. Its most common use is to sum a calculated field in a detail record and put the result in the group trailer or trailer record.
An example of when this expression might be used is as follows: Let’s say you are writing one DETAIL record out per project, summing the total number of actual hours charged. Part of your output is the budget hours for the project. You want to have a TRAILER record containing the total budget hours and total actual hours. If you simply used the SUM expression for the budget hours TRAILER field, the resulting value would not be correct because it would sum the budget hours value once for each database row read (each cell of each timesheet). Since there would be multiple database records for one project, your value would be too large. If you use the FIELDSUM, however, the system will sum the value that is written out in the DETAIL record and the total will be correct.
Arguments:
recType: Any of the valid record types used in your template. Could be any of the following:
HEADER
GROUPHEADER
DETAIL
GROUPTRAILER
TRAILER
number: The template field number of the field to sum (must be a previous field defined in the export template -- either in the same record type or in a previous record type)
Nestable: Yes.
Example:
FIELDSUM (DETAIL, 6)
Syntax: FIRSTVALUE (arg1, arg2 [, arg3…argn])
Description: This expression is used to pick the first non-blank value out of a list of two or more values. Keep in mind that you can use a constant as the last value for default values logic.
Arguments: arg1..n Any literal values, database fields or expressions that you wish to evaluate.
Nestable: Yes.
Example:
The following example would result in the project account number if it was not blank, otherwise the task account number if it was not blank, otherwise ”No Account Number”.
FIRSTVALUE( PROJ_ACCT_NUM, TASK_ACCT_NUM, ”No Account Number”)
The following example results in the assignment budget amount if populated, otherwise zero:
FIRSTVALUE(ASSIGNMENT_HOURS_BUDGET, 0)
Syntax: !HALT!
Description: This Expression can be used within the IF expression (arg4 and arg5) to stop the processing of an export and provide an error message. It is a way for you to include user-defined errors. You can include text to the right of the !HALT! keyword and this text will be displayed when the error is reported.
Arguments: The expression is followed by message text.
Nestable: Yes.
Example:
For example, the following IF statement causes the export process to stop and report an error if the amount is less than 10, otherwise it will print out the amount:
IF ( AMOUNT < 10 )
{!HALT!Amount is less than 10}
ELSE
{AMOUNT}
IF ( LENGTH(NAME) = "" )
{!HALT!Name is blank}
ELSE
{NAME}
Syntax: HIDE (arg1)
Description: This Expression is used to prevent a field from being included in the output of an export. It is sometimes necessary to include fields on group headers or trailers so that you can have the group by logic work correctly or you may want to use a field as a form of 'working storage', perhaps to perform a calculation that you later reference in a POST command for example. In such cases, you may not want to display the data on the output file. This expression allows you to do just that.
Including this expression will have the following impact, depending on which type of template you are working with:
csv formatted output - the entire column will be suppressed (i.e. output will NOT include a comma representing this field). If you would like to include a comma representing this field but would just like to hide the contents of the field, you can instead include a -1 in the Size column.
fixed formatted output - the output will completely hide this field all together (i.e. it will take up no positions in the output) as if the field were not included in the template. Note that when used with this template type this behavior would yield the exact same results as including a -1 in the Size column.
Arguments: arg1 Any date database field that you wish to include in the logical grouping or validation, but not display.
Nestable: Yes.
Example:
HIDE(USERNAME)
Syntax: IF (value1 op value2) { trueValue} [ ELSE { falseValue} ]
Description: An IF evaluates a condition and returns a user-specified true or false value. Each element of the if statement could be literal, database field, or other expression with the exception of the second element, the operator.
Note that the ELSE clause is optional.
Arguments:
value1 any valid expression.
op The operator used in the evaluation. Valid values are: > >= < <= != = IN !IN STARTSWITH ENDSWITH CONTAINS
value2 any valid expression. When using the IN operator, it must be followed by one to many constant values, separated by a pipe symbol |.
trueValue is any valid expression.
falseValue is any valid expression.
Operator Description
Note that some operators may behave differently depending on the type of data they are dealing with. For example, the > or < operators will work with both numeric and string data. For the string data, they will use the natural sort order for the string and for numeric they will use the numerical order.
= Evaluates to true if value1 is equal to value2
!= Evaluates to true if value1 is not equal to value2
> Evaluates to true if value1 is greater than value2
< Evaluates to true if value1 is less than value2
>= Evaluates to true if value1 is greater than or equal to value2
<= Evaluates to true if value1 is less than or equal to value2
IN Evaluates to true if value1 is contained in a pipe-separated (|) list of values (in the value2 position)
Note that the values in the list will have one set of enclosing double quotes (e.g. IN "OT|RT|ST" )
!IN Evaluates to true if value1 is not contained in a pipe-separated (|) list of values (in the value2 position)
CONTAINS Evaluates to true if value1 contains value2 in it at any position
STARTSWITH Evaluates to true if value1 starts with value2 (the leading characters of value1 match value2)
ENDSWITH Evaluates to true if value1 ends with value2 (the trailing characters of value1 match value2)
You can also use two special keywords as true/false values:
1) !IGNORERECORD! -- suppresses summing and displaying of current record.
2) !HALT!string -- Stops the export and displays the user-defined string as a message. (useful for error reporting).
IF( AMOUNT < 10 ) {!HALT!Amount is less than 10 stop} {AMOUNT}
Nestable: YES.
Example: The IF command is a powerful, potentially complex expression. You are probably more likely to have nested expressions when dealing with complex IF statements, so it is important to keep the syntax correct.
SUM (IF (PAY_CODE IN "OT|PT")
{MATH (TIMESLIP_QUANTITY * TIMESLIP_EFF_COST_RATE)}
ELSE {0} )
IFZERO is not an expression, but rather a special tag that can be used in conjunction with the NUMBER function in the Format column. This feature is documented with the NUMBER function as well as with the Format column help -- but many people said they couldn't find the doc so we're including it with the list of expressions as well.
----------
When using the NUMBER function in the Format field you have an additional capability of including the IFZERO tag. This feature was added as a work-around to the issue with the Java NUMBER(#) format not suppressing a zero value. For example:
NUMBER(#) IFZERO("")
Note that if there is a possibility that the number returned will have additional precision, you may want to include additional precision in your format mask, i.e.:
NUMBER(###.###) IFZERO("")
You cannot use the IFZERO tag when formatting a number within an expression --it can only be used in the Format field.
Syntax: !IGNORERECORD!
Description: This Expression can be used within the IF expression (arg4 and arg5) to prevent individual records from being included in the export output. !IGNORERECORD! causes the export process to ignore the current record and suppress it from the output. If you are summing, the amount is also not included in any totals. This is useful if you wish to add some additional conditional logic to an export. For instance, lets say you wanted to write out records with a dollar amount. Using the IF statement below, you could have the export not include any amounts less than a certain amount.
Note that when exporting time or expense data and using the Mark as Extracted option, any timesheets or expense reports that meet the export selection criteria will be marked as extracted regardless of additional filtering that may occur due to expressions such as the !IGNORERECORD! expression. That is, if a timesheet or expense report was included in the processing but subsequently filtered out due to an export expression, the entire timesheet or expense report will still be marked as extracted.
Arguments: None
Nestable: Yes.
Example:
IF( AMOUNT < 10 )
{!IGNORERECORD!}
ELSE
{AMOUNT}
Syntax: LENGTH (arg1)
Description: This Expression is used to return the length of a particular data element. It is handy for validating your data or making decisions based on the size of fields.
Arguments: arg1 Any date database field that you wish to find the length for.
Nestable: Yes.
Example: The following IF expression displays the name if it is not blank, otherwise it halts the export with an appropriate message:
IF ( LENGTH(NAME) = )
{!HALT!Name is blank}
ELSE
{NAME}
Syntax: MATH (exp)
Description: Method called to evaluate MATH expressions. The MATH expression is really a newer version of all previous math type expressions (DIVIDE, MULTIPLY, ADD, SUBTRACT). It uses much more typical notation and can also use parenthesis to group math operations.
Arguments:
exp -- any combination of numbers, database fields, or expressions that evaluate to numbers plus any of the following operators:
+ addition
- subtraction
* multiplication
/ division
Nestable: Yes.
Example:
MATH (TIMESLIP_QUANTITY * BILL_RATE)
MATH (( TIMESLIP_QUANTITY * BILL_RATE) / TIMESHEET_TOTAL_HOURS )
MATH ( (8/3) * (7 * 2 / 1) - (15 * 2.35 / 12 + 2 - 44) )
Syntax: !NEWLINE!
Description: This is actually another - special keyword, which is used to insert a newline character into the middle of a record. It may be useful if you are attempting to create an output layout that has 'pairs' of output records. You could actually create one detail record, with a !NEWLINE! within the record. This could also be used for simple output formatting (to create blank lines).
Example:
IF( PAY_CODE = "OT")
{!NEWLINE!}
ELSE
{PAY_CODE}
Syntax: NUMBER (arg1,arg2)
Description: This Expression is used to format a numeric field from the database in an expression. It is very similar to using NUMBER() function in the Format field when creating a template, except that you may need the same functionality within the middle of an expression such as within an IF or a CONCAT.
Arguments:
arg1 Any numeric database field that you wish to format.
arg2 Any format mask allowed by JAVA. For more information about the format (including positive and negative numbers) click here.
Nestable: Yes.
Example:
This example shows the use of the number formatter within an expression:
CONCAT("The number is: ",
NUMBER(AMOUNT,"###,##0.00"))
When using the NUMBER function in the Format field you have an additional capability of including the IFZERO tag. This feature was added as a work-around to the issue with the java NUMBER(#) format not suppressing a zero value. For example:
NUMBER(#) IFZERO("")
Note that if there is a possibility that the number returned will have additional precision, you may want to include additional precision in your format mask, ie:
NUMBER(###.###) IFZERO("")
You cannot use the IFZERO tag when formatting a number within an expression --it can only be used in the Format field.
Limitations
When you are using the NUMBER expression to round data inside a SUM, please make sure to NOT include a format mask that has any decorator characters like $ or even group separators like a comma.
BAD Example:
SUM(NUMBER(MULTIPLY(TSCELL_RATE_EFF_COST * TSCELL_QUANTITY),"##,###.00"))
Good Example: (notice the comma in the format mask has been omitted):
SUM(NUMBER(MULTIPLY(TSCELL_RATE_EFF_COST * TSCELL_QUANTITY),"#####.00"))
This can cause issues because the SUM is expecting a numeric only value. In the example above, any value over 1,000 would encounter an issue if a comma is used here. Keep in mind that you can still use whatever format mask you need in the Format field of the record itself (the editable field to the right of where the expression is edited), since this format is applied after all summing has been done.
Syntax: POST (arg1)
Description: This Expression is used when you need to evaluate values right before they are written out -- or post summing. It can also be used when you are not summing values if you want to refer to other fields in a row (see the FIELD expression).
Caution: You should probably use this feature in conjunction with the FIELD expression rather than w/ specific database fields. Using this expression with a database field may be misleading as the value in the database field may be reflecting the previous physical record value (since this is a "post" or after the fact function).
Arguments: arg1 A value or expression.
Nestable: No.
Example:
POST(DIVIDE(FIELD(DETAIL,10),FIELD(DETAIL,11)))
This preceding example will divide the result from field 10 on the detail record by field 11 on the detail record after all summing has occurred and right before the record is written out.
Syntax: PROPERTY (arg1)
Description: This Expression is used to read a property from your Unanet system. The result of this expression will be the value that your Unanet system has for a property, or blank if it does not exist.
Arguments: arg1 The name of the property in the Unanet.properties file.
Nestable: Yes.
Example:
PROPERTY("unapay.default.directory")
Syntax: RECORDCOUNT (arg1)
Description: This Expression is used in cases where you need a total count of a particular record type.
Note that you can only count records that have already been processed, as such you can include a count of "Detail" records in a "Group Trailer" and "Trailer" - but you cannot include a count of "Detail" records in a "Header" (since those records have not yet been processed at the time the Header record is generated.
Arguments:
arg1 Any of the valid record types used in your template. Could be any of the following (case does not matter):
HEADER
GROUPHEADER
DETAIL
GROUPTRAILER
TRAILER
Nestable: Yes.
Example:
RECORDCOUNT(Detail) If used in the File Trailer record, for example, this would return the number of Detail records included in the resulting output.
Syntax: &&Label Goes Here&&
Description: This feature allows you to include a value in your output that is supplied by the user when running an export template. When you enclose a string within a set of 2 ampersands, the system will include a new option on the selection criteria page when running that template. The label for that new option will be the string that you have enclosed in the double ampersands. The value that the user supplies can then be included in the output as a stand-alone value or as part of a more complex expression.
Note that you can also reference the same run time parameter within a template multiple times (and it will only show up on the selection criteria page once).
If supplying a date format and you intend to use it in a comparison, you'll likely want to have that date formatted as follows:
YYYY/mm/dd -- and you may consider including that format as a reminder in the label itself.
Example:
&&Run Date (yyyy/mm/dd): &&
Syntax: SEQUENCE (arg1 [, arg2 [, arg3 [,arg4]]])
Description: This Expression is used in cases where you need a counter. The counter will start at any value you specify in arg1. It will increment differently depending on whether you specify the optional arg3 (see below).
Arguments:
arg1 Literal database field or expression which must resolve to an integer that represents the starting number for the sequence counter and the number that it will reset to when required.
arg2 A literal whose value is the Field Name of the field on the same record that you want to break on (note, this is not the database field name but rather the name you have given the field in the template). When this field changes, the sequence counter will reset. If you just want the sequence counter to count each record, you can use a constant value (e.g. empty) for this and it will never reset.
arg3 An optional literal whose value can be one of the following (EACH, EACH-MULTIFIELD or GROUP):
EACH: This is the default. When operating in the ”EACH” mode, the counter sequence will start at the value of arg1 and increment for each row until the value of arg2 changes. When the value of arg2 changes, the sequence will reset to the value of arg1. Keep in mind that a sequence will only increment once per record. For example, if you include the same sequence 3 times in a DETAIL record, each of those DETAIL records will have sequence numbers like this:
| PROJECT | SEQUENCE(1,"PROJECT") | SEQUENCE(1, "PROJECT") | SEQUENCE(1, "PROJECT") |
| PROJECT A | 1 | 1 | 1 |
| PROJECT A | 2 | 2 | 2 |
| PROJECT A | 3 | 3 | 3 |
| PROJECT B | 1 | 1 | 1 |
EACH-MULTIFIELD: Works the same way as EACH, but increments the sequence number once for each time it is included rather than once for each record processed. For example, if you include the same sequence 3 times in a DETAIL record, each of those DETAIL records will have sequence numbers like this:
| PROJECT | SEQUENCE(1, “PROJECT”, EACH-MULTIFIELD) | SEQUENCE(1, “PROJECT”, EACH-MULTIFIELD) | SEQUENCE(1, “PROJECT”, EACH-MULTIFIELD) |
| PROJECT A | 1 | 2 | 3 |
| PROJECT A | 4 | 5 | 6 |
| PROJECT A | 7 | 8 | 9 |
| PROJECT B | 1 | 2 | 3 |
GROUP: When operating in the ”GROUP” mode, the counter will start at the value of arg1 and increment only when the value of arg2 changes.
arg4 This argument is only valid when arg 3 is GROUP. An optional literal whose value is the Field Name of a field in the template. When arg4 is used, it changes the way the sequence works in the following way:
The sequence will now reset when the value of Arg2 changes
The sequence will count the groups of arg4 values
Nestable: No.
Example:
Ex. 1: SEQUENCE (1)
Ex. 2: SEQUENCE (1, “Project”) or alternatively written, SEQUENCE(1, “Project”, EACH)
Ex. 3: SEQUENCE (1, “Project”, GROUP)
Ex. 4: SEQUENCE (1, “Project”, GROUP, “Person”)
Output for each example below:
| Project | Person | Ex. 1 | Ex.2 | Ex. 3 | Ex. 4 |
| PROJECT A | PERSON X | 1 | 1 | 1 | 1 |
| PROJECT A | PERSON X | 2 | 2 | 1 | 1 |
| PROJECT A | PERSON Y | 3 | 3 | 1 | 2 |
| PROJECT B | PERSON Y | 4 | 1 | 2 | 1 |
| PROJECT B | PERSON Y | 5 | 2 | 2 | 1 |
| PROJECT B | PERSON Z | 6 | 3 | 2 | 2 |
| PROJECT C | PERSON Z | 7 | 1 | 3 | 1 |
| PROJECT C | PERSON X | 8 | 2 | 3 | 1 |
Syntax: !SKIPRESTOFLINE!
Description: This is actually another special keyword, which is used to insert a "terminate processing for the rest of the current record". It may be useful if you are attempting to create an output layout that has 'pairs' of output records. You could actually create one detail record containing both output record layouts using the !NEWLINE! keyword, but use an IF prior to it to optionally create the second record in the pair. In other words, you could either skip the rest of the detail record (not write out the second record of the pair) OR keep going (include the second record in the pair).
Example:
…
…
IF( PAY_CODE = "OT")
{!NEWLINE!}
ELSE
{!SKIPRESTOFLINE!}
…
…
Syntax: SUBSTR (arg1,arg2[,arg3])
Description:This Expression is used to return part of a string. There must be at least two arguments. The process will give you an error if arg2 or arg3 are non-numeric.
Arguments:
arg1 The literal, database field or expression that you want to evaluate.
arg2 An integer representing the starting character of arg1 that you want to return. You can optionally include a negative second argument with no third argument to specify the last n characters of a string.
arg3 (Optional) An integer representing the ending character of arg1 that you want to return. If this value is not used, then the expression will return the characters of arg1 beginning with arg2 through the end.
Nestable: Yes.
Example:
SUBSTR(USERNAME,1,5) bytes 1 through 5 of USERNAME
SUBSTR(USERNAME,2,5) bytes 2 through 5 of USERNAME
SUBSTR(USERNAME,5,5) byte 5 of USERNAME
SUBSTR(USERNAME,5) bytes 5 through the end of USERNAME
SUBSTR(USERNAME,-5) last 5 bytes of USERNAME
Syntax: SUM (arg1)
Description: This Expression is used when you want to sum a database field. The system groups by the other non-summed database fields on a record. If any of the values on the non-summed database fields change, the record will be written and the summed total will start again for the next set of data. Because of this grouping, the sort order is very important when you are using a SUM expression. See the Export Template Layout screen for more details on Ordering.
Arguments: arg1 A numeric database field that you wish to sum.
Nestable: No.
Example:
SUM(TIMESLIP_QUANTITY)
SUM (IF (PAY_CODE IN "OT|PT")
{MATH (TIMESLIP_QUANTITY * TIMESLIP_EFF_COST_RATE)}
ELSE {0} )
Syntax:
SWITCH(switchValue) {
CASE (caseValue, result)
CASE (caseValue, result)
DEFAULT (result) }
Description: This Expression is used to evaluate a field and based on the value of that field, produce different results. It is similar in function to the IF statement, but is easier to use when you need to evaluate one field and take many actions based on its value.
Arguments:
switchValue -- any valid expression
caseValue -- any valid expression
result -- any valid expression
Nestable: Yes.
Example:
SWITCH (PAY_CODE) {
CASE ("RT","Salary")
CASE ("OT","Overtime Pay")
DEFAULT ("Salary") }
Syntax: TIMEQUANTITY (arg1,arg2)
Description: This Expression is used to format a numeric field from the database that represents time in a variety of formats such as hours and tenths/hundredths of hours, hours only, minutes only or seconds only -- in an expression. For example, 8.5 hours can be formatted to be displayed at 8:30 (i.e. 8 and one half hours displays as 8 hours and 30 minutes --> 8:30).
Arguments:
arg1 Any numeric database field representing time that you wish to format.
arg2 Any supported format mask. Valid options include:
hh - hours
mm - minutes
ss - seconds
hh:mm - hours:seconds (where a single digit hour will contain a leading zero)
h:mm - hours:seconds (no leading zero for single digit hours)
Nestable: Yes.
Example:
CONCAT("The number is: " ,TIMEQUANTITY(AMOUNT,h:mm))
Note: The NUMBER format option can also be used with negative values: e.g. NUMBER(##0.00;-##0.00) IFZERO("")
Here are some examples:
Copyright © 2021 Unanet. All rights reserved.
APPEND