I think it is a recursive calculation I need here!
I have a table in my database, which self-joins as it records areas within areas. So, if it were describing a campus there would be one record for Building 2, one record for fourth floor, several records describing the rooms within those floors and possibly records for each areas within those rooms. As it's used for describing many different ways of splitting a location up, this model allows it to record as many areas as we want in as many levels of joined records as we want.
Each record has a primary key, a foreign key that is the primary key of the area it is within and a name. The top level record can be distinguished as the parent_area_id field would be empty.
The problem I have is that I need to have a calculated field in each record that returns a text breadcrumb trail of the names of the area and those above it.
So, in the example above I'd have one that says for instance: Building 2 > Fourth Floor > Room 1 > Bar Area.
I can do this by using lookups etc but they then don't change if areas are assigned to other areas and would also require processing to apply to existing records, Since these are imported from a SQL system that holds the basic records this would be a problem.
I'm sure it's possible to do through a calculation but it's making my head go funny!
Thanks in advance.