The Business Rule action "Set Color of Ticket" can fail with "Color field in item not found" error

Manage Support IDs

Use checkboxes to 'Remove support IDs/Users' or 'Allow to create Cases'
Support IDs for BMC Customer Support Site Guest User
Profiles Which Include    Back to All Support IDs
 

Knowledge Article

The Business Rule action "Set Color of Ticket" can fail with "Color field in item not found" error
When using Business Rules with the action "Set Color of Ticket", they can sometimes fail to apply the action due to a problem with the color field attribute for the item. The Footprints.log will record an error citing "Color field in item not found".
FootPrints
FootPrints
BMC Footprints Service Core 12.x
When using Business Rules with the action "Set Color of Ticket", they can sometimes fail to apply the action due to a problem with the color field attribute for the item.   The Footprints.log will record an error citing "Color field in item not found".

The Footprints.log entry will appear like the one below.
 
ERROR|2016-07-01T11:13:12.860|-07:00|FPSC-00162177|ajp-bio-8009-exec-2|*.business.rule.operation.action.SetColor.run()|||Color field in item is not found.|
The problem occurs when the internal Color field definition is lost and no longer linked to the actual field which stores the color. Unfortunately we have not found all the causes to this definition loss, however this solution should work for all causes.
NOTE: These steps are to be performed during a maintenance period and should only be attempted after a successfully complete backup of the Footprints 12 database.
  1. Run the attached "10 - Check for Missing Color Fields.sql" query against your database making sure to update the fpscdb0001_* schema names if yours is different.  The results should appear like the ones below.  The item producing the Business Rule errors should be listed in the results.  These results will show all Items that are missing the Color field attribute, even if it hasn't produced errors yet.
    User-added image
  2. As an administrator, choose one of the items listed, then in the Administration panels, edit the item's fields list.   We need to manually create a new Simple Text field to replace the missing color field.   In my test example, I just called it "Color".  Once the field is added to the item, Save & Publish the workspace.
  3. Next use the SQL Management Studio to review the table for the item to be fixed (in the specified container schema).   We need to find the new column name that was created for the new Color field.  In most cases this should be "color_1".
  4. Copy the "20 - Fix Color for Custom Fields.sql" script into a new Query Window in SQL Management Studio.  Edit the line below to change the 'color_1' value to the name of the new column you found in step 3.  Once this line is corrected for your column name, run the query once as a test. 
    declare @colorColumnName varchar(100) = 'color_1'
  5. If the test run of the query about produced results and no errors, alter the link below and change the value from 0 to 1.  Re-run the query again to apply the changes. 
    declare @shouldCommit int = 0;
  6. Stop and Restart Tomcat.
  7. Once Footprints 12 has come back online publish the workspace once more to complete the steps for this solution.   Your Set color of Ticket rule actions should work once more.
  8. Repeat the process again for any other items found when running the script in step 1 above.
     
FootPrints
FootPrints
BMC Footprints Service Core 12.x
FootPrints
FootPrints
BMC Footprints Service Core 12.x
Attachment(s):
20 - Fix Color for Custom Fields.sql
10 - Check for Missing Color Fields.sql