Column types

Here is a list of available column/data types:

Text types

    • Text: models a short piece of text (a few words) shown on a single line.
    • Name: special case of a Text type that represents the name of a person or place. 
    • LongText: models longer text content shown across several lines.

Numeric types - in the app, values of these types can be graphed.

  • Number: models an integer value.
  • Decimal: models a number with decimal precision.
  • Price: models currency values. The TypeQualifier field can be used to indicate a currency symbol ($ is the default).
  • Percent: represents percentage values.

Temporal types - in the app, these values are shown utilizing the timezone and presentation format of the client device.

  • Date: models a day (day-month-year).
  • Time: models a time within a day (hour-minute-second). You can set the "IgnoreSeconds" attribute to disregard seconds.
  • DateTime: models the day and the time.
  • Duration: models a period of time.

Change types - in some apps, it is important to record a timestamp or increment a counter automatically in a row when changes are made to other columns, and even _values_ within the columns. The change types provide this functionality. When the Type Qualifier is empty, these change types automatically update when _any_ other column value changes. However, they can be constrained to react to changes only on specific other columns by providing a Type Qualifier of the form: {"ChangeColumns":["Column1", "Column2"]}. Change types can be constrained to react to changes on certain values using this format in the Type Qualifier: {“ChangeColumns”:[“Column1”], “ChangeValues”:[“failed”,”error”,”urgent”]}.

  • ChangeTimestamp: shows when an entry was last edited. See how this works along with a Type Qualifier in the Store Inventory sample.
  • ChangeCounter: shows how many times an entry has been edited. See how this works without a Type Qualifier in the Interview Feedback sample.
  • ChangeLocation: will automatically populate with the current GPS location (where the change was made).

Enumerated types - in the app, fields of these types are constrained to having one of a fixed list of allowed values.

  • Yes/No: also known as a 'Boolean' type, these values display as Y or N in the app and have a 'slider' choice mechanism in input forms.

  • Enum: Allows the user to select a single value from a list of allowed values. See Dropdowns

  • EnumList: EnumList is identical to Enum, except it allows the user to select multiple values from a list of allowed values. See Dropdowns

  • Ref: are used to create relationships between one record and another. See References Between tables.
  • Color: color code entries in your app with a subset of 6 standard colors: Red, Yellow, Green, Orange,  Purple, and Blue.
    See how color coding works in the Project Plan sample.

  • Progress: show the progress of an entry by utilizing a ‘Harvey Ball’ ideogram. See how the progress function works in the Project Plan sample.

Communication types - in the app, values of these types can be tapped to launch communication.

  • Phone: models a phone number -- gives you the option to both call and SMS text through the app.  

    At present, AppSheet only automatically recognizes phone numbers in North American format. We hope to improve this in the future to recognize international phone numbers as well.

    You can overcome this limitation by manually setting the column type to Phone. Once you do this, AppSheet will treat the column as a phone number, and the column type setting will be preserved across schema regeneration.

    When your application includes both a home phone number and a cell phone number, you can use "UX" > "Format Rules" to distinguish between the two. For example, you might use the "fa-home" icon in front of the home phone number, and either the "fa-mobile" or "fa-bars" icon in front of the cell phone number. The latter resembles the signal bars on a cell phone.

  • Email: models an email address -- gives you the ability to send emails by clicking the email address. 

