1 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: UDF to pull out subsite parent

Does anyone have a UDF to pull out the "parent" site for a given subsite:

- Bash Wood (Location type: Wildlife Trust Reserve)
--- Main Ride (Location type: Sub-site)
----- West Verge (Location type: Sub-site)

so using the function on "West Verge" would bring out "Bash Wood"?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: UDF to pull out subsite parent

I have uploaded a UDf which was designed to do this. Supposed to have been tested in 2005,  but not sure how well. It return the name, but if you want the key then it would be easy to adapt.

http://forums.nbn.org.uk/uploads.php?file=SUDFLocationTopLevel.zip

Mike Weideli

MikeWeideli's Website

3 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: UDF to pull out subsite parent

Excellent Mike - thanks. I've modified it slightly to stop when it hits a parent site that doesn't have a location type of "sub-site":

--****************************************************************************************************
DECLARE @ReturnString varchar(100)
DECLARE @LocationName varchar(100)
DECLARE @ParentKey char(16)
DECLARE @LocationType char(16)

SET @ParentKey = (SELECT Parent_Key FROM LOCATION WHERE LOCATION_KEY = @LOCCKEY)
SET @LocationType = (SELECT Location_Type_Key FROM LOCATION WHERE LOCATION_KEY = @ParentKey)
SET @LocationName = (SELECT Item_Name FROM LOCATION_NAME LN WHERE LN.LOCATION_KEY = @ParentKey AND LN.Preferred = 1)

WHILE @LocationType = 'NBNSYS0000000006'
  BEGIN
    SET @ParentKey = (SELECT Parent_Key FROM LOCATION WHERE LOCATION_KEY = @ParentKey)
    SET @LocationType = (SELECT Location_Type_Key FROM LOCATION WHERE LOCATION_KEY = @ParentKey)
    SET @LocationName = (SELECT Item_Name FROM LOCATION_NAME LN WHERE LOCATION_KEY = @ParentKey AND LN.Preferred = 1)
  END
--****************************************************************************************************
SET @ReturnString = @LocationName 

RETURN @ReturnString
END

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website