How to query for related list for location

[nowguru]

I would like to display list of assets associated to specific location (eg room).  I tried updating the related list view for cmn_location, but I dont see how you "join" other tables (eg other custom asset tables) that have location for the room defined.

[Maik Skoddow]

Hi

you cannot "join" related lists. If you need a joined view, you have to create a database view. But without any more details, I cannot tell how.

Kind regards
Maik

[nowguru]

Thanks for quick reply Maik.  For all of our custom hardware assets, I have associated a location (ie room) for each asset.  I would like to be able to see all of the hardware assets that have the location set for specific room.  We have created a dashboard that a user can filter for a specific room to see all the different types of assets, but I was hoping that the user could just open the room location and see the assets that are related to the room (via location field) under the related list.

The default view for related list of the location shows the parent location value - in my case building, but as you said, it wont show you data in other tables.  What are pros/cons of creating custom view vs creating custom relationship and associating the location with the various custom hardware assets?

It seems strange that no else has had this requirement before against location field

Thanks again

[Daniel Slocum · Accepted solution]

In my experience, it is best to add rooms and sometimes cubes within rooms to the location hierarchy under a building or floor. Thus, they are actual locations in the location hierarchy tree that can be selected in the location field.

Once the location is standardized, you can run a report off of the Asset table showing all of the records in the same room, much like the baseline stockroom modules behave.  Or, because of the baseline Location attribute on the Asset record, there is a related list available which you can add to the Location form. It is called Asset->Location.  If you navigate to the Location form, then choose Configure Related Lists from the right-click menu, you can add the Asset->Location related list to the form.

[nowguru]

Thank you Daniel.  Your last statement was what I was looking for!