Data Type Validation

 

Expand/Collapse all Show/Hide All

For information about data type validation, refer to the appropriate section:

 

Data type validation for import jobs in the Material Requirements Planning and Work Order modules

Each data item being imported is checked to ensure that the data is consistent with the data type defined for the item. The import data is also processed into a format appropriate for that data type. The validation process is performed for each of the following data types.

Expand/Collapse item  Account

Alphanumeric characters are allowed for an account-type field. Import data for an account-type field is processed in accordance with the import mask specified on the Import Job Maintenance Data tab. If alphabetical data is imported, the alphabetical characters are left justified in the mask, and if the mask is smaller than the actual field size, the character positions to the right of the mask are zero-filled. If numeric data is imported, the digits are right justified in the mask and the unused portion of the mask is zero-filled. If the mask is smaller than the actual field size, the character positions to the right of the mask are zero-filled.

Expand/Collapse item  Example 1 - How imported account data is formatted

The following illustration shows how incoming data is formatted if import mask ######### is specified at an account-type field with a field size of nine characters.

Import Data

Formatted Data

123456789

123456789

1234

000001234

2A00  

2A00

ABC  

ABC

 

Expand/Collapse item  Example 2 - How imported account data is formatted

The following illustration shows how incoming data is formatted if import mask #### is specified for an account-type field with a field size of nine characters.

Import Data

Formatted Data

1234

123400000

12

001200000

ABC  

ABC_00000

A20

A20_00000

Note The underscore character (_) used in these examples represent a blank character.

 

Expand/Collapse item  Date

All date-type fields must contain the date in a consistent format specified for the field. You can select from one of the 17 predefined date formats. The selected format defines where the month, date, and year information is stored. Each component of the date is verified separately (that is, the month must be a number from 1 through 12 or the first three characters of the month). The date must be a number from 1 through 31. The year must be a number between 00 and 99 when the two-character year is used, or a number between 1800 and 2100 when the four-character year is used.

Note For file types Excel (.xls) and Lotus (.wk4 and .wks), the date format selected is not important; however , when the date is a text-type field, the date format must have slash separators and the date mask must be con sistent with the format of the text date being imported. If there are no separators in the date (for example, MMDDYY), the file extension must not be .xls, .wks, or .wk4. Instead, use the .csv or .txt file extension.

Expand/Collapse item  Example - How imported dates are formatted

The following illustration shows some supported date formats and how the date of May 31, 2003 must be stored in the import file for each corresponding import mask used.

Import Data

Formatted Data

MMDDYY

053103

MMDDYYYY

05312003

YYMMDD

030531

MMMDDYY

MAY3103

 

Expand/Collapse item  Numeric

Numeric data items must consist of numeric characters, a period, and a minus sign. Only a single period is allowed to designate the decimal point. A minus sign at the beginning or end of the field is also allowed. A number enclosed in parentheses, or ending with the letters CR, is considered to be a negative number. A number ending with the letters DR is considered to be a positive number. Any commas and blank characters in a numeric field are ignored. The numeric data is also checked to ensure that it does not exceed the numeric mask assigned to that field. The numeric mask information is provided on theData Dictionary Listing.

Expand/Collapse item  Example - How imported numeric data is formatted

The following illustration shows how import data is processed by the Visual Integrator module.

Import Data

Formatted Data

10,530.51

10530.51

900.00-

-900.00

(5100.00)

-5100.00

20,100CR

-20100.00

 

Expand/Collapse item  String

Alphanumeric characters are allowed for a string-type field. Any characters beyond the defined field length are ignored.

 

Expand/Collapse item  Phone

Phone-type fields must contain the phone number in one of four supported formats.

  • AAAXXXNNNN
  • AAA-XXX-NNNN
  • (AAA) XXX-NNNN
  • XXX-NNNN

The area code (AAA), prefix (XXX), and number (NNNN) can consist of any combination of alphanumeric characters . The correct length to be entered is determined by the actual format used in the source file. The length must be specified as 8 if the XXX-NNNN format is used, 10 if the AAAXXXNNNN format is used, 12 if the AAA-XXX-NNNN format is used, or 14 if the (AAA) XXX-NNNN format is used.

Expand/Collapse item  Example - How imported phone number data is formatted

The following illustration shows supported phone number formats.

Import Data

Formatted Data

8

850-0111

10 

7148500111

12 

714-850-0111

14

(714) 850-0111

 

Expand/Collapse item  Yes/No

