cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
KGranger
Engaged Sweeper
I have a simple asset report whose only criteria is that custom field #5 (defined as a radio button list) contains one of six possible team member names. It is supposed to be listing those assets that were physically inventoried by one of the six team members. The report (SQL statement shown below) returns some assets inventoried by Steve Wright but not all. Obviously I need to know exactly how many assets Steve has counted. I do not know at this point if Steve is the only one affected or not.

This must something simple I'm missing but I'll be hanged if I can see it. I've attached a couple of screen shots showing one of the assets that should be showing up and isn't. As you can see, IT0007 was inventoried by Steve on 10/20/2015 but it does not appear when I filter the report results. Any help would be appreciated.
=====SQL Statement=====
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.BarCode,
tblState.Statename As State,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.Custom5 As [Inventoried By],
tblAssetCustom.Custom6 As [Inventory Date],
tblAssetUserRelations.Comments
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Where (tblAssetCustom.Custom5 = 'Aleks Sterkin') Or
(tblAssetCustom.Custom5 = 'Anh Pham') Or
(tblAssetCustom.Custom5 = 'Ken Granger') Or
(tblAssetCustom.Custom5 = 'Lenwood Mangham') Or
(tblAssetCustom.Custom5 = 'Mitchell Lewis') Or
(tblAssetCustom.Custom5 = 'Steve Wright')
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
I moved this topic to the report requests section.

Your report contains information about asset-user-relations. Table tblAssetUserRelations is joined through an inner join, that means only assets will get listed for which any asset-user-relations have been set up. If this wasn't intended, the best might be just removing that table from your report. Alternatively, right-click on the link between tblAssets and tblAssetUserRelations and "select all rows from tblAssets".

Also, you might consider removing the filter on Custom5. That would help you finding issues caused by typos or other unexpected data in this field.

Please find a modified report below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.BarCode,
tblState.Statename As State,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.Custom5 As [Inventoried By],
tblAssetCustom.Custom6 As [Inventory Date],
tblAssetUserRelations.Comments
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblAssetUserRelations
On tblAssets.AssetID = tblAssetUserRelations.AssetID

View solution in original post

2 REPLIES 2
KGranger
Engaged Sweeper
I knew it had to be something simple. Thanks for the help. I don't have to worry about typos in Custom5 as it is a radio-button list, so the values I'm looking for are limited. 'select all rows from tblAssets' got me what I needed. Thanks again.
Daniel_B
Lansweeper Alumni
I moved this topic to the report requests section.

Your report contains information about asset-user-relations. Table tblAssetUserRelations is joined through an inner join, that means only assets will get listed for which any asset-user-relations have been set up. If this wasn't intended, the best might be just removing that table from your report. Alternatively, right-click on the link between tblAssets and tblAssetUserRelations and "select all rows from tblAssets".

Also, you might consider removing the filter on Custom5. That would help you finding issues caused by typos or other unexpected data in this field.

Please find a modified report below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.BarCode,
tblState.Statename As State,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.Custom5 As [Inventoried By],
tblAssetCustom.Custom6 As [Inventory Date],
tblAssetUserRelations.Comments
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblAssetUserRelations
On tblAssets.AssetID = tblAssetUserRelations.AssetID