Tracker Column Types
Choose the format to best organize your data
MangoApps offers 18 different types of columns to help you keep your data organized.
Universal Column Options
When configuring various data types for columns, there are several customizable fields that are consistent across multiple column types. The following fields are considered universal for all column types:
Label: This field will always be required as it serves as the column name. Character limit 250.
Description: This field will always be optional. The description of your column will show as a ⓘ icon next to your column name in the tracker and next to the corresponding field in the form. This description can be up to 500 characters.
Column Type: This displays the current column type. The column type can be changed later within the Column Menu.
Default Value: Optional. By setting a value, this field will be pre-populated with what is entered here.
Behavior: This field is optional. Choose from the following:
None - The column will act as normal.
Required - Select this option to make a column required when completing an entry. This will show as a red asterisk next to the field name in a form. Data will not be entered into the tracker as a new entry unless this field is completed.
Read Only - This column will only be editable by admins and the tracker creator. All other users will only be able to read the entry and activity of the entry.
Required & Unique (Primary Key) - This property ensures that the information entered into this column is both mandatory and unique. It is particularly useful for creating searchable SKUs or unique terms in large databases. This value can be searched for within your domain search bar.
Changing the behavior value may take up to a minute to take effect after saving, depending on the numbers of rows in the tracker.
Show field to: Choose to show this column to admin users or all members with access to the tracker. This is useful for keeping data together without all users being able to see it.
This guide will highlight the additional fields which will appear that differ between column types.
Amount
This column type will show the additional menu options:
Currency - Required; default is USD. You can choose to display the following denominations: Dollar, Euro, Rupee, Pound, Francs, Yen, or Pesos
Field Range - Optional. Set a minimum value and/or a maximum value for the accepted data type. Negative values are accepted.
Auto Number
This column type will automatically assign a number to an entry starting with a designated starting number and increasing incrementally. You have the option to customize the display format of the autogenerated number. This column type can only have the behavior of Required & Unique (Primary Key).
This column type will show the additional menu options:
Display Format - Optional. To customize the auto number, define the prefix, suffix, starting number, and/or number format. You can use any combination of numeric values, text, or symbols in the prefix and suffix fields.
For example,
Prefix: 'SKU#'
Number format: '0000'
Suffix: 'd'
The auto number will then generate values between "SKU#0001d" to "SKU#9999d"
Editing the display format will only affect future entries. Once there are entries in the column, the starting number and number format cannot be changed.
Each Tracker can have only ONE auto number column. Once an auto number column is added to a Tracker, the auto number option will no longer be available as a new column option. This ensures that there is only one auto number column per Tracker since entries for this column are system generated.
While using the same auto number format in other Trackers is possible, it's crucial to understand that the generated numbers will be unique for each Tracker. This means that even if the format remains the same, the actual numbers assigned will be distinct for each Tracker.
Check Box
A checkbox column is ideal when you want to be able to select multiple options from a present list of options/choices. In the edit mode you can drag & drop to re-order the options/choices and choose from a list of 40 different colors. Up to a max of 10 options/choices can be added for a checkbox column type.
This column type will show the additional menu options:
Field Choices - At least 2 choices are required. To add more choices, click the +Add a choice option at the bottom of your current choice list. Choices can be clicked and dragged to rearrange how they will appear in the entry and form menu.
Optionally, toggle on Color to associate a color with the choice options.
Date
A date column allows you to easily enter a date and optionally a time into a cell. When editing a date time, you will be presented with a calendar widget that makes it easy to select a specific date. There is an option to set a current date as the default value for the column
This column type will show the additional menu options:
Date Format - Required. Choose the format of the date. The format dropdown allows for the inclusion of hours, minutes, seconds, and even time zone.
Range - Optional. Set the calendar date range allowed for entries. You can choose beginning start date and an ending start date for acceptable entries.
Default Value or Date of Submission - Optional. Choose a default date or choose to use the entry submission date as the default value for the column if no entry is made to the field. The user will still be able to edit the field if they have edit permission.
Dropdown
A dropdown column is ideal when you want to be able to select a single option from a preset list of options/choices. You can select the desired option from the dropdown menu, or start typing in the drop down search field to narrow down the list of options.
This column type will show the additional menu options:
Field Choices - At least 1 choice is required. Drag & drop to re-order the options/choices and choose from a list of 40 different colors. Up to a max of 100 options/choices can be present in the dropdown. For > 100 options/choices consider using the ‘Tracker Lookup’ column type to pull from a tracker with more values.
Email
An email text column is intended to store a single email address per email entry field. This column type uses the universal column options.
Feedback
Create a series of options for a dropdown menu. This option allows you to assign icons for choices as opposed to the normal Dropdown column type.
This column type will show the additional menu options:
Field Choices - At least 2 choices are required and up to 5 choices can be created. Choose from a list of over 150 different icons. This column type will come pre-populated with the following choices: Awesome, Good, Okay, Down, Awful.
File Attachment
Attach up to 10 files to an entry. Clicking on the attachment opens the file in the file viewer. This column type uses the universal column options.
Formula
Formula columns allow you to compute a value in each row/entry based on other cells in that same row/entry. For example, if you have a tracker of product orders, you can create a column that computes the Total Cost for each record based on the Price and Quantity.
A formula can be composed of functions (e.g. SUM, AVERAGE, IF), operators (e.g. >, <, =), and other columns from the same tracker
This column type will show the additional menu options:
Formula - Enter your formula here. See below for tips on configuring your formula into the tracker.
Format - Optional. By default, Decimal will be chosen. You can choose a format for your formula result to be a decimal, integer, currency, or percentage. Depending on your choice, you will be given further precision options.
Configuring Formulas
Input your formula into the Formula text box just as you would in a spreadsheet. However, use the names of fields instead of cell ranges, such as Q1, Q2, Q3, etc., as shown in the graphic above.
As you begin typing your formula, you'll receive suggestions for functions and fields. You can click on these suggestions to incorporate them into your formula.
Note that in trackers, formulas are applied to the entire column. This means that the same formula will be applied to every entry in that column.
Basic calculations using a formula
Adding
You can add multiple columns together by using SUM function and columns separated with a ‘,’ symbol
For example, several columns are added together to find the final total: SUM( %{legal_fees}% , %{retainer_fees}% , %{miscellaneous_fees}% )
Multiplying
You can multiply columns together by using MULTIPLY function and columns separated with a ‘,’ symbol
For example, two columns are multiplied together to find the final value: MULTIPLY( %{hourly_fees}% , %{hours_billed}% )
Dividing
You can divide columns together by using DIVIDE function and columns separated with a ‘,’ symbol
For example, two columns are divided together to find the final value: DIVIDE( %{total_fees}% , %{hours_billed}% )
Comparing numeric, date, & text strings using a formula
Compare numeric, date & text column using the IF function
IF function will return one of two values depending on whether the logical argument is true or not: IF ( ‘logical argument)”, “value1”, “value2”)
In the following examples, two columns are compared with different logical operators together
IF(‘%{column1} > %{column2}’, “Yes”, “No”)
IF(‘%{column1} == %{column2}’, “Yes”, “No”)
IF(‘%{column1} != %{column2}’, “Yes”, “No”)
IF(‘%{column1} < %{column2}’, “Yes”, “No”)
IF(‘%{column1} + %{column2} > 5’, “Yes”, “No”)
IF(‘%{column1} – %{column2} < 5 ‘, “Yes”, “No”)
IF(‘%{column1} * %{column2} > 5’, “Yes”, “No”)
IF(‘%{column1} / %{column2} < 5 ‘, “Yes”, “No”)
IF(‘%{column1} > %{column2}’, ‘%{column1}’, ‘%{column2}’)
IF(‘%{column1} < %{column2}’, ‘%{column3}’, ‘%{column4}’)
Date field formula
Calculate the duration between two date columns using the DATETIME_DIFF formula
For example, two date columns are used to calculate the duration in days: DATETIME_DIFF( %{end_date}% , %{start_date}% , ‘days’)
Office Location Look Ahead
This column allows you to select an office location from a pre-generated list of locations registered within your network. This column type uses the universal column options.
This column type has modified options for the following field:
Default Value: Optional. The default value can be set to the user's location or a specific office location within the network.
Phone Number
This column type allows you to input and format phone numbers. In a form, users can use the flag dropdown menu to select a country code. This column type uses the universal column options.
This column type has modified options for the following field:
Default Value: Optional. You have the ability to set a default number and country code.
Radio Box
A radio box column is ideal when you want to be able to select a single option from a laid out list of options/choices (like the multiple choice question type in surveys).
This column type will show the additional menu options:
Field Choices - At least 1 choice is required. To add more choices, click the +Add a choice option at the bottom of your current choice list. Choices can be clicked and dragged to rearrange how they will appear in the entry and form menu.
Optionally, toggle on Color to associate a color with the choice options.
Rich Text
A rich text column accepts multiple lines of formattable text for each cell. You can enter new lines into a rich text field, customize emphasis typography, create bulleted list, add hyperlinks, and more. This column type uses the universal column options.
Customization to text will not be shown in the table view. However, it will be visible when viewing the entry details.
This column type has modified options for the following field:
Default Value - Optional. The default value field offers a rich text box for customizing the entered value.
Team Look Ahead
A team look ahead column allows you to select one team from a pre-generated list of teams within the network. The Default Value field for this column type allows you to select the default team or you can check the box to auto-fill the Current Team the tracker has been added to as the default value.
This column type will show the additional menu options:
Look Ahead Data - Required. Choose whether the look ahead list will populate the entry user's teams or all teams within the network.
Allow new additions on the fly - Optional. When enabled, users can input a new field choice while creating an entry if the choice is not already available in the look ahead list.
Text
A text column accepts unformatted lines of text for each cell. This column type is best for short pieces of text. Users can enter up to 5000 characters of any type (unless limited by the below listed options).
This column type will show the additional menu options:
Allow Numeric Values - Optional. When disabled, numeric values (0-9) will not be allowed.
Number of Characters Allowed - Optional. Add a minimum and maximum amount of characters allowed for the field.
Row Height: Required. Choose the starting row height as a single line text box or more. Only 5000 characters are allowed per text field, despite the number of lines added to the text box.
Tracker Lookup
Tracker look-up column types play a pivotal role in retrieving and integrating column values between different trackers, facilitating efficient data management.
This column type will show the additional menu options:
Lookup Tracker - Required. Select a different tracker from within the workspace the current tracker is located.
Lookup Column - Required. Select the column name from which the data will be referenced. Any column selected as a Lookup Column cannot be deleted from the parent tracker.
The selected column MUST be marked as Required & Unique within the parent tracker in order to access the Include Additional Columns menu covered below.
Include Additional Columns - Optional. Select up to 5 additional column whose values will be fetched when the selected Lookup Column is called.
In order for this field to populate and execute successfully, the following specifications must be followed:
Excluded Column Types:
System columns, formula columns, file attachment columns, and those with Admin Only visibility are NOT available for selection.
Visibility Settings:
Only columns with Everyone visibility from the lookup tracker are displayed in the checkbox list for Include Additional Columns.
Exclusion of Specific Columns:
The column selected as the Lookup Column will not be listed, nor will other tracker reference columns from the parent tracker.
Child Tracker Column limitation:
The Allow multi-select option for the destination tracker column for the Tracker Lookup type needs to be OFF.
Handling Missing Values:
If a value is not found in the looked-up tracker row, the corresponding field in the main tracker remains blank, ensuring accurate data representation.
Tracker Reference Values:
The selected additional column values are fetched through the associated tracker lookup columns, automatically populating corresponding "Additional Columns" in the main tracker. These values are set to read-only to maintain data integrity.
Allow multi-select - Toggle this option ON to allow users the ability to enter more than one value.
Allow new additions on the fly - Toggle this option ON to allow users to enter in values, even if they do not appear from the look ahead list.
URL
This column type allows for the entry of URLs and hyperlinks. This column type uses the universal column options.
This column type has modified options for the following field:
Default Value - Optional. Enter in a default URL. You also have the option to set display text for the URL instead of the link, up to 100 characters.
User Look Ahead
A user look ahead column allows you to select one or more users from an automatically generated list of users. A user look ahead field can be set to allow only one user or to allow multiple users.
This column type has modified options for the following field:
Default Value -
This column type will show the additional menu options:
Look Ahead Data - Required. Select whether you want the look ahead data to include all network users, only members of the current team (in which the tracker is located), or the entry user's team members.
Allow new additions on the fly - Toggle this option ON to allow users to enter in values, even if they do not appear from the look ahead list.
Allows multi-select - Toggle this option ON to allow users the ability to select more than one value.
Include Additional Columns - This option enables admin users to incorporate extra fields that will automatically populate with information retrieved from the entered user's network profile.
Each tracker can only have one User Look Ahead column that includes additional profile fields.
Users can select up to a maximum of 5 additional profile fields from the list. Both system and custom profile fields are available in the list for selection. Fields whose User Controlled Visibility cannot be altered by users will populate but cannot be edited within the tracker.
@mention the user - Enable this setting to @mention the user who is entered in the column. Each record in the tracker must be setup to have its own newsfeed for this setting to be applicable. For details on changing the news feed settings for trackers, please visit Tracker Menus.
Last updated
