How to include Object Properties table in Load Query

when creating new task, service or asset, it is possible to store extended properties in the database for the related instances which can be used for reporting and querying purposes these can either be stored in the objects builtin property table (e g jobproperty, serviceinstanceproperty or assetinstanceproperty) or a common properties table can be created the built in property table is good when you only have a couple of properties to store, and can automatically be done by setting the property="1" attribute in the arguments e g \<arguments> \<examplearg property="1"> \</examplearg> \<arguments> however if you wish to store multiple arguments then it is better to use a common properties table for more information on common properties tables please refer to the linked associated article once you have the properties being stored it is possible to write scripts using the loadquery function to return object collections based on the values stored in property tables the below sections explains how to write these queries querying data stored in commonproperties table when writing a query that references the commonproperties table, you need to wrap the field name in curly brackets {} the loadquery function will then interpret this to mean that the value within the curly brackets is a column in the commonproperties table on the object that it needs to join to the internal sql query for example a service has been created for users to request teams calling the service has a commonproperties that contains a property called "ddirequired" which is used to indicate whether the user requested a ddi or not a script needs to be written to create a serviceinstance collection of users that has the ddirequired property set to "true" to do this we will use the serviceinstancequery loadquery function along with the query "ddirequired = 'true' and enddate is null" you will see in the below code that "ddirequired" has been wrapped in curly brackets, which the load query function will interpret as being a column in the commonproperties table of the service service service = evaluator getobject("=//services/access/teams calling") as service; string query = "{ddirequired} = 'true' and enddate is null"; list\<serviceinstancequery> services = serviceinstancequery loadquery(service, query); querying data stored in the builtin properties table an alternative to creating a common properties table is to store the data in the builtin property tables (e g jobproperty, serviceinstanceproperty or assetinstanceproperty) the process for referencing the property is similar to the method for data stored in the commonproperties table by wrapping the name within curly brackets, however for the builtin table you need to add a " =/ " (or " =/data/ " for service and asset instances) to the start of the property name so based on the previous example if the "ddirequired" argument was stored in the serviceinstanceproperty table the query would look like this string query = "{=/data/ddirequired} = 'true' and enddate is null"; at the time of writing this kb, the following only applies to the jobcollection loadquery function, and does not apply to serviceinstancequery or assetinstancequery this will be available for serviceinstancequery and assetinstancequery in v8 2+ the inbuilt property table allows for data to be stored in one of 3 ways, raw value, adguid, or refid depending on how the data has been stored in the property table you may need to instruct the query to use one of the 3 columns by default it will use the "value" column, but if the data is stored in the refid or adguid column then you will need to add either refid or adguid to the end of the expression e g {=/distributionlist refid } or {=/user adguid }