Types are optional
In Fieldbook, you don't have to set the type of a column when you create it, and columns don't have to have just one type of data. By default, columns are “generic”, and like spreadsheets, they can hold any type of data—text, numbers, dates, currency, percents, etc. These types are automatically recognized so that you can use them in sorts, filters and formulas.
Setting a type
You can add a column type in order to:
- Validate data (only numbers are allowed in a Number column)
- Make sure input is interpreted properly (in a Text column, a zip code like 01234 will not be turned into a number)
- Make input easier (in a Currency column, the currency symbol will be added automatically)
Just use the “Choose column type...” option in the column menu.
Text columns treat everything as text, even if it looks like a number, date, etc. This is good for:
- Zip codes, where you want to keep leading zeroes
- Tracking numbers or other large ID numbers
- Codes that might be interpreted as dates
Number columns require numeric input. If you try to enter something that's not a number, you'll get an error.
Numbers can be formatted as:
- 1,234.5 (decimal point or dot)
- 1.234,5 (decimal comma)
Your format is based on your locale settings.
Right now, numbers are displayed with up to three decimal places.
Currency columns require a monetary value. You can specify the currency explicitly with a symbol like $ or €, or you just can enter a number and it will get the default currency configured on the column.
Right now we support:
- $ (Dollar)
- £ (Pound)
- € (Euro)
- ₹ (Rupee)
- ¥ (Yen)
- ₺ (Turkish Lira)
Currency values will also use decimal point or comma depending on your settings.
Currency values display with either no decimal places (whole numbers) or exactly two decimal places, as needed.
When currency values are used in formulas, the result is usually a currency as well, e.g.:
- $10 + $20 = $30
- £100 * 5 = £500
(However, adding different currencies is not recommended, as we don't currently calculate exchange rates.)
Percent columns require a percentage value. These are just numeric values formatted in a different way. If you enter a number, a percent sign will be added automatically.
When percent values are added, they result in another percent; however, a percent times a number or currency will lose the percent:
- 10% + 20% = 30%
- 50% * 30 = 15
- $100 * 5% = $5
Date columns require a date. The easiest way to select a date is using the date picker by clicking on any row in a date-type column.
Dates can also be input manually in a wide variety of formats, for example:
- 10/21 (defaults to current year)
- October 21st, 2015
- Oct 21 2015
- 21 Oct 2015
The first two examples above (with 10/21) assume month/day format, which is the default in the US. In other locales, day/month is the default (21/10). You can change this under your locale settings.
Tip: In a Date column, you can even enter "today", "tomorrow" and "yesterday".
Right now, no matter how dates are input, they are always displayed as numbers separated by slashes (6/6/2017).
Dates can be used in formulas as well:
- "10/21/2015" + 7 = 10/28/2015 (add or subtract a number to calculate days before/after)
- "10/21/2015" - "10/1/2015" = 20 (subtract two dates to get the number of days between)
Day of year
Day-of-year columns represent a date independent of the year, such as an anniversary or a birthday. (The only way to get a day-of-year value is in a day-of-year column, since any day entered in a generic or date column will be interpreted as a date in the current year.)
Checkbox columns contain true/false (“Boolean”) values. They're good for yes/no or on/off status columns like “Done”, “Active” or “Paid”.
You can use checkbox columns in filters:
And in formulas:
Pick list columns let you set a list of acceptable values. This is good for:
- Status values: Red, Yellow, Green
- Bucketing values: High, Medium, Low
- Stages of a process: New, Working, Review, Done
- Categories: Bug, Feature, Idea
A pick list cell will have a dropdown with the items in it, in the order that you specify. You can also type values or type to filter a long list. Any value not on the list is an error.
Right now you can only choose one value per cell. If you want to pick multiple values, use a linked sheet.
When you sort or group on a pick list column, it uses the order of the list (High, Medium, Low), not alphabetically (High, Low, Medium). When grouped on a pick list column, you can reorder the list by dragging the groups around. You can also edit the list directly when you set the column type.
Email and Website
Image and File
Image and File columns let you upload attachments. Image columns will display the image inline:
File columns show the attachment with its filename:
More about attachments: Images and file attachments
Any columns with a type will validate its input. If you're typing into a cell, invalid input will not be allowed.
Same filling out a form:
If you paste a block of text, it's possible that some of the input is valid and some is not. Invalid input will go into the sheet, but will be highlighted as a red error cell. (You can filter for errors in a column using the iserror() function.)
In the API, invalid input is rejected with an HTTP 400 error code.
Changing column type
You can set or change a column type at any time, even if it has data in it already. Fieldbook will do its best to convert the column to the new data type. (Any cells that can't be converted will be highlighted in red as error cells.)