These fields must contain either Y or N. No other values are allowed.

 

Data type validation for import jobs in modules other than Material Requirements Planning and Work Order

Each data item being imported is checked to ensure that the data is consistent with the data type defined for the item. The import data is also processed into a format appropriate for that data type. The validation process is performed for each of the following data types.

Expand/Collapse item  String

For these modules, the data type in the dictionary is limited to String or Numeric. The dictionary supports four format types: ZEROFILL, ALPHANUM, CHARNUM, and MASTERNUM.

If the data type is String, the following validation rules apply:

  • ZEROFILL: Right justify and zero-fill, based on dictionary maximum length for the field.

  • ALPHANUM: If numeric, right justify and zero-fill, based on dictionary maximum length for the field; otherwise, each character must be 0-9, A-Z, a-z, or a blank. Blanks are stripped from the right end.

  • CHARNUM: Same as ALPHANUM, except that in addition, all printable characters are allowed from decimal (.) to tilde (~) in the ASCII collating sequence.

  • MASTERNUM: Same as CHARNUM, except that dash (-) is not allowed.

The following examples show how imported string data is formatted.

Expand/Collapse item  Example 1 - How imported string data is formatted

The following illustration shows how incoming string data is formatted if the format type is ZEROFILL and the dictionary length is 9.

Import Data

Formatted Data

123456789

123456789

1234

000001234

2A00

fail

ABC

fail

 

Expand/Collapse item  Example 2 - How imported string data is formatted

The following illustration shows how incoming string data is formatted if the format type is ALPHANUM and the dictionary length is 9.

Import Data

Formatted Data

1234

000001234

12

000000012

ABC

ABC

aBc

aBc

A20

A20

A 20

A 20

A-20

fail

12.34

fail

 

Expand/Collapse item  Numeric

Numeric data items must consist of numeric characters, a period, and a minus sign. Only a single period is allowed to designate the decimal point. A minus sign at the beginning or end of the field is also allowed. A number enclosed in parentheses, or ending with the letters CR, is considered to be a negative number. A number ending with the letters DR is considered to be a positive number. Any commas and blank characters in a numeric field are ignored. The numeric data is also checked to ensure that it does not exceed the numeric mask assigned to that field.

Expand/Collapse item  Example 1 - How imported numeric data is imported

The following illustration shows how imported numeric data is processed by the Visual Integrator module.

Import Data

Formatted Data

10,530.51

10530.51

900.00-

-900.00

(5100.00)

-5100.00

20,100CR

-20100.00

 

Expand/Collapse item  Example 2 - How imported numeric data is formatted

The following illustration shows how imported numeric data is formatted if the mask is ###,###.00-.

Import Data

Formatted Data

1234567

fail

123456.78

123,456.78

-123456.78

123,456.78-

1234

1,234.00

 

Expand/Collapse item  Date

All date-type fields belong to a class called DATE and must contain the date in a consistent format specified for the field. You can select from one of the 10 predefined date formats. The selected format code defines where the month, date, and year information is stored. Each component of the date is verified separately (that is, the month must be a number from 1 through 12 or the first three characters of the month). The date must be a number from 1 through 31. The year must be a number between 00 and 99 when the two-character year is used, or a number between 1800 and 2099 when the four-character year is used.

Note For file types Excel (.xls) and Lotus (.wk4 and .wks), the date format selected is not important; however , when the date is a text-type field, the date format must have slash separators and the date mask must be con sistent with the format of the text date being imported. If there are no separators in the date (for example, MMDDYY), the file extension must not be .xls, .wks, or .wk4. Instead, use the .csv or .txt file extension.

Expand/Collapse item  Example - How imported dates are formatted

The following illustration shows all supported date formats and how the date of May 31, 2010 must be stored in the import file for each corresponding import mask used.

Format Code

Import Mask

Import Data

1   

YYYYMMDD

20100531

2   

YYMMDD

100531

3   

MMDDYY

053110

4   

MMDDYYYY

05312010

5   

DDMMYY

310510

6   

DDMMYYYY

31052010

7   

M/D/Y

05/31/10 or 5-31-2010 or May/31/2010, etc.

8   

D/M/Y

31-5-10 or 31/05/2010 or 31/May/10, etc.

9   

Y/M/D

2010/05/31 or 10-5-31 or 2010.may.31, etc.

10   

MAS90   

?:0531

 

Expand/Collapse item  Yes/No

These fields must contain either Y or N. No other values are allowed.