This help page covers the following topics:
Adding a Template - defining the high level name and type of template
Editing Template Details - defining the field by field details within a template layout
Expressions - a list of the advanced expressions that can be used within a field definition
You may also want to check out:
Listing Templates -- displaying a list of existing templates (which you can subsequently edit)
Location: Administrator - Export - Template - Add -- or -- Administrator - Extract - Template - Add
Privilege Needed: Administrator
The screen below can be used to define the high level definition of a template.
 |
Save the data on the current form. |
 |
Clear the data on the current form. |
Template Type - This determines what types of exports this template can be used for. Valid values are Time, Expense, People, Project and Organization.
File Type - This determines the format of output. The options are: Comma Separated Values and Fixed-Format. Comma Separated Values is a file format that has its fields separated by commas. If there are any commas in the values themselves, double quotes will be placed around the entire value. The Fixed-Format has no separating characters and each field is expected to be a certain length.
Template Name - This can be any unique name the user wishes to assign the template.
Enabled - Controls whether or not this template will show up as available in the various selection criteria screen (in the list of available templates drop down). This could come in handy when you are in the middle of designing a template --but do not want anyone using it just yet.
Description - This can be any description the user wishes to give the template.
Once the template is created, the details for
the template can be set by clicking on the 
icon for the template on the Template - List
page.
 |
Insert a new field below selected field. |
 |
Insert a new field above selected field. |
 |
Move selected field up one position. |
 |
Move selected field down one position. |
 |
The sort order of the exported or extracted fields. |
 |
