How to Create a Common Properties Table

11 min

Common Properties tables are used to store arguments from Jobs, Service or Asset Instances in the database. This is handy when you need to be able to create reports or query the data that has been recorded.

The following steps explain how to create the common properties table.

Steps to Create a Common Properties Table.

The following steps are based on creating a common properties table on a service called Teams Calling where we are storing whether a user requires a DDI, if so what the DDI number is.

These steps are the same for creating a common properties table on Tasks and Assets.

In this example the Service Arguments look like this:

<data>
  <ddiRequired />
  <ddi />
</data>

On the Service, create a new Xml parameter and call it "CommonProperties"

Open up the new parameter and add the following xml:

<schema table="#TODO TableName">
</schema>

Update the table attribute and replace "#TODO TableName" with a unique name for the table you want to create. e.g cp_TeamsCalling

<schema table="cp_TeamsCalling">
</schema>

For each column that you wish to create in the common properties table, add the the following line:

<property name="#TODO" type="#TODO" sqlType="#TODO">#TODO Expression</property>

Update the following attributes:

If you are creating a CommonProperties table on a Task, please refer to the "Task Additional Property Attributes" section below for the additional attributes that need to be set.

Update the inner text of the property element with the expression that will be resolved when storing the data. e.g. =/Data/ddiRequired

Once completed, your xml should look something like this:

<schema table="cp_TeamsCalling">
    <property name="DDIRequired" type="System.String" sqlType="bit">=/Data/ddiRequired</property>
    <property name="DDI" type="System.String"sqlType="varchar(30)">=/Data/ddi</property>
</schema>

Save and exit the Xml Editor.

Within the Explorer panel, right click on the Service and then click on Service Tasks > Update Common Properties Table. * * For tasks, the option is located under the All Tasks menu

Then click OK. The table will then be automatically created. No confirmation message will be displayed, unless there is an error.

Open SQL Management Studio and check that the table has been created.

System.String

System.DateTime

System.Guid

System.Bit

System.Int32

Task Additional Property Attributes

When creating a Common Properties table on a Task, one or more of the following attributes MUST be added to each property line:

Attribute	When is property set?
oncreate	The property will be updated when the job is submitted
onupdate	The property will be updated each time the job is updated (when Job.SetInfo() is executed).
onclose	The property will be updated when the job is completed.

These are used to control at what stage in the job the property needs to be updated.

<property name="#TODO" type="#TODO" sqlType="#TODO" oncreate="1" onupdate="1" onclose="1"></property>

If you wish to prevent a property from being updated on a particular event (e.g. oncreate), then attribute must be removed. Changing the value to "0" (e.g. oncreate="0") does not do anything.

Updating an Existing Common Properties Table

New columns can be added to the end of a common properties schema.

Changes to types for existing columns are NOT supported. Changes to the column types can be done manually in SQL for the tables and then change the schema.

Advanced Options

The following are advanced setup options, and should only be performed with assistance from an Activate Consultant.

Joining to additional tables.

There is an ability to provide a join in CommonProperties for Services and Assets. An example of joining to ADCache to the object to include some user information is below:

<schema table="cp_TeamsCalling">
  <property name="DDIRequired" type="System.String" sqlType="bit">=/Data/ddiRequired</property>
  <property name="DDI" type="System.String"sqlType="varchar(30)">=/Data/ddi</property>
  <join to="left outer join ADCache on userGuid = ADCache.Guid">
         <property name="UserTitle"type="System.String">ADCache.title</property>
         <property name="UserDepartment"type="System.String">ADCache.department</property>
  </join>
  <index name="Srv_ddiIdx2">ServiceInstanceID,DDIRequired</index>
</schema>

By joining to another table, the property will become available for use in the ServiceInstance and AssetInstance query functions.

Adding Indexes

Sometimes it may be necessary to add additional indexes to the CommonProperties table. This can be done by adding an index node into the CommonProperties schema:

<index name="Srv_ddiIdx2">ServiceInstanceID,DDIRequired</index>

Storing Calculated Values

There is an ability to have calculated values set into a property using SQL Scalar-valued functions. This can be done by setting an attribute called "const" on the property, and then providing the SQL that will be used when the record is updated. e.g. TotalApprovalMinutes = duo.GetTotalApprovalMinutes(%=//Job/Id%)

An example of where this is used is the Job CommonProperties (//Resources/Configuration/Job):

<property name="TotalApprovalMinutes" type="System.Int32" onclose="1" const="1">TotalApprovalMinutes = dbo.GetTotalApprovalMinutes(%=//Job/Id%)</property>