Creating a Custom Table

From ServiceNow Wiki
Home > Build > Applications > Creating a Custom Table
Jump to: navigation, search
Note
Note: This article applies to Fuji. For more current information, see Create a Table at http://docs.servicenow.com

The ServiceNow Wiki is no longer being updated. Visit http://docs.servicenow.com for the latest product documentation.


1 Overview

You can create custom tables as well as application menus, modules, and security rules that allow users to work with data in the tables. You can also create service catalog record producers directly from a table record. Creating tables is a core part of creating an application (see Creating Custom Applications).

You must have the admin role to create custom tables.

2 Creating Tables

Create or modify a table from the Table form. In versions prior to the Fuji release, the form does not have sections, and the Application field and the fields in the Application Access section are not available.

  1. Navigate to System Definition > Tables.
  2. Click New.
  3. Define the table by completing the fields on the form (see table).
  4. In the Columns section, use the Table Columns embedded list to add columns to the table.
  5. In the Controls section, define additional table options (see table).
  6. In the Application Access section, define the scope protection for the table.
  7. Click Submit to create the table, or click Cancel to close the Table form without creating a new table.
Sample table record

Field Description
Label Enter a unique label for the table. The label appears on list and form views for the table. Updating the Label field also updates the label record in the language file for the current language. See Field Labels.
Name Edit the table name, which is automatically populated based on the table label and a prefix as follows:
  • For a table in a scoped application, the name is prefixed with a namespace identifier to indicate that it is part of an application (starting with the Fuji release).
  • For a table in a global application and for versions prior to Fuji, custom tables have a name prefixed with the string u_ to indicate this is a custom table. For example, if you enter Marketing Event as the table label, the table name defaults to u_marketing_event.

You cannot modify the prefix; however, you can modify the rest of the table name. The name can contain only lowercase, alphanumeric ASCII characters and underscores (_).

Extends Table [Optional] Select the table to extend. Extending a base table incorporates all of the fields of the original table and creates system fields for the new table. You can extend tables that are marked as extensible if they are in the same scope or if they allow configuration from other scopes.

This option is available only when creating a table.

Application [Read only] Displays the application associated with this table. If you are working on an application or are creating a table from an application record, the field defaults to that application. Otherwise, the field defaults to Global. Any records that are created from the table record, such as modules and security rules, are assigned to this application by default. See Selecting the Current Application.
Create module [Optional] Select the check box and then complete the Add module to menu field to create a list module in the application menu.

This option is available only when creating a table.

Add module to menu Select an existing menu or select Create new and enter a new menu name. This option is available only when the Create module check box is selected.
Columns
Column label Define a unique label for the column. The label appears on list headers and form fields for the column.
  • When you update the Column label field, the system also updates the label in the language file for the current language. See Field Labels.
  • When you create a new column, the column name is automatically populated based on the label. The name is automatically prefixed with u_ to indicate that it is custom. For example, if you enter Activity Description as the column label, the column name defaults to u_activity_description.
Type [Mandatory] Define the field type for the column. See Introduction to Fields. To preserve existing data, only change fields between the same basic type (for example, Choice and String). A warning appears if a change to a custom field will result in data loss. For a base system field, you cannot make a change that will result in data loss.
Reference Make the field into a reference field by entering the referenced table name.

Note: Dynamic reference creation is enabled for this field. So, if you enter a table name that does not match an existing table, a new table is created when you save changes to the current table record. If the current table has a module in the application navigator, then a module for the newly created table is automatically created in the same application menu.

Max length [String fields only] Limit the length of the field. A length of under 254 appears as a single-line text field. Anything 255 characters or over appears as a multi-line text box.

Note:

  • You can only change this value if the Type of the field is String. Changes for any other type of field are ignored.
  • Users on an Oracle instance cannot increase the maximum length of a string field to anything greater than 4000 through the application UI because this requires the CLOB datatype in Oracle. To increase beyond this size, log an incident with ServiceNow Technical Support to request the change.
  • To prevent data from being lost, only decrease the length of a string field when you are developing a new application and not when a field contains data. A warning appears if a change to a custom field will result in data loss. For a base system field, you cannot make a change that will result in data loss.
Default value Specify the default value of the field for any new record. Ensure that this value uses the correct field type. For example, an integer field can use a default value of 2 but cannot use a default value of two. These values can be overridden with dictionary overrides.
Display Indicate whether this field is the display value for reference fields (appears on records that reference this table).

Note: This option does not control whether or not this field is displayed on lists or forms.

