Recursive Relationship (not sure, if this is the right name for it)

[lyter]

Hi!

Just signed up in the forum because i could not find an answer to my problem in 1.5 days searching various forums and googling a lot.

I attached a partial screenshot of the relationships in the database. Basically I'm looking for a method to relate ConfigItems (which are actually servers) to each other. So for example:

ConfigItem 1 has a ConfigItem 2, which could have also ConfigItem 3 and ConfigItem 4.

How would you design this?

I tried to add another table ("CI_CI), which would keep record of the parent-CI and the child-CI.

Hopefully I could explain myself properly, otherwise I'll give it another try.

Thanks in advance!

[comment]

ConfigItem 1 has a ConfigItem 2, which could have also ConfigItem 3 and ConfigItem 4.

So an item could have multiple children, but only one parent?

[lyter]

Yes, exactly! I'm sure you noticed, but to make sure: Parent/Children are of the same "type" (same table).

EDIT: Maybe I have to state also that I'd need to display the Children records in a portal. This hasn't worked so far, so my guess was that the relationships wouldn't work the way they should. Hence this post. ;)

[comment]

Well, then you need only one table. The two important fields to have are:

ItemID - Number, Auto-enter serial number

ParentID - Number

Define these two self-join relationships:

Parent::ItemID = Items::ParentID

and:

Items::ItemID = Child::ParentID

where both Parent and Child are occurrences of the Items table. Place a portal to Child on a layout of Items to see the current item's children.

To assign an item to a parent, populate the ParentID field with the ItemID of the desired parent.

[lyter]

[b]Parent[/b]::ItemID = Items::ParentID

and:

Items::ItemID = [b]Child[/b]::ParentID

I'm not getting what you mean by the Parent and Child tables. These tables don't exist. Since you recommended just one table (in this case "ConfigItems") the relationships have to only in this table, which makes no sense to me.

Sorry if I'm not getting the point. :)

[comment]

I'm not getting what you mean by the Parent and Child tables. These tables don't exist..

... where both Parent and Child are occurrences of the Items table.

See also:

http://www.filemaker.com/12help/html/relational.11.10.html#1028163

[lyter]

Thank you very much, I didn't know about the table occurrences. Works like a charm now! ;)