Formatting Columns

We can change the appearance of values shown in a column by formatting the column. To do so, right click onto a column head and choose Format from the context menu. The same format will be applied throughout the entire column.

 

Format options will be displayed that are appropriate to the type of column. For example, a column for a floating-point field will have different Style options than a money field, integer field or text field.

 

Style

The overall type of format to be applied, for example, whether percentages are shown as .4565 or as 45.65 %

Positives

Enabled with numeric types. Controls the appearance of positive values.

Negatives

Enabled with numeric types. Controls the appearance of negative values. For example, negative currency values may be show

Separate thousands

Enabled with numeric types. When checked, inserts a separation character to indicated thousands. The character used is determined by the current Windows locality settings, being a comma "," in the US and a period "." in various European countries.

Alignment

Left, Right and Center alignment. The Right alighment has the side effect of lining up decimal points when styles using a specified number of decimal places are used.

Decimal places

Enabled with numeric types. The number of digits to the right of the decimal point to display.

Width

The width of the column in characters.

Indent

The indent to apply in number of characters.

 

Style Options

 

Style options depend on the type of field. In general, style options are patterned on those found in Microsoft Office applications. Some field types have many style options while others have few options. Following are notes on the most popular options for field types that have many style options:

 

Floating point

<default> - Shows numbers without thousands separators and with as many decimal places are significant, truncating trailing zeros.

12.34 - Use the specified number of decimal places and other options.

12 1/3 (up to one digit) - Round to the nearest fraction using single digits in both the numerator and denominator. Examples: 6 5/7, 3 5/8 and 30 1/9.

12 17/50 (up to two digits) - Round to the nearest fraction using up to two digits in both the numerator and denominator. Examples: 5 21/50, 8 4/25, 3 3/4

12 1/2 (as halves), 12 1/4 (as quarters), 12 3/8 (as eighths), 12 5/16 (as sixteenths), 12 3/10 (as tenths), 12 34/100 (as hundredths) - Round to the nearest fraction using the specified denominator units.

1.23D+001 or 1.23E+001 - Scientific notation using "D" or "E" to indicate the exponential part.

1.23D+1 or 1.23E+1 - Scientific notation using "D" or "E" to indicate the exponential part with leading zeros trimmed from the exponent.

Currency

<default> - Same as floating point.

<no symbol> - Use the given number of decimal places without any currency symbol.

symbols - Use the given currency symbol to the given number of decimal places.

three letter codes - Use the given three letter SWIFT currency code.

Boolean

<default> - Use 1 for true and 0 for false. Other options provide for the usual True / False, Yes / No and other renderings of Boolean status.

Date and Time

<default> - American style month/day/year dates as in 3/09/2001.

Note that date and time field types store not only the date but also the time in all cases. Whether the date, the time, or both together are visible depends on the formatting.

Latitude

<default> - Standard decimal degrees where a minus sign indicates Southern latitudes and Western longitudes. Trailing zeros will be truncated.

12.3456 - Display using the given number of decimal places.

12º20.736 - Degrees with minutes and decimal fractions of a minute. A slightly dissonant format that nonetheless seems to be popular in certain GPS applications. It's used in some marine charts to note waypoints.

12º20'44.2" - Results in displays using degrees, minutes and decimal seconds formats.

N - The above styles are available in an N style, which appends an N, S, E or W to show North, South, East and West latitudes or longitudes. Otherwise, a minus sign will be used to indicate Southern latitudes and Western longitudes.

Note: Control the number of trailing digits after a decimal point with the Decimal Places option.

Percentage

<default> - Show percentages using percent symbol, for example, 52.01 %

0.52 - Show percentages as decimal fractions, for example, 0.456.

options - Other options repeat the various fractional styles available in floating point numbers.

 

Editing

 

Cells that are formatted in a given column formatting style will accept edits in any compatible style. For example, if a date and time column is formatted to show dates as 3/09/2001 and one enters 9 mar during the year 2001 the entry will be accepted and shown as 3/09/2001. In this case, Manifold knows that "mar" in date and time fields means March. If the or time is not explicitly given the system uses the current system year and time. To specify all parts of the data and time value, enter explicitly those parts desired. If one enters 9 mar 99 the entry will be accepted and shown as 3/09/1999.

 

Latitudes and longitudes expect that a single number with a decimal point, like 90.1234 is intended as a decimal degrees value. Other numbers in the generic form of '90 2 3'-type strings will be parsed as degrees, minutes and sections using the two or three numbers with white space or almost any characters in between. This makes it easy to enter degrees, minutes and seconds values for latitude and longitude without hunting all over the keyboard for obscure ways of entering the "degree" character.

 

Other Uses of Column Formats

 

Column formats specified in tables will be used in labels and in export.

 

·      Labels created from fields will use whatever formatting has been assigned to that field. See Creating Labels from Fields for examples.

·      When tables are exported to purely text formats such as .csv or .html, the contents of the table fields will be formatted as specified by table formats.

 

Specifying Languages for Columns

 

Language specification in text columns varies depending on whether Unicode or ANSI text is used. Unicode text columns can simultaneously contain characters from more than one language. ANSI text columns are usually restricted to one language in addition to (possibly) English. Character data contained in an ANSI text column will be interpreted by the system according to the code page associated with that column.

 

By default, ANSI text columns use the system code page. If desired, any ANSI text column in a table can be set to use a different code page by right clicking on the column head and choosing Language.

 

images\sc_column_language.gif

 

Notes

 

Why the strange fractional styles for numbers? These are often used to express measurements using English units such as inches. In the USA, for example, people routinely use measurements such as 8 3/16 inches.

 

Tech Tips

 

From the context menu for table columns (right click onto a table column head) choose Best Fit, Best Fit All or Best Fit Titles to set width of table columns automatically.

 

Users are sometimes surprised to see numbers they expect to be values like 1.1 appear in a query or other table as 1.09999942779541, even if a floating point type is used. That is a result of the fundamental architecture of computers combined with the choice of format style used.

 

Floating-point values have finite precision. Some values, such as 1, 2, or 0.25 can be represented precisely, but most can not. For example, neither 12.6 nor 13.7 can be represented precisely. To see what really hides behind a value shown as 12.6, set the format style of the relevant column to 12.34 and increase the number of decimal digits to 10. Try the same for 13.7.

 

If we subtract one approximation from another, as in the case of an SQL query, we get an approximation of the result so that an expected result such as 1.1 may appear as 1.09999942779541.

 

The default format style uses a Microsoft Windows API which makes some approximations, such as 12.6000..., look round but leaves others unaffected. There is some logic behind that API, but that logic is obviously not applicable to all cases. To provide fine control over desired appearance is one reason why Manifold allows setting the format style and the number of decimal digits explicitly.