Controls
Extensible [Optional] Select the check box to allow other tables to extend this table. Clear the check box to prevent the creation of additional child tables; existing child tables remain unchanged.
Live feed [Optional] Select the check box to enable record feeds for the table (these are called document feeds in versions prior to Fuji). This option adds the Show Live Feed button (Live company feed.png) in the form header.
Auto-number [Optional] Select the check box, and then define the number format to add an auto-numbered field to the table. The check box is available only when a number format does not exist for the table. Otherwise, you can edit the existing number format.
Create access controls [Optional] Select the check box and then complete the User role field to create basic security rules for the table.
User role Enter a new name or select an existing user role that is required to access this table. This option is available only when the Create access controls check box is selected.
Application Access
Can read Select the check box to allow script objects from other application scopes to read records stored in this table. This option offers runtime protection. For example, a script in another application can query data on this table. You must first select read access to grant any other API record operation.
Can write Select the check box to allow script objects from other application scopes to modify records stored in this table. This option offers runtime protection. For example, a script in another application can modify a field value on this table. This option is available only when the Can read check box is selected.

Clear the check box to prevent script objects from other application scopes from modifying data stored in this table.

Can create Select the check box to to allow script objects from other application scopes to create records in this table. This option offers runtime protection. For example, a script in another application can insert a new record in this table. This option is available only when the Can read check box is selected.

Clear the check box to prevent script objects from other application scopes from creating records in this table.

Can delete Select the check box to to allow script objects from other application scopes to delete records from this table. This option offers runtime protection. For example, a script in another application can remove a record from this table. This option is available only when the Can read check box is selected.

Clear the check box to prevent script objects from other application scopes from deleting records from this table.

Allow access to this table via web services Select the check box to allow users to make inbound web service queries to this table. This option offers both design-time and runtime protection. The user performing the query must have the correct permissions to access this table, even when this check box is selected.

Clear the check box to prevent users from making web service queries to this table.


Allow configuration Select the check box to allow applications from other application scopes to create configuration records for this table that change its functionality. For example, an application designer can select this table from the Tables list on business rules, client scripts, or UI actions. This option offers design-time protection. For more information about creating configuration records for an application in a private scope, see Application Access Settings.

Clear the check box to prevent application designers from selecting this table when creating configuration records.

3 Adding Columns

You can add database columns (fields) when creating a new table or when editing an existing table. You can add fields on tables that are in the same scope as the field and on other tables that allow applications in another scope to add fields on them (starting with the Fuji release). To learn more about creating fields, see Creating New Fields.

To add columns from the table record:

  1. Navigate to System Definition > Tables.
  2. Create a new table or open an existing table record.
  3. In the Table Columns embedded list, double-click Insert a new row.
  4. Define the column by completing the fields in the row (see table).
    • [Existing tables only] You can create a column with advanced settings or create a column on a table in a different scope by clicking New (starting with the Fuji release). See Modifying Dictionary Entries.
      In versions prior to Fuji, you can create a column with advanced settings by right-clicking the form header and selecting Create Advanced Column.
    • The Table Columns embedded list provides list filtering, searching, and sorting in addition to standard embedded list functions.
  5. Repeat steps 3–4 for each column being added.
    • To delete a field, click the red X beside the row. This option is available only for custom fields.
    • To save column changes, save the form. See Using Embedded Lists.
  6. Click Submit or Update.
  7. [Optional] To customize the form layout for the new fields, open the table record, click the Layout Form related link (Personalize Form in versions prior to the Eureka release), and then configure the form.
    New fields are automatically added to the default form view. If a default form view already exists, new fields are added at the end of the first section.
  8. [Optional] To customize the list layout for the new fields, open the table record, click the Layout List related link (Personalize List in versions prior to the Eureka release), and then configure the list.

3.1 Global Default Fields

When you create a new custom table, several fields appear in the Table Columns embedded list. For all tables, required system fields are added automatically. You cannot delete or modify these fields.

For tables that extend another table, fields on the parent table also appear on the Table Columns embedded list for the current table. If you modify these fields, remember that all changes to fields on the parent table also affect all child tables, not just the current table.


These required system fields are added to all tables:

Field Type Description
Class [sys_class_name] System Class Name If the table is extensible, a string field that indicates which child table contains the record.
Created [sys_created_on] Date/Time A time-stamp field that indicates when a record was created.
Created by [sys_created_by] String A string field that indicates the user who created the record.
Sys_id [sys_id] Sys ID The unique record identifier for the record.
Updates [sys_mod_count] Integer A numeric field that counts the number of updates for this record since record creation.
Updated by [sys_updated_by] String A string field that indicates the user who most recently updated the record.
Updated [sys_updated] Date/Time A time-stamp field that indicates the date and time of the most recent update.

4 Creating Modules for Tables

Modules allow users to access the table from the application navigator. For more information about modules, see Creating Modules. To learn about mobile device modules for instances that use the smartphone interface, see Defining Smartphone Application Menus and Modules.

