How to go about doing this?

I am currently trying to develop a Prospect database for my company (Insurance company). And I am not able to find the way to implement this feature into FileMaker 8 Advanced. I’m also new to FileMaker, I took 3 FM 7 classes from CoreSolutions (Intro, Intermediate I and Intermediate II) about a month ago.

Let me write the sample data and how it should work.

Prospect Table (I’m just using a test db)

Field names: ProspectID,Company Name, Telephone, website

ProspectID, Company Name, Telephone, Telephone, Website

P1, A, 18003453434, www.a.com

P2, B, 18881231234, www.b.com

P3, AA, 18003453435, www.aa.com

P4, AB, 18003453436, www.ab.com

P5, C, 18883211234, www.c.com

A is the parent company and it has two subsidiaries, AA and AB. In the world of insurance, the parent company always take care of the insurance for its subsidiaries. So for the sales reps, it’s useless to call AA or AB. When a sales rep enter a prospect in the database, it should be able to tell if this company already exists and if it is,show that it is a subsidiary or a parent (companies without subsidiaries are parent as well).

During my training at CoreSolutions, I had ask my teacher how I can fix this problem. He told me I needed to use a join table which has 2 fields called ParentID and ChildID, the data in these 2 fields is the primary key of the Prospect table (ProspectID).

On the board he drew the prospect table and an arrow going toward the ParentID of Subsidiary Table (Join table) and another one coming back to the Prospect Table. He was saying that it will check itself with a join table. And that the subsidiary table is composed of 2 fields that combined together will provide a unique value.

Example of the Subsidiary table

ParentID, ChildID

P1,P1 (P1P1 is unique)

P2,P2 (P2P2 is unique)

P1,P3 (P1P3 is unique)

P1,P4 (P1P4 is unique)

P5,P5 (P5P5 is unique)

How can this be done? I haven’t been able to do it.

subsidiary.zip

Welcome smsaw,

While a join table would work, it may not be necessary.

If each company can only have one direct parent company, then no join table is required. A self join from the ParentProspectID to the ProspectID would work.

If it's important to see all the subsidiaries down the prospect chain, then using a method like I show in the Employee Tree example in this thread would work:

http://www.fmforums.com/forum/showtopic.php?tid/135595/

Thank you so much Ender!

I didn't have time to work on this project since I posted the question. Now that I see your example, I have finally grasp the concept of self-join. (I think I do). And now I remember the teacher teaching about it. But I was having problem grasping it at the time.

I've got my table to work as I wanted now and it's all thanks to you. TYVM! :clap: