Query of Asset Item from Assets

Note: "Asset Group" is renamed to "Asset Item" from Calem Release R11d. 

​

The background:

• Assets are linked to inventory items via its "Asset group" which is also an inventory item.
• Query of asset group fields from asset forms are not available out of the box.
• Query of custom fields added in asset groups are not available out of the box.
• The steps below describe the process to allow query of custom fields of asset groups from asset forms.
  • The process below describes the query of a single custom field of asset group from asset forms.
  • The same process is applicable to query more custom fields or seeded fields of asset groups.

• Create an interim view "cmv_in_zc" to include both inventory item ("cm_in") and its custom fields ("zc_cm_in").

create or replace SQL SECURITY INVOKERview cmv_in_zc AS select a.*, b.* from cm_in a left outer join zc_cm_in b on b.zc_id=a.id​

• Next, modify the default view definition by adding a file "Calem_Home/server/setup/view CmDbView.custom.conf.php". The new view includes a field "is_discontinued" which is to be displayed in asset and available for query.
  
  • The custom entries below ensure that future upgrades will not change your view customization.
  • For future releases you should review Calem's "cmv_asset" out of the box and make adjustments if needed to ensure that your changes are compatible to new releases.

$_CALEM_dbview['cmv_asset'] = array(

'generic' => "create or replace SQL SECURITY INVOKERview cmv_asset AS select a.*, b.is_discontinued from cm_asset a left outer join cmv_in_zc b on b.id=a.in_id"

);

• Create the updated view in your database:

​create or replace SQL SECURITY INVOKERview cmv_asset AS select a.*, b.is_discontinued from cm_asset a left outer join cmv_in_zc b on b.zc_id=a.id

• The view definition file has to be modified to include the new field (or fields) added. Add a new file "cmv_asset.custom.php" at"Calem_Home/server/metadata/wtable/", the same directory of "cmv_asset.php". Add the new field (or fields) to the new definition file.
  
  • The new file "cmv_asset.custom.php" ensures that your customization works with future version upgrades.

$_CALEM_table['fields']['is_discontinued']=array('type'=>'boolean');​

• Finally, run the build process "Calem_Home/bin/deploy.sh" (or deploy.bat) to build the changes into clients.

The steps above are technical but manageable provided one has basic operational knowledge of MySQL database and have computer skills.

• You can always get help from Calem if there are questions.
• Optionally, you may commission Calem to build advanced searches like the ones discussed here into a future release.