DERIVED attribute uses and limitations
Article ID: 387574
Updated On:
Products
Issue/Introduction
One may encounter the DERIVED attribute in Web Screen Painter's Schema Designer as well as when viewing certain attributes, such as the following that are available out of box in the SDM cnt object (run "bop_sinfo -d cnt" to view these entries)
web_url LOCAL STRING(0) DERIVED('http://[SDM-SERVER]/CAisd/pdmweb.exe?OP=SEARCH+FACTORY=cnt+SKIPLIST=1+QBE.EQ.id=' || CAST(id AS NVARCHAR))
combo_name LOCAL STRING(0) DERIVED(last_name || isnull(', ' || first_name || ' ' || middle_name))
The following document serves to describe how the DERIVED attribute works, including its uses and limitations.
Environment
Release: All Releases
CA Service Desk Manager
Cause
A common misconception is that DERIVED is a column type that is contained in the SDM backend database. DERIVED is not a column type contained in the backend database. It is a local attribute that is defined for the given object that is populated automatically by the backend.
Using Contact objects as an example, they are defined as the "cnt" object, which in turn pulls most of its attributes from the ca_contact and usp_contact tables. One can run the command "bop_sinfo -d cnt" to view the attributes of the "cnt" Contact object, which in turn, will include the "combo_name" and "web_url" as a DERIVED local string attribute.
The important thing to be aware of is that "combo_name" and "web_url" are not direct fields which one can access from a backend database table. Neither the ca_contact and usp_contact tables have a field named "combo_name" or "web_url". Along with the fields from the ca_contact and usp_contact table, the "cnt" object also has attributes that are created dynamically by the Service Desk object system, which is where the DERIVED functionality comes into play, which uses internal functions to concatenate existing attributes from the existing object to form the DERIVED value.
The DERIVED function concatenates string content to create a value dynamically. The "cnt" object's "combo_name" attribute ties together the existing "last_name", "first_name", and "middle_name" attributes to create the "combo_name" DERIVED string value, and similarly for "web_url", pulls elements from Option Manager, such as web_cgi_url and adds the contact id to create the DERIVED string value.
Resolution
A question that may arise is if it is possible to leverage the DERIVED attribute. Such an activity is possible, as offered in the following two examples.
- Create a DERIVED attribute on the cnt object which displays the combo name as [first name] [middle name] [last name]. The default combo name displays as [last name] [first name] [middle name]
- Create a DERIVED attribute on the cr object which will display an introductory string and the id number of the cr object itself.
Steps to follow:
- In Web Screen Painter, go into Schema Designer and choose the cnt object.
- Create a new column called "ztestcomboname" and set the Field Type as DERIVED.
- In the Advanced tab, under Derived Expression, enter this text:
first_name " " middle_name " " last_name
- Repeat the same changes described above, but update the cr object. Please be careful to update the cr object instead of the cnt object.
In the following screencaps, we add the DERIVED attribute "ztestidnumber" with the following Derived Expression in the Advanced tab
"Internal ID Number " id
- Save and Publish the schema.
- Stop SDM Services, then run pdm_publish
- Start SDM Services back up.
Additional Information
One can test that the attribute change works in a variety of ways.
First, try running "bop_sinfo -d cnt" and "bop_sinfo -d cr" to verify that the DERIVED attributes all loaded successfully. Including OOB attributes, you will see:
Object: cnt
web_url LOCAL STRING(0) DERIVED('http://[SDM-SERVER]/CAisd/pdmweb.exe?OP=SEARCH+FACTORY=cnt+SKIPLIST=1+QBE.EQ.id=' || CAST(id AS NVARCHAR))
combo_name LOCAL STRING(0) DERIVED(last_name || isnull(', ' || first_name || ' ' || middle_name))
combo_name2 LOCAL STRING(0) DERIVED(last_name || ',' || isnull(first_name) || ',' || isnull(middle_name))
ztestcomboname LOCAL STRING(0) DERIVED(first_name || ' ' || middle_name || ' ' || last_name)
Object: cr
web_url LOCAL STRING(0) DERIVED('http://[SDM-SERVER]/CAisd/pdmweb.exe?OP=SEARCH+FACTORY=cr+SKIPLIST=1+QBE.EQ.id=' || CAST(id AS NVARCHAR))
ztestidnumber LOCAL STRING(0) DERIVED('Internal ID Number ' || CAST(id AS NVARCHAR))
Secondly, go into the Message Template for Manual Notify (Administration tab: Notifications -> Message Templates, and edit "Default Manual Notify message template for request/incident/problem". Include the following code at the bottom of the message template:
CUSTOM DERIVED ATTRIBUTE TESTS
Customer name using test combo name:
@{call_req_id.customer.ztestcomboname}
Ticket internal number concatenated with intro string
@{call_req_id.ztestidnumber}
Locate a call_req ticket (Request, Incident, or Problem) with a named customer contact who has a first name, middle name, and last name, then perform a manual notify. You will not be sending an actual email, but will be able to view the message to be sent, which will contained the processed DERIVED attributes and display their content
The message text will display as follows:
Please try the above in a test environment first before attempting in production
DISCLAIMER: The scope of this article and of Support is to demonstrate how DERIVED schema attribute definitions work, and offers simple examples that may be recreated via Web Screen Painter and Schema Designer. This document is provided for informational and demonstration purposes only. Support is not permitted to assist in debug/troubleshooting of any custom code, including any changes made outside of Web Screen Painter and Schema Designer. Support is also not permitted to assist or provide consultation of any content developed that is based off this document.
Supplemental Reading:
Feedback
