Advise on creating an hierarchy type structure

[jdenver]

Any advice on how I can go about this: I have a customers db that contains a field called 'recommended_by'. This field stores a number that relates to a customer in the db, so when a new customer is entered I can select a number that corresponds to the customer that passed on my name from the list displayed. I have a record with the surname field set to 'Advert' so if a new customer calls from my advert, I select this as the recommended number.

Is there a way of selecting a customer and then have displayed the path to that customer through recommendation up to 'Advert' name which will be at the top of the hierarchy?

[jdenver]

At the end of the day, I just need to be able to select a customer and see a recomended list of customers from one to the other back to the original advert. Someone must have an idea?

[Lee Smith]

Hi jdenver,

I'm not sure I follow you completely. But here is what I'm understanding.

BTW, I think you are pretty close to having what you want, however, please correct me if I missunderstood something.

You have a file with Current Clients?

Your Current Clients have recommened Prospects?

You have created a relationship Key using your Current Clients and assigned it to the Prospects

Now you want to see a list of prospects associated with the Current Client.

Lee

[Fenton]

I've got an idea. I don't know if there's a simpler way. But I just strung a series of the join table, one for each "level" (I stopped at 5).

[P.S. I'd strongly suggest that you use Record-level access privileges to protect that "Advert" record from Delete, by using the Limited option.]

ListHierarchy.fp7.zip

[comment]

I believe this can be done recursively, without limiting the number of 'generations'.

BTW, why is this in Classifieds?

LinkCascade.fp7.zip

Edited March 12, 2007 by Guest