How to Create a Common Properties Table

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 true 333,334left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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>