Field Data Types

Owned by Stephen Young
Last updated: Oct 01, 2017 by Brian Banet

When creating GlobalSearch Fields, they must be assigned a Data Type. GlobalSearch and/or the SQL Server database will ensure user entered values for match the Data Type.  Data types are important for both enforcing the type of date entered into a field, in addition to searching for ranges and limits.

Character Field Data

The Character data type includes letters, numbers, and characters. Use this data type for alphanumeric data entry, such as names or for numbers which are a “string of digits,” such as a P.O. number or Social Security Number. You can change from any other data type to Character. You cannot change from Character to another data type.

Keep in mind that changing the data type will likely affect how the data sorts. For example, numbers that use the Character data type sort differently than with a numeric sort. 1, 2, 10, 11, 12, 2, 20, 21, 22 will sort into 1, 10, 11, 12, 2, 20, 21.

The length of a Character Field defaults to 50 characters. You can increase the maximum length up to 8000 characters.


Editing Character Fields

It is possible to edit a Character Field to make it shorter, but if you make the Field shorter than any current data in the Field you will risk losing data. GlobalSearch allows you to enter in a length shorter than any current data and then, when you open up existing documents with values longer than the new length, it will not let you save the document with any new data until you have altered the field and made the data the minimum number of characters specified in the Index Field Length.


Numeric Field Data

Numeric data types can only be integers (whole numbers, no fractions). If a decimal is entered into a Field with the Numeric data type, it will be rounded up or rounded down, depending upon the value. The appearance of Numeric Fields can be controlled with the Format box. (Refer to Values for Formatting Index Fields for details.) The Numeric data type supports values from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).

Date Field Data

Use the Date datatype for date, time, or both date and time Fields. You can enter a date in many different formats, such as words (month and day spelled out), abbreviations (month and day abbreviated), one-digit or two-digit months and dates, two- or four-digit years, slashes or dashes, etc. The appearance of Date Fields can be controlled with Formats. (Refer to Values for Formatting Index Fields for details.)

Searching Date and Time Ranges

If you create a Date/Time Field, but format it as date only, you can perform an “Equals” search on date. However, if you provide a format that includes time, you can’t search for “equals a date” without also providing a specific time. If formatting as date/time, use “Greater Than or Equal To” or “Less Than or Equal To.”


Decimal Field Data

The Decimal data type can only include integers or decimals. Use for this data type for Fields denoting money. This Data Type expects two decimal places. The appearance of Decimal Fields can be controlled with Formats. (Refer to Values for Formatting Index Fields or details on formatting.)

You can enter as many decimal places as you like and they will be stored. However, a Format setting will control how many decimal places will display. The actual value will display rounded up or down if the Format supports fewer decimal places than the data that was entered. The Decimal data type supports values from -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308.


Storage Estimates for Field Data

  • Character: It takes one byte of storage per character typed, plus two to four bytes of overhead. In SQL, this data type is a CharVar (Character Variable) field. SQL is limited to 8000 characters.
  • Numeric: It takes four bytes of storage per integer. In SQL, this data type is an Int (Integer) field.
  • Date: DateTime is stored as two four-byte integers. In SQL, this data type is a DateTime (Date and/or Time) field.
  • Decimal: Decimal takes eight bytes of storage. In SQL, this data type is a Float53 field.