Calem Blogs

Blogs of CalemEAM

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.

Data Model

The first step is to modify Calem's asset view "cmv_asset" to include a custom field from the the asset group. The sample views are provided in "Calem_Home/server/setup/view/ CmDbView.custom.sample.conf.php".
  • 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.
Client Customization via Calem Designers

Once the server changes are done you may customize the client forms via Calem Designers to include the new field in the data view and search filter:

  • Calem Designers allow one to customize forms, reports, and search screens via drag and drop.
  • First of all you will determine the scope of client customization:
    • You can customize asset screens for user groups who need to access this feature. See User Groups and Calem Designers in Calem Enterprise Admin Guide for more information.
  • Next, customize asset list, read and search forms for selected groups to make the new field ("is_discontinued") available.
    • The new custom field ("is_discontinued") will be a field available in the "cmv_asset" table so you can place it in the data and search forms.


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.
A Note of Work Order Life-Cycles in Calem
Work Orders to Print