Clear the data on the current form. |
| -Add Records- | Select the desired record layout. Click here for more information about the different Records. |
If no fields have been added previously, select the desired record layout. In the example below, the first field in the Detail record is going to be added to a template.
Record Type - Shows if the record is a "File Header Record", "Group Header Record", "Detail Record", "Group Trailer Record" or "File Trailer Record".
File Header Records -- These show up once and only once in an output file.
Group Header Records -- These records can appear multiple times in the output. An example would be for exporting expenses, you may use a Group Header for each distinct voucher, followed by multiple Detail Records which would represent the various voucher line item details.
Detail Record -- These records can appear multiple times in the output.
Group Trailer Record -- These records can appear multiple times in the output. An example would be for exporting expenses, you may use a Group Trailer for each distinct voucher, which could summaries a number of Detail Records which would represent the various voucher line item details.
File Trailer Record -- These show up once and only once in an output file -- and may be used to show some type of grand total information.
Field Number - Is a number given automatically by the system when entering a field.
Field Name - Any text is allowed here and it does not have to be unique.
Length - This field has two possible meanings -- depending on the type of template (comma separated or fixed-formatted). The output will be truncated to this integer value for comma-separated files. Also, for fixed-formatted output, fields will be expanded to meet this width.
Format - This field is used if you would like to format a date or numeric value. For example, if you want the date to show up in YYYY-MM-DD format, you would supply the following value in this field: DATE(yyyy-MM-dd). Likewise, a sample numeric format entry may look like: NUMBER( 99999999999.99;-99999999999.99). For detailed information about possible date and numeric formatting click here.
Null Value - The user can specify a value to use whenever the data element is null.
Notes - This can store any free-form text that might help the user understand the purpose of this field.
The items in this section determine where Unanet will get the output data. Only one of these choices is valid for any one field.
Constant - this value is the literal value that should be placed in this field. For example, if you enter Hello World then that is exactly what will show up in that field on every output record.
Special Constants
!NEWLINE! -- Inserts a newline character into the middle of a record. It may be useful if you were attempting to create an output layout that had '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). Remember, you can use this value in an expression to get the same effect. For instance, if you wanted to put a newline followed by a note if a row is for overtime data, you could use the following expression:
IF(PAY_CODE,=,"OT","!NEWLINE!",PAY_CODE)
Field - the user can select from a drop-down list of valid database fields from the Unanet system.
Expression - This text box can be used to enter advanced field expressions (that is -- not simply a constant or simple field value). Expressions can be used to do mathematical functions, insert sequence numbers, hide records, append values and more. Many of the expressions can be nested. Each of the expressions is explained in detail below -- with an example for each.
Notes
Expression: ADD
Syntax: ADD(arg1,arg2[,arg3, argn])
Description: This Expression is used to add two or more numbers 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). The process will give you an error if any of the values are non-numeric.
Arguments: arg1..n Any literal values, database fields or expressions which are numeric.
Nestable: Yes.
Example:
ADD(HOURS,5)
Expression: APPEND
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.
Expression: CASE
Syntax: CASE (field,value1,result1 [,value2,result2] ["!DEFAULT!",default1]])
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: field Any literal values, database fields or
expressions that you wish to evaluate.
value1 value to compare to the field.
result1 -- result to return if the field = value1
value2 value to compare to the field.
result2 -- result to return if the field = value2
default1 -- result if the field did not equal any of the valuen
arguments.
Nestable: Yes.
Example:
CASE(PAY_CODE,"RT","Salary","OT","Overtime
Pay","!DEFAULT!","Salary")
Expression: CONCAT
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:
CONCAT(PERSON_LAST_NAME,,
,PERSON_FIRST_NAME)
Expression: DATE
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 box when creating a template, except that you may need the same functionality in the middle of an expression such as an IF or a CONCAT.
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.
Example:
CONCAT(The
date is: ,DATE(WORKDATE,MM/dd/yyyy))
Expression: DIVIDE
Syntax: DIVIDE (arg1,arg2)
Description: This Expression is used to divide the first argument by the second argument. There must only be two arguments. The process will give you an error if any of the values are non-numeric.
Arguments: arg1 Any numeric literal value, database field or
expression that that you wish to divide into.
arg2 Any numeric literal value, database field or expression
that you wish to divide by.
Nestable: Yes.
Example:
DIVIDE(HOURS,2)
Expression: HIDE
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.
However, you may not want to display all the group data on the output
file. This expression allows you to
do just that.
You could also use it to conditionally halt with an error message upon
validating a field that you do not wish to display.
Arguments: arg1 Any date database field that that you wish to
include in the logical grouping or validation, but not display.
Nestable: Yes.
Example:
HIDE(USERNAME)
IF(AMOUNT,<,10,!HALT!Amount is less than 10 stop,AMOUNT)
Expression: IF
Syntax: IF (arg1,arg2,arg3,arg4,arg5)
Description: This Expression is used if you need to evaluate a simple expression and return two different results based on whether the evaluation is true or false.
Arguments: arg1 The literal, database field or expression to be
used in an evaluation.
arg2 The operator used in the evaluation.
Valid values are:
=
(equal)
!=
(not equal)
>
(greater than)
<
(less than)
arg3 The literal, DB field or expression to be used in an
evaluation.
arg4 The literal, DB field or expression to be returned if the
evaluation is true.
arg5 The literal, database field or expression to be returned
if the evaluation is false.
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.
There are two special keywords that can be used in the arg4 and arg5 components of the IF expression. They are !IGNORERECORD! and !HALT!.
!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 (10):
IF(AMOUNT,<,10,!IGNORERECORD!,AMOUNT)
!HALT! causes the export process to stop and report an error.
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.
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 stop,AMOUNT)
Expression: LENGTH
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 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,NAME)
Expression: MULTIPLY
Syntax: MULTIPLY (arg1,arg2[,arg3, argn])
Description: This Expression is used to multiply two or more numbers 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). The process will give you an error if any of the values are non-numeric.
Arguments: arg1..n Any numeric literal values, database fields or expressions that that you wish to multiply.
Nestable: Yes.
Example:
MULTIPLY(QUANTITY,RATE,2)
Expression: !NEWLINE!
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 were attempting to create an output layout that had '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!",PAY_CODE)
Expression: NUMBER
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() in the Format box when creating a template, except that you may need the same functionality in the middle of an expression such as an IF or a CONCAT.
Arguments: arg1 Any numeric database field that that you wish to
format.
arg2 Any format mask allowed by JAVA. For more information
about the format click here.
Nestable: Yes.
Example:
CONCAT(The
number is: ,NUMBER(AMOUNT,###,##0.00))
Expression: PROPERTY
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)
Expression: SEQUENCE
Syntax: SEQUENCE (arg1, arg2)
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 reset each time a field on the same record (specified in arg2) changes.
Arguments: arg1 A numeric literal that represents the starting
number for the sequence counter.
arg2 A literal whose value is the Field Name of the field on
the same record that you want to break on.
When this field changes, the sequence counter will reset.
If you just want the sequence counter to count each record, you can use
for this value and it will never reset.
Nestable: No.
Example:
SEQUENCE(1,)
this one will count forever.
SEQUENCE(1,amount) this one will reset whenever the amount
field changes.
Expression: SUBSTR
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 art3 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,5) bytes 5 through the end of USERNAME
SUBSTR(USERNAME,-5) last 5 bytes of USERNAME
Expression: SUBTRACT
Syntax: SUBTRACT(arg1,arg2[,arg3, argn])
Description: This Expression is used to subtract one or more numbers from the first argument. There must be at least two arguments, but there may be as many as the expression field itself has room to hold (2000 characters). The process will give you an error if any of the values are non-numeric.
Arguments: arg1 Any numeric literal value, database field or
expression that that you wish to subtract from.
arg2 Any numeric literal values, database fields or
expressions that you wish to subtract from arg1.
Nestable: Yes.
Example:
SUBTRACT(QUANTITY,10)
Expression: SUM
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)
Expression: TIMEQUANTITY
Syntax: TIMEQUANTITY (arg1,arg2)
Description: This Expression is used to format a numeric field from the database that represents hours and tenths/hundredths of hours in an expression. It is very similar to using TIMEQUANTITY() in the Format box when creating a template, except that you may need the same functionality in the middle of an expression such as an IF or a CONCAT.
Arguments: arg1
Any numeric database field representing hours that that you wish to format.
arg2 Any format mask. For more information
about the format click here.
Nestable: Yes.
Example:
CONCAT(The number is: ,TIMEQUANTITY(AMOUNT,h:mm))
Return to Unanet Table of Contents page
Copyright © 1998-2001 Computer Strategies, Inc.,
All Rights Reserved.
Last revised: February 13, 2003.