Gathering data from parents & grandparents

[gephry]

Alright, this is a little odd. Someone set this database up to be dictionary-like or even choose-your-own-adventure-like ("For more, please see record..."). So each record can refer to another record within the current table. That being said, you can have a string of child > child > child > parent. Basically some records are a "continuation" or "more info" about the parent record.

So what I'm trying to figure out is, how can I collect or snowball data together from the parents and grandparents....on each of the Children records. Preferably without a script! (I'm trying to stick to calculations.)

The data structure is set up like this:

KEY

KEY_Parent

Title

Description

(etc.....)

Table::KEY_Parent == Table::KEY

Does anyone know of a way to access the data on the other parent/grandparent records when on the furthest Child? There is only one self relationship referring to the parent of the current record. But that parent could be the child of another record. In this instance:

KEY = 10

KEY_Parent = 9

Title = "Smallest Child"

KEY = 9

KEY_Parent = 8

Title = "Middle Child"

KEY = 8

KEY_PARENT = 7

Title = "Older Brother"

KEY = 7

KEY_Parent = ""

Title = "Absolute Parent"

For each record, I would like to be able to get a list of all the titles "above" that child. For example:

KEY = 10

KEY_Parent = 9

Title = "Smallest Child"

calcAllParents = "Smallest Child¶Middle Child¶Older Brother¶Absolute Parent"

KEY = 9

KEY_Parent = 8

Title = "Middle Child"

calcAllParents = "Middle Child¶Older Brother¶Absolute Parent"

etc.

Any idea on how to get the all of the titles of each of these records in a list?

This is a little wonky, so I really appreciate the help!

[TheTominator]

Any idea on how to get the all of the titles of each of these records in a list?

I can think of two ways to do this.

(a) One gives you all possible ancestors but is recursive and can fail badly if you accidentally form a loop.

( The other is limited in the number of potential ancestors but robust.

I recommend method (.

Method (a)

Create a relationship Parent: that relates the parent key to the key.

Create a recursive calculation

cSelfAndParentTitles = List(Title; Parent::cSelfAndParentTitles)

You probably need to create an empty value for cSelfAndParentTitles before defining it to refer to itself.

Method (

Create many relationships in a chain.

Parent: that relates the parent key to the key.

ParentsParent:: is the parent of the Parent table

ParentsParentsParent is the parent of ParentsParent

and so on.

You might abbreviate these as

Parent, PP, PPP, PPPP, PPPPP, ...

Create a calculation

cSelfAndParentTitles = List (Title; Parent::Title; PP::Title; PPP::Title; PPPP::Title; PPPPP::Title) /* And so on for as many relationships as you have in the chain */

[gephry]

Thanks, Tominator. I had been working with method A for a while but was hitting a snag. It was giving me only a partial list of the titles; I think it was because I was just using Title & "¶" & Parent::cSelfAndParentTitles. List() did the trick. And I am keeping the infinite loop at bay by using:

If( KEY_Parent =""; Title; List( Title; Parent::cSelfAndParentTitles ) )

Simple enough!

I agree, Method B would be safer, but I'm not 100% sure how many levels deep this data goes. So I'd rather cover my bases with an "infinite" calculation.

Thanks again!