Admin users can add custom fields in Calem. "Lookup" fields can only take values from a dropdown list or another table. They are great tools to avoid duplicate or invalid data.


1. Lookup and Dropdown

Calem supports two types of fields that take values from a predefined list. These fields are generally referenced as lookup fields. A lookup field can take a value from a list (Dropdown), or a table

  • A dropdown field takes values from values of a list. It works the best when the list of values is small. For example, the status of work orders is a list of a dozen or so values. A dropdown list is used for work order status.
  • A lookup field takes values from another database table. The table can store many rows. For instance, asset class (or  categorization) can have many values depending on the assets managed. The class (categorization) table can have tens, hundreds or thousands of values. Asset class (categorization) is a lookup table in Calem.

2. My Dropdowns

My dropdowns are the dropdown lists added by customers. These dropdown lists can be used in custom field design, just like a dropdown list shipped in Calem.

  • Menu path: "Admin module | Data design | My dropdowns
  • A custom field can be created to take values from "My dropdown".
See Admin Guide for more information about My Dropdowns.


3. Custom Lookup Field

When adding a custom field you may choose "Lookup" as field type. A "Lookup" type means that the custom field can only take values defined in a table such as "Asset" (called lookup table), or a dropdown list such as "WO priority", or "My dropdown" (a dropdown list added by your organization).

  • It is recommended that a lookup field be named with a suffix of "_id". For instance, an asset lookup field can be named as "link_asset_id" if the custom field is a lookup field to the asset table. A work order status field can be named as "wo_status_id".

The lookup form in Data Design provides the following menu items to help you pick a lookup table:

  • "Show All" – show all the lookup table and dropdowns.
  • "Table lookup" – show lookup tables only.
  • "Dropdown" – show dropdowns shipped by Calem.
  • "My dropdown" – show dropdowns added by your organization.

The form to create a lookup field:

The lookup form to select a source to lookup from:


4. Lookup Tables

Unlike "My Dropdowns", one cannot add a new lookup tables in Calem. There are some tips:

  • Use an existing lookup table if a fit is found.
  • There are five custom tables in Calem you may rename:
    • Menu path: Organization | Codes | Address | More | Lookup 0 to Lookup 4
    • Customize name (for instance, from "Lookup 0" to "OP-AREA").
    • Populate the table with fields
    • Use the table in your custom fields.
  • There are some lookup tables that may be re-purposed:
    • Country, State, City, Region and Zip (see the screenshot below)
    • Owner Type (Inventory | Codes | Owner Type)
    • Class (Purchase | Open | Class)
    • Route (Asset | Codes | Route) 
  • New lookup tables are added to Calem all the time.
    • Ask Calem support to add a new lookup table.
    • A lookup table and forms to manage them can be added by Calem for a fee.


 5. Dropdown Customization

Users with Admin module permission can customize dropdowns:

  • Menu path: "Admin | Data Design | Dropdowns" or "My Dropdowns"
  • Find the dropdown you need to work with. 
    • See next section to find the dropdown name for a dropdown field.
    • For instance, enter "category" to search for category dropdowns.
  • Right-Click the dropdown tree root in the right panel to add a dropdown value.
    • Enter a label and Id for the dropdown value, and press "Save" button to add a dropdown.
    • Follow the same naming convention for a dropdown Id.
      • For instance, work order category Id is named as "woc_preventive" for "Preventive" (label).
      • Your new category of "Operation" may have an Id "woc_operation".
    • Populate additional attributes for a dropdown Id.
      • For instance, work order category has attributes of PM (preventive maintenance) and CM (corrective or responsive maintenance) checkboxes. You might check one of the checkboxes if applicable.
    • "Fixed" checkbox indicates that a dropdown value is used internally for processes and should not be deleted.
  • Right-Click a dropdown value to edit it
    • You can edit the Id, label and attributes.
  • Modify the dropdown label
    • To modify for a different language pack, log out, then log into Calem with that language pack.
    • Repeat the steps above to modify the text for another language.
  • A dropdown Id not used in your organization may be deleted.
    • A dropdown with "Fixed" checkbox checked cannot be deleted.

6. Identify Dropdown Names

Dropdown names can be found from forms where they are used. We will use work order form as an example to find the dropdown name for work order category field.

  • Menu path: Work Order | Open WO | Click a WO to open its form
  • WO "TaskView" form is shown, click "Close" Tab.
  • "Category" field is shown (2)
  • Click "Customize" (3) to go to design mode
  • Find the "Category" field: the field name is "category_id", the dropdown is "cm_wo_category".