Geographic types - in the app, values of these types can be seen on a map.

  • Address: models a fully-specified postal address.

    Simple Address Field
    You can specify a complete address in a single worksheet column. Include the word "Address" in the column name to help AppSheet identify the address column. For example, you could name the column "Address" or "Customer Address".

    You can include two or more addresses in the same worksheet. For example, your worksheet might contain a “Billing Address” in one column and a “Shipping Address” in another column. 

    Enter the complete address including the street, city, state, country and postal code values. This helps ensure that the address geo-codes correctly.

    Automatically Computed Address Field
    You can store address information in AppSheet by creating adjacent columns in your worksheet and naming them appropriately. When you do this, AppSheet automatically recognizes that the adjacent columns form an address. For example, you can create adjacent columns in your worksheet, and name them “Street”, “City”, “State”, "Country", “Zip”. In this case, AppSheet will recognize that taken together these columns represent an address and it will create a "Computed Address” field that concatenates the values in these fields.

    Do not specify a column name containing the word "Address" in place of "Street". We interpret any column name containing the word "Address" to mean that the column contains an entire address. For example, a column name such as "Street Address" or "Home Address" is assumed to contain an entire address. This will prevent adjacent fields from being recognized as a multi-part address.

    If you require two lines of street information, you can name your columns “Street1”, “Street2”, “City”, "State”, "Country", "Zip”. If you require three lines of street information, you can name your columns “Street1”, “Street2”, "Street3", "State”, "Country", "Zip", and so forth.

    If you require an apartment, suite, or unit number in your address, you can name your columns “Street”, “Apartment”, “City”, "State”, "Country", "Zip”. You can also use "Apt", "Suite" or "Unit" in place of "Apartment".

    You can use "Province" in place of "State".

    You can omit "Country", but this may reduce the accuracy of the geo-coded address.

    You can use "PostalCode" or "ZipCode" in place of "Zip".

    You can include two or more addresses in the same worksheet. For example, your worksheet might contain both a “Home Address” and a “Work Address”. AppSheet recognizes the two addresses based upon a combination of naming and adjacency. Keep the “Home Address” columns adjacent to one another and likewise for the “Work Address” columns. Then name the columns to help AppSheet group them appropriately. For example, you might name the "Home Address" columns “Home Street”, “Home City”, “Home State”, "Home Country", “Home Zip”. You might name the "Work Address" columns “Work Street1”, “Work Street2”, “Work City”, “Work State”, "Work Country", “Work Zip”. You can use this approach to include three or more addresses in your worksheet, if necessary.

    Manually Computed Address Field
    You can populate an address column by manually combining the values of two or more columns. In your worksheet create a column and include "Address" in the column's name. For example, you might call the column "Customer Address". Add the worksheet as a table. In the Editor, go to the Data > Column Structure tab. Ensure the "Customer Address" field has a type of "Address". Assign the field an AppFormula such as CONCATENATE([Calle],",",[Ciudad],",",[Estado],",",[Pais],",'",[Postal]) where the field names in the CONCATENATE function represent the names of the street, city, state, country, and postal code fields.

  • LatLong: models a latitude and a longitude (eg: '48.5564, -122.3421'). Form fields for this data type can fill in the current location with a single click. See how location capture works in the Site Inspection sample.

Content types - in the app, values of these types are shown as inline content, or open in an external content viewer.

  • Drawing: creates a drawing pad in the app. 
  • Image: models .jpg, .png and .gif images. The values may be image URLs or names of files in the source file system of the spreadsheet. Please reference the section describing how to use files as images. Images are captured on the device using the camera or from the local camera roll.
  • Thumbnail: also models images, but instructs the app to expect small icons and thumbnails. Thumbnails are captured just like images.
  • Signature: models user signatures. These are captured using a touch-based signature pad and are stored as small inline images in the spreadsheet.
  • File: models any file content that can be viewed in a browser (typically used for PDF documents). There is no capture mechanism for files in the app.
  • Show: empty columns in your spreadsheet that serve the sole purpose of improving the presentation of data capture forms. There are six categories of show types:
    1. Page_Header: used to create a new page within the form
    2. Section_Header: used to create a new section within the same form page
    3. Text: used to show some descriptive text
    4. URL: used to show a clickable URL
    5. Image: used to show a static image
    6. Video: used to show an MP4 video


Other types
 
- these are meant for internal use only and should not be explicitly assigned by AppSheet users in the current version.

  • URL: a web address. There are two parts to a URL value: an address like "http://en.wikipedia.org" and optional link text like "Click here to visit Wikipedia."
  • App: navigate to another AppSheet app or to navigate to a different view in the current app Field type: App
  • MultiColumnKey: a composite field representing the combination of multiple fields for the purpose of a key.

 

Column Types Video Part 1: Text, Numeric, Temporal, Communication, and System Types 

 

Column Types Video Part 2: Change Types 

 

Column Types Video Part 3: Geographic Types


Column Types Video Part 4: Content Types (except 'Show')



Column Types Video Part 5: Enumerated (Enum Types)



Column Types Video Part 6: Show Types



Have more questions? Submit a request

0 Comments

Article is closed for comments.