Using SQL commands to shorten the Request ID field

Only administrators running AR System with an SQL database can update existing request ID field values by directly accessing the SQL database. The syntax for direct access is different for each SQL database that AR System supports.

When you change the length of the Request ID field in a database table, all related database tables, such as status history tables ( H Tables), and Attachment tables ( B Tables and BC Tables) must also be updated.

Finding the name of the table

Before you can shorten the request ID field value, you must find the table holding the form being changed.

To use the methods described here, you must be familiar with basic commands in the SQL command interface. SQL commands bypass AR System completely. If you bypass AR System, verify that all data is valid when you are finished.

To find the table name

1. Find the correct schema ID for the form with the following query:
   Select SchemaId, name from arschema order by 2
   This query returns a list of schema IDs and associated form names.
2. Find the correct field ID with the following query. (The example assumes that the schema ID is 43.)
   Select FieldId, FieldName from field where SchemaId = 43
   This query returns a list of field IDs and associated field names.

3. Use the schema ID, field ID, and information in the following table to construct the table name.

   Table name	Description
   TschemaID	A table that contains the data in your form. A table named T43 indicates that 43 is the schema ID.
   TschemaIDCfieldID	(Oracle only) A table that contains long text and diary data. For example, a table named T43C536870924 indicates that 43 is the schema ID and 536870924 is the field ID. In many cases, the form has more than one long text or diary field. This format is used for backward compatibility with forms created using AR System versions prior to 4.5.
   HschemaID	A table that contains the Status History information for your form. A table named H43 indicates that 43 is the schema ID. See the Database Reference Using-relational-databases-with-BMC-Helix-Innovation-Suite.
   BschemaID	A table that contains a list of all the attachments and related information for each record in your form. A table named B43 indicates that 43 is the schema ID. See Relationship-between-forms-and-database-tables.
   BschemaIDCfieldID	A table that contains the actual Binary objects for attachment fields in your form. A table named B43C536870924 indicates that 43 is the schema ID and 536870924 is the field ID. In this example, the field ID for the attachment field is 536870924. In some cases, the form has more than one attachment field.
   S schemaID	A table that contains information about Row-level security permissions.

For TschemaID and BschemaID tables, the request ID column of the table is always named C1. For the HschemaID , TschemaIDCfieldID , and BschemaIDCfieldID tables, the Entry ID column is equivalent to the C1 column.

Examples for changing existing Request ID field value format when the Request ID does not have a prefix

The following examples assume that the table is named T43 and that the field size is 8 characters. The 8 represents the number of characters to keep, starting from the right side of C1. C1 is originally 15 characters long. Make sure that the number of characters in the second parameter in the RIGHT function is equal to the new size of the C1 field and that the sum of the two numeric values in the SUBSTR function is 16 (1 greater than the original length of C1 ).

Oracle database scenarios

To add a prefix to the TschemaID table, use the following syntax:
update T43 set C1 = substr(C1,8,8); 

To add a prefix to the BschemaID table, use the following syntax:
update B43 set C1 = substr(C1,8,8); 

For the HschemaID table, use the following syntax:
update H43 set entryId = substr(entryId,8,8); 

For the BschemaIDCfieldID tables, use the following syntax:
update B43C536870924 set entryId = substr(entryId,8,8); 

For the TschemaIDCfieldID tables, use the following syntax:
update T43C536870924 set entryId = substr(entryId,8,8);

In the functions substr(C1,8,8) and substr(entryId,8,8), the first 8 represents the starting position of the characters to keep, and the second 8 is the number of characters to keep.

For more information about the SUBSTR function, see SUBSTR.

Microsoft SQL Server scenarios

To add a prefix to the TschemaID table, use the following syntax:
update T43 set C1 = RIGHT(C1, 8) 

To add a prefix to the BschemaID table, use the following syntax:
update B43 set C1 = RIGHT(C1, 8) 

For the HschemaID table, use the following syntax:
update H43 set entryId = RIGHT(entryId, 8) 

For the BschemaIDCfieldID tables, use the following syntax:
update B43C536870924 set entryId = RIGHT (entryId, 8)

Examples for changing existing Request ID field value format when the Request ID has a prefix

The following examples assume that:

• The table is named T43.
• The prefix is HD.
• The field size (including the prefix) is 8 characters.

The 6 represents the number of characters to keep, starting from the right side of C1. C1 is originally 15 characters long. Make sure that the number of characters in your prefix plus the second parameter in the RIGHT function is equal to the new size of the C1 field.

Oracle database scenarios

To add a prefix to the TschemaID table, use the following syntax:
update T43 set C1 = 'HD'||substr(C1,10,6); 

To add a prefix to the BschemaID table, use the following syntax:
update B43 set C1 = 'HD'||substr(C1,10,6); 

For the HschemaID table, use the following syntax:
update H43 set entryId = 'HD'||substr(entryId,10,6); 

For the BschemaIDCfieldID tables, use the following syntax:
update B43C536870924 set entryId = 'HD'||substr(entryId,10,6); 

For the TschemaIDCfieldID tables, use the following syntax:
update T43C536870924 set entryId = 'HD'||substr(entryId,10,6);

In the functions substr(C1,10,6) and substr(entryId,10,6), 10 represents the starting position of the characters to keep, and 6 is the number of characters to keep.

Microsoft SQL Server scenarios

To add a prefix to the TschemaID table, use the following syntax:
update T43 set C1 = "HD"+ RIGHT(C1, 6) 

To add a prefix to the BschemaID table, use the following syntax:
update B43 set C1 = "HD" + RIGHT(C1, 6) 

For the HschemaID table, use the following syntax:
update H43 set entryId = "HD" + RIGHT(entryId, 6) 

For the BschemaIDCfieldID tables, use the following syntax:
update B43C536870924 set entryId = "HD" + RIGHT (entryId, 6)