When you are creating a table, you can quickly create a default module for the standard browser and the mobile UI.

  1. In the table record, select the Create module check box. The Add module to menu field appears.
  2. Select an existing menu or select Create new and enter a new menu name.
    • If you are working on an application, the primary menu is selected by default.
    • If you are not working on an application, Create new is selected and the menu name defaults to the table label.
  3. Define the table and save the record.
    • A new module is created with the following values:
      • Table: current table
      • Title: plural of the table label
      • Link type: List of Records
      • Application menu: selected menu
      • Device type: Any (starting with the Dublin release for instances that do not use the smartphone interface)
    • If you selected Create new in step 2, a new application menu is created. The Default device type is set to Any (starting with the Dublin release for instances that do not use the smartphone interface).
    • For instances that use the smartphone interface, a new mobile module and a new mobile application menu are created.
Create a default module


To create additional modules, for example, a filtered list view:

  1. Navigate to System Definition > Tables and open the Table form.
  2. Scroll down to the Modules related list, and click New.
  3. Define the module by completing the Module form. See Creating a Module.
    • The Table field defaults to the current table and the Link type defaults to List of Records.
    • The Device type field defaults to Any (starting with the Dublin release for instances that do not use the smartphone interface).
    • You must enter the Application menu and Title.


To create additional modules for the mobile UI, for instances that use the smartphone interface only:

  1. Navigate to System Definition > Tables and open the Table form.
  2. Scroll down to the Mobile Modules related list, and click New.
  3. Define the module by completing the Module form. See Defining Smartphone Application Menus and Modules.
    • The Table field defaults to the current table.
    • You must enter the Application menu and other module information.

5 Adding Record Numbering

You can quickly create a number field and define the number format for records on the table. To learn about additional numbering options, see Managing Record Numbering.

To add record numbering to a table that does not have a number format defined:

  1. Navigate to System Definition > Tables.
  2. Create a new table or open an existing table record.
  3. Select the Auto-number check box. The number format fields appear on the form.
    Note: The check box is available only when a number format has not yet been defined. You can define only one number format per table.
    Add record numbering

  4. [Optional] Modify the number format by updating the fields (see table).
  5. Click Submit.
    • The number format is automatically created for the table.
    • If an auto-numbered field does not already exist, a new field is automatically created on the table with the following values:
      • Label: Number
      • Name: u_number
      • Default value: javascript:getNextObjNumberPadded();
Field Description
Prefix Enter a prefix for every number in the table (for example, INC for Incident). The default value is the first three letters of the table label.
Number Enter the base number for this table (default value is 1000). Record numbers are automatically incremented, and the next number is maintained in the Counter [sys_number_counter] table.

If you set the base number to a value higher than the current counter, the next record number uses the new base number. Otherwise the next record number uses the current counter. The counter does not reset to a base number lower than itself.

Number of digits Enter the minimum number of digits to use after the prefix (default value is 7).
  • Leading zeros are added to auto-numbers, if necessary. For example, INC0001001 contains three leading zeros.
  • The number of digits can exceed the minimum length. For example, if Number of digits is 2 and more than 99 records are created on the table, the numbers continue past 100 (such as INC101).

Warning: Changing this field may update all number values for existing records on a table. Take care when changing this field on a production instance. See Renumbering Records.

Note
Note: To change the default values for new number formats, change the Default value field on the system dictionary record for the Number or Number of digits field. These fields are on the Number table.


5.1 Removing Record Numbering

To remove record numbering from a table, delete the number format and the auto-numbered field.

  1. Navigate to System Definition > Number Maintenance.
  2. Click a table name to open the number record for that table.
  3. Click Delete.
  4. Navigate to System Definition > Tables and open the Table form.
  5. Click the red X beside the auto-numbered field.
  6. Click Update.

6 Creating Security Rules for Tables

You can quickly create basic security rules for a table. To learn more about security rules, see Using Access Control Rules.

To create security rules from the table record:

  1. Navigate to System Definition > Tables.
  2. Create a new table or open an existing table record.
  3. Select the Create access controls check box. The User role field appears on the form.
  4. Enter a new name or select an existing user role that is required to access the table.
    If you are working on an application or a table that is part of an application, the default user role for the application is automatically populated. Otherwise, the field is automatically populated based on the table label.
    Add table security

  5. Click Submit or Update.
    • If the user role does not match an existing role, a new role is created.
    • Security rules are created that grant the user role full access to the table—read, write, create, and delete.
  6. [Optional] Reopen the table record and scroll down to the Access Controls related list to create new or modify existing access control list (ACL) rules.
Note
Note: You can repeat the procedure to grant full access to a different role. The new role is created and the original role is replaced for every ACL on the table. The original role is not changed for ACLs on other tables or objects, and any other roles or scripts on the ACLs are not changed.


7 Extending a Table

