Modify Table Command

If you are a Business Administrator with table access, you can modify system tables. If you are a Business Administrator with person access, you can modify tables in any person’s workspace (likewise for groups and roles). Other users can modify only their own workspace tables.

You must be a business administrator with group or role access to modify a table owned by a group or role.

This page discusses:

Syntax

Use the Modify Table command to add or remove defining clauses and change the value of clause arguments: 

modify table NAME user USER_NAME [MOD_ITEM {MOD_ITEM}];
Or
modify table NAME system [MOD_ITEM {MOD_ITEM}];
  • NAME is the name of the table you want to modify. If you are a business administrator with person access, you can include the User clause to indicate another user’s workspace object.
  • system refers to a table that is available for system-wide use, and not associated with the session context.
  • MOD_ITEM is the type of modification you want to make. Each is specified in a Modify Table clause, as listed in the following table. You only need to specify the fields to be modified.
    Modify Table Clause Specifies
    ADD_ITEM {ADD_ITEM} Any of the items that can be added when creating the table. For more information, see Add Table Command.
    name NEW_NAMEThe current name is changed to the new name.
    description VALUE The current description value, if any, is set to the value entered.
    units | [picas] | points | inches The current units definition is changed to the new units specified.
    icon FILENAME The image is changed to the new image in the field specified.
    column delete COLUMN_REF The column identified by the given column number is removed from the table. To obtain the column number for a specific column, use the Print table command. When the table definition is listed, specify the number assigned to the column to delete.
    column delete name COLUMN_REF The column identified by the given column name is removed from the table.
    column modify COLUMN_REF [label STRING_VALUE] COLUMN_TYPE_DEF [COLUMN_DEF_ITEM] The column identified by the given column number is modified. To obtain the column number for a specific column, use the Print table command. When the table definition is listed, specify the number assigned to the column to delete.
    column modify name COLUMN_REF [label STRING_VALUE] COLUMN_TYPE_DEF [COLUMN_DEF_ITEM] The column identified by the given column name is modified.
    column modify COLUMN_REF order NUMBERMove the specified column to the indicated order number.
    column COLUMN_DEF A new column can be defined according to the column definition clauses and placed at the end of the table.
    activeChanges an inactive table to an active table.
    inactiveChanges an active table to an inactive table.
    hidden The hidden option is changed to specify that the object is hidden.
    nothidden The hidden option is changed to specify that the object is not hidden.
    add property NAME to ADMIN [value STRING]Adds the specified property.
    add property NAME [value STRING]Adds the specified property.
    add visible USER_NAME{,USER_NAME}The object is made visible to the other users listed.
    remove property NAME to ADMINRemoves the specified property.
    remove property NAMERemoves the specified property.
    remove visible USER_NAME{,USER_NAME}Removes visibility from the listed users.
    visible USER_NAME{,USER_NAME}; The object is made visible to the other users listed.
    property NAME [to ADMINTYPE NAME] [value STRING] The named property is modified.
    property NAME [to ADMINTYPE NAME] [value STRING] The named property is modified.
    history STRING Adds a history record marked "custom" to the system table that is being modified. The STRING argument is a free-text string that allows you to enter some information describing the nature of the modification.

    For more information, see Adding History to Administrative Objects.

    The history clause applies only to system tables; it does not apply to user tables.

    • COLUMN_REF can be one of these items:
      • COLUMN_NAME
      • COLUMN_NUMBER

Each modification clause is related to the arguments that define the table. To change the value of one of the defining clauses or add a new one, use the Modify clause that corresponds to the desired change.

When modifying a table, you can make the changes from a script or while working interactively with MQL.

  • If you are working interactively, perform one or two changes at a time to avoid the possibility of one invalid clause invalidating the entire command.
  • If you are working from a script, group the changes together in a single Modify Table command.

Deriving a User Table from a System Table

If you are a user with access to a system table, you can copy (clone) the system table to create a new table in your workspace. This new user table inherits all the columns of the system table. Any subsequent changes made to the system table are reflected in the derived user table as well.

Once derived, the user table can be customized to add new columns, hide existing columns and to change the order of the columns. However, other aspects of the user table, like expressions and settings of columns inherited from the system table, cannot be altered.

A user table that is derived from a system table cannot be used to derive another user table.

You can only derive a user table from a system table in MQL. Once the derived user table is created, you can see it the same as your other tables.

To create a copy of a user-derived table for another user, you must re-derive the original system table for the new user and then reapply any customizations.

Only system tables and derived tables are shown in webapp selection menus.

The Copy Table command lets you derive a user table by copying a system table:

copy table SRC_NAME DST_NAME [derived] [COPY_ITEM {COPY_ITEM}] [MOD_ITEM {MOD_ITEM}];
  • SRC_NAME is the name of the table definition (source) to be copied. Must be a system table if your are deriving from it.
  • DST_NAME is the name of the new definition (destination); that is, the user table being created.
  • derived is an optional keyword that should be given just after the DST_NAME (name of the new user table) to indicate that the new table is derived. When the derived keyword is included, the SRC_NAME table must be a system table. The default is notderived.
  • COPY_ITEM can be:
    COPY_ITEMSpecifies
    fromuser USERNAMEUSERNAME is the name of a person, group, role or association.
    touser USERNAME
    overwriteReplaces any table of the same name belonging to the user specified in the touser clause.

    The order of the fromuser, touser, and overwrite clauses is irrelevant, but MOD_ITEMS, if included, must come last.

  • MOD_ITEM is the type of modification you want to make. For more information, see the table above.

Derived Table Behavior

When you modify a system table after a user table has been derived from it, the modifications are not apparent in the table definition until the user table is modified.

Similarly, if a derived user table is modified with new columns added, and then the system table it was derived from is modified by adding new columns, the order of the columns in the derived table might not be as expected. You will need to readjust the column order numbers in MQL or rearrange the columns.

If the system table (source) is deleted, the user table derived from it is also deleted. If you want to keep the derived user table, you can first modify it to make it a standalone table. For example: 

modify table test user1 notderived;

If you need to export a derived table, all columns must have a name. If they do not, you will get a warning when exporting. You must add names to any un-named column and re-export the table or import will fail.