How to make a history of companies. . .

[Cassandre]

I want to make a database filled with companies and their start and end dates. These companies have

[BobWeaver]

This is what is often referred to as a multi-level or recursive type relationship.

You can set up a multi-level database with an unlimited number of generations, but you will have to set a finite limit as to how many immediate children or parents a record may have. As an example, I posted a Multi-Level example here. This example, by design is limited to a single parent, and four children for each record, but no limit to the number of generations going off in either direction. So, if you can safely assume that no more than say 5 companies will ever merge at one time to form a new company, then you just create 5 parent fields. Likewise, if you will never have a company split up into more than 5 companies (won't work for AT&T alas) then you just create 5 child fields, etc. These parent and child fields are then linked by individual relationships back to a company ID field in the file. I suggest you look at the sample file, and see if you can modify it to suit your purposes.

One other thing that I should mention is that, while browsing, Filemaker doesn't let you look beyond one generation in either direction. This is because of the way the relationship work. Therefore, in my example it was necessary to include scripts to propagate the data up and down the generations to create the necessary data displays and calculations.

[Ugo DI LUCA]

Hi,

Bob's files are awesome as usual, and from what I've seen is based upon one file only, the second being used for "graphic and display" purpsose. I'd investigate it some more though.

An alternative, which I currently use is to implement another file, that you'd use as a join file with 2 main key fields (Parent_Id and Child_ID).

This way, you'd be able to have a display of all related parents and childs for any record of your Master file.

If Company A has childrens Company B,C and D, there would be 3 records in the Join

Record_1 - Parent_Id : Company A ---Child_Id : Company B

Record_2 - Parent_Id : Company A ---Child_Id : Company C

Record_3 - Parent_Id : Company A ---Child_Id : Company D

When Company A becomes a child of Company E, the record would be inputted as :

Record_4- Parent_Id : Company E ---Child_Id : Company A

Building relationships from the Main file to the Join would let you have a display of either the Parent and Childs.

If you want to have Company_E listed as the Main Parent for Companies B,C and D as well, you'd have to use a self-Join in the Join File and a calculation that would check if the Parent_Id is also a Child_Id.

SJParentToChild > JoinFile:Parent_Id::JoinFile:Child_Id

c_ReturnMasterParent = Case(IsValid(

SJParentToChild ::Record_Id, SJParentToChild ::Parent_Id, Parent_Id)

As this later calculation isn't indexed, you won't be able to build a relationship on it. You'd need a script (or use a trigger) to index this calc. If you deal with multiple relationships, you'd better script any entry anyway, in order to reduce the amount of cross-records calculations.

If Company_B has some childrens affiliated, then you'd need to do the same thing with another SelfJoin that would link the Child_Id to the Parent_Id.

SJChildToParent > JoinFile:Child_Id::JoinFile:Parent_Id

c_ReturnChilren = Case(IsValid(

SJChildToParent ::Record_Id, SJChildToParent ::Parent_Id, Child_Id)

If Company_C has more than one Parent, then you'd need to do the same thing with another SelfJoin that would link the Child_Id to the Child_Id.

SJChildToChild > JoinFile:Child_Id::JoinFile:Child_Id

c_ReturnOtherParent = Case(not Record_Id

=SJChildToChild ::Record_Id, SJChildToChild ::Parent_Id, Parent_Id)

Now, when browsing your Main records, you'd have a complete view of either Master Parents,Childrens, Related Parents and Childrens.

The concept may be complicated if there are many related children and parents, but then I'd implement the ValueListItems function.

With some work on the scripts, you could have all these records appear in a portal, may be implementing a Tree-Like Portal.

You can get an overview of this structure (Tree-Like) in the sample section. The thread is called 'Line Items in Folders'

Just some thoughts.

In Bob's file, the second file seems to be implemented for graphic purpose. I would need some time however to rebuild

[Cassandre]

Hoi Bob and Ugo, read your replies and I