Video Screencast Help

How to search through the Location Hierarchy

Created: 27 Jun 2013 • Updated: 27 Jun 2013
jasoncordell's picture
0 0 Votes
Login to vote

Do you have your locations setup in a hierarchy?  If you are using Asset, you probably are.  Have you ever needed to search for people or assests based upon a random starting point in the location hierarchy, but had no idea how to get a listing of all of the locations?  This SQL will help with that.

Building 1  
  1st Floor
    Section B1F1-1
    Section B1F1-2
    Section B1F1-3
  2nd Floor
    Section B1F2-1
    Section B1F2-2
    Section B1F2-3
  3rd Floor
    Section B1F3-1
    Section B1F3-2
    Section B1F3-3

If you set the Location variable to '2nd Floor', it will bring back 2nd Floor, Section B1F2-1, Section B1F2-2, Section B1F2-3.  If you set it to 'Building 1', it will bring back everything.

This SQL will bring back every location from any given starting point within the hierarchy tree, which could be passed into other SQL to look for users/assets with a location matching the results of your recursive location search.