The Field Format tab is used to format source data before it is converted to XML via the IMPORT_FILES service. A source field can have 0 or more formats. The format listed first on the Field Format tab is applied first.
For example, a Person/Employee import includes the birth_date field. The person’s birth_date is January 13, 1967 and in the import file it is formatted as 01/13/1967. In order for the IMPORT_FILES service to correctly import this date, you would add a Field Format record for the birth_date field. This Field Format record will be for the format_date setting with the parameter MM/dd/yyyy.
The Field Format tab is available when you click the Source Fields tab on the Import Definition form.
See Also:
Field Format - Field Descriptions
Find/View a Field Format Record
Import Name
Identifies the Import Name that the record applies to.
Format Name
Identifies the format that will be applied to the source data. The available functions are listed in the table below.
Format Name |
Description/Example |
|
Capitalize First Character of All Words |
capitalize |
The first character
of each word in the field will display in upper case.
|
Capitalize First Character of First Word |
cap_first |
The first character
of the first word in the field will display in upper case.
|
Decimal Hours to Seconds |
decimal_to_hours |
Converts decimal hours to seconds.
|
Format a Date String |
format_date |
Used to identify the date format of the source field. When you select this Format Name, you must enter the Date Format. See Simple Date Format for the letters you can use to create your Date Format.
|
Format a Date and Time String |
format_datetime |
Used to identify the datetime format of the source field. When you select this Format Name, you must enter the DateTime Format. See Simple Date Format for the letters you can use to create your DateTime Format.
|
Format a Time String |
format_time |
Used to identify the time format of the source field. When you select this Format Name, you must enter the Time Format. See Simple Date Format for the letters you can use to create your Time Format.
|
ISO_8601 Duration to decimal hours |
duration_to_decimal_hours |
This format rounds imported values to two decimal places. If you want to specify the number of decimal places to round the value, use the duration_to_decimal_hours_precision Field Format (see below).
|
ISO_8601 Duration to decimal hours with precision |
duration_to_decimal_ |
This format allows imported values to round to the specified number of decimal places if the original value is longer. For the Decimal Places parameter, you can enter a number between 1 and 12.
|
Left Padding |
left_padding |
Adds a specified character to the left side of the value until the value meets a specified length. When you select Left Padding, you must also specify the Pad Character and the String Length. For example, if the Left Padding format is applied to person number 123, with Pad Character “a” and String Length 5, the person number will be imported as aa123.
|
Left Trim by Character |
left_trim_character |
The value will have the specified Trim Character removed starting from the left. For example, if the Left Trim by Character format with Trim Character 0 is applied to person number 0015679, then the person number will be imported as 15679.
|
Lower Case All Characters |
lower_case |
All characters in
the field will display in lower case.
|
Right Padding |
left_padding |
Adds a specified character to the right side of the value until the value meets a specified length. When you select Right Padding, you must also specify the Pad Character and the String Length. For example, if the Right Padding format is applied to person number 123, with Pad Character “b” and String Length 5, the person number will be imported as 123bb.
|
Right Trim by Character |
right_trim_character |
The value will have the specified Trim Character removed starting from the right. For example, if the Right Trim by Character format with Trim Character 9 is applied to person number 1567999, then the person number will be imported as 1567.
|
Substring |
substring |
Select this format to import the part of the value that is between the Start Position and End Position. Counting for the Substring is zero-based. For example, to start counting from the beginning of the string, set the Start Position to 0. Negative Start and End Positions indicate offsets from the end of the string. For example, if the Substring format is applied to Person Number aa123bb, the Start and End Positions shown below will result in the following values: Start Position 2, End Position 5 = 123 Start Position 0, End Position 4 = aa12 Start Position 4, End Position 0 = null value (If the Start Position is not strictly to the left of the End Position, no value will be returned). Start Position -4, End Position 5 = 23
|
Trim Left Spaces |
left_trim |
Any white spaces located to the left of the first character in the field will be removed.
|
Trim Left and Right Spaces |
trim |
Any white spaces located to the left of the first character and to the right of the last character in the field will be removed.
|
Trim Right Spaces |
right_trim |
Any white spaces located to the right of the last character in the field will be removed. |
Un-Capitalize First Character of First Word |
uncap_first |
The first character of the first word in the field will display in lower case. Example: jane Doe
|
Upper Case All Characters |
upper_case |
All characters in
the field will display in upper case.
|
Parameter 1, Parameter 2
The Parameter 1 and Parameter 2 fields are used to specify fill characters or trim characters that are applied to the field’s value based on the Format Name you select. For example, if you select the Format Name “Left Trim by Character” and specify “@” as your Trim Character, the @ symbol will appear in the Parameter 1 column.
Update Date, Updated By
These fields display when the record was created or updated, and the person who created or updated the record.
When you are formatting a date and/or time string for a source field, you can use the following letters to specify the year, month, day, hour, etc.
Letter |
Date or Time Component |
Example |
G |
Era designator (AD) |
2001.07.04 AD = yyyy.MM.dd G |
y |
Year (2001, 01) |
Jul 4, '01 = MMM d, "'yy |
M |
Month in year (July, Jul, 07) |
Jul 4 = MMM d 07.04 = MM.dd |
w |
Week in year |
27 |
W |
Week in month |
2 |
D |
Day in year (1-3 numbers) |
189 = DDD |
d |
Day in month (1-2 numbers0 |
Jul 4 = MMM d Jul 04 = MMM dd |
F |
Day of week in month (1 number) |
Ex: “2” represents second week in the month |
E |
Day in week |
Wed, 4 Jul 2001 = EEE, d MMM yyyy |
a |
AM/PM marker |
12:08 PM = hh:mm a |
H |
Hour in military time (0-23) |
Ex: HH:mm = 13:05 |
h |
Hour in AM/PM (1-12) |
12 |
K |
Hour in AM/PM (0-11) |
0 |
k |
Hour in day (1-24) |
24 |
m |
Minute in hour |
30 |
S |
Millisecond |
978 |
s |
Second in minute |
55 |
Z |
Time Zone |
Ex. -0800 |
z |
Time Zone |
Ex. Pacific Standard Time, PST |
Examples of Date Formats:
Source Data Example |
Simple Date Format |
|
|
|
|
12:08 PM |
|
|
|
|
|
|
|
|
|
|
|
Note: Once the source data is imported you can configure how the values are displayed in the application using the Date Pattern setting in System Settings.
Click Main Menu > Configuration > Interfaces > Import Definition.
Select an Import Type and an Import Name and click Find.
Click the Source Fields tab and select the record that contains the details you want to view.
Click the Field Format tab to view the configured values.
Click Main Menu > Configuration > Interfaces > Import Definition.
Select an Import Type and an Import Name and click Find.
Click the Source Fields tab and select the field for which you want to add a format record.
Click Add on the Field Format tab.
Select a Format Name.
Depending on the Format Name you selected, you may also have to enter a value in the Parameter 1 and Parameter 2 fields. The names of these fields will change depending on the Format Name you select. For example, if you select the Format Name “Left Trim by Character” and specify “@” as your Trim Character, the @ symbol will appear in the Parameter 1 column.
If you selected date/time related strings in the Format Name field, you must identify the source data pattern. Enter the pattern using a Simple Date Format in the Date Format, Time Format, or DateTime Format field. Once the source data is imported you can configure how the values should display in the application using the Date Pattern setting in System Settings.
If you want to add more than one Field Format record, click Save and Add to save your settings and keep the Add Field Format window open. Or click Save to save your Field Format record and return to the main form.
The Formats column on the Source Field tab will update with the formats you have added.
Click Main Menu > Configuration > Interfaces > Import Definition.
Select an Import Type and an Import Name and click Find.
Click the Source Fields tab and select the record that contains the details you want to view.
Select a record on the Field Format tab and click Modify.
Change the parameters as necessary and click Save.
Click Main Menu > Configuration > Interfaces > Import Definition.
Select an Import Type and an Import Name and click Find.
Click the Source Fields tab and select the record that contains the details you want to view.
Select a record on the Field Format tab and click Delete.
Click OK to confirm the action.
The Move Up and Move Down buttons allow you to change the order of the Field Format records. The sequence of the Field Format record is important if you want the service to apply the format to the applicable values. The format listed first will apply first.
To move a record up, select the record on the Import definition form and click the Move Up button until it is positioned where you want to keep it.
To move a record down, select the record on the Import definition form and click the Move Down button until it is located where you want to keep it.