"Borrowed" equipment report

[steve_shaw]

Hi chaps,

I'm using the user relational mapping to mark certain floating spares as "borrowed by x".

How would I write a report to show me all equipment currently being "borrowed", and by whom?

We want to check the status of "borrowed" equipment every few weeks to make sure things are being returned on time.

[steve_shaw]

Hi Daniel,

Thanks, it's working now. I recreated the report and it returned the expected results.

Many thanks for your quick replies,

Steve.

[Daniel_B]

We assumed that you have set asset user relations for your computer and used the relation "borrowed by". The report will only list assets in Active state.

Could you explain which data you are looking for and which assets you are seeing now in the report result?

Or would you like to have a list of all your assets? In that case, you need to change the Inner Join between tblAssets and tblAssetUserRelations to a Left Join (Select all rows from tblAssets).

[steve_shaw]

Hi,

Thanks Daniel. However, the report returns no results, despite several live assets being set to "borrowed by".

[Daniel_B · Accepted solution]

This report will list your assets which are "borrowed by" any user. Types of asset user relations can be defined or modified under Configuration\Relational Mapping

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysAssetRelationTypes.Name As relation,
  tblAssetUserRelations.Username,
  tblAssetUserRelations.Userdomain
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetUserRelations On tblAssets.AssetID =
    tblAssetUserRelations.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetUserRelations.Type
Where tsysAssetRelationTypes.Name Like 'borrowed by%'
  And tblAssetCustom.State = 1