You can create a new table that stands alone or that extends a base table. Extending a base table incorporates all of the fields of the original table and creates new fields for the new table. This inheritance is used to create subcategories of data. Examples include the Incident, Problem, and Change tables, which are all subcategories of the Task table.

To extend a table, select the table to extend in the Extends Table field on the table record. This option is available only when you are creating a table.

Note
Note: For a table that extends another table, the Table Columns embedded list shows columns on both the child table and the table it extends. To improve sorting and filtering, consider customizing the list to add the Table field. You cannot change the table for a field. You can add columns only to the current table.


Customize Table Columns list to sort fields by Incident or Task

To make a table extensible:

  1. Navigate to System Definition > Tables.
  2. Click the table that you want to extend.
  3. Select the Extensible check box.
    The table is now available in the Extends Table field.
Note
Note: You cannot extend system tables (such as sys_audit) or database view tables.


7.1 Example: Enabling Workflows for Tables that Extend Task

To create a table of task records, similar to the Incident, Problem, or Change table, create a new table that extends the Task table. To allow workflows to operate on the table:

  1. Navigate to System Definition > Tables and open the new table record.
  2. Right-click the form header and select Show Dictionary Record.
  3. Add the following to the attribute:
hasWorkflow
The new table now has fields from the Task table and supports workflows.

7.2 Example: Creating a New CMDB Class

Each CMDB class is its own table, so creating new classes requires creating new tables.

To create a new class (such as Laptops or Thin Clients):

  1. Create a new table (see following table for specific values).
  2. [Optional] If you want other CMDB classes to extend the new one, select the Extensible check box. You can also select this field after the table is created.
  3. Use the Table Columns embedded list to add any class-specific columns. For example, the following image shows a way to create a new CMDB class for laptops.
    Example: CMDB class for laptops

  4. Click Submit.
  5. Add any related lists. Refer to the existing classes (such as Workstations) for examples.
  6. Position the new module in the correct location in the application navigator.
    In the Laptops example, the new class belongs next to the Workstations module, under the Base Items heading.
    1. Right-click Configuration in the application navigator and select Edit Application.
    2. Locate the Workstations module in the list and note the value in the Order column.
    3. Open the new Laptops module and give it an order value one number greater or less than the order number of the Workstations module.
    4. Click Update.
Field Description
Label Enter the name of the new class (such as Laptops or Thin Clients).
Name Preface the name of the table with u_cmdb_ci_ to make it similar to the other CMDB classes (for example, u_cmdb_ci_laptop).
Extensible Select the check box to allow other tables to extend this table.
Extends Table Select the parent table for the new class. For example, if the new class is Laptops, which is a subclass of Computers, select the cmdb_ci_computer table. If the new class is a top-level class, select the cmdb_ci table.
Add module to menu Select the check box and then select the Configuration application to create a list module for the new class in the same application with the other classes.

8 Creating Record Producers for Tables

To save time, you can create service catalog record producers directly from a table record (starting with the Dublin release).

To create a record producer from a table record:

  1. Navigate to System Definition > Tables and open the table record.
Note
Note: When using a workflow with a Record producer, set the condition to Run the workflow.
  1. Under Related Links, click Add to Service Catalog.
  2. Complete the Name, Short Description, and Category fields as you would for service catalog items.
  3. Use the slushbucket to select the fields and the order in which you want them to appear. To use container variables, select |- container start -| and |- container end -|.
    Record producer

  4. Click Save and Open to open the record producer and define additional options. Alternatively, click Save to return to the table record.
    • A record producer is created with these values:
      • Table name: table record opened in step 1
      • Name, Short Description, and Category: information entered in step 3
    • A variable is created for each of the selected fields with these values:
      • Name: Column name of the field
      • Type: variable type that corresponds to the field type
      • Order: position selected in the slushbucket (for example, 100 for the first field and 200 for the second field)
      • Question: Column label of the field
    • If the field type is Choice, a corresponding question choice is created for each field choice.
    • The new record producer is added to the Record Producers related list on the table record.

9 Adding a Database Index

As an administrator, you can define database indexes for tables starting with the Fuji release.

Warning
Warning: This is an advanced procedure and should only be performed by an administrator familiar with database management.
  1. Navigate to System Definition > Tables.
  2. Select the table you want to add an index to.
  3. Right-click the form header and select Configure > Related Lists.
  4. Add the Database Indexes > Reference Table related list.
  5. Click Save.
  6. In the Database Indexes related list, click New.
  7. Select one or more columns to index.
  8. Specify an Index Name.
  9. [Optional] If the selected column should contain a unique value for each row, select Use Unique Index.
  10. Click Create Index.
    The Create a New Index dialog appears.
  11. Enter an email address in the Email me field, or select Do not notify me.
  12. Click OK.

The index is not created immediately. If you specify an email, the instance sends a notification when finished adding the index.

Was this article helpful?
Yes, I found what I needed
No, I need more assistance