Self join - still baffles me...

[stevie.at]

Posted this in "Relationships" but no reply...so I give it another shot here as I still could not make it work...found something similar here but not quite the same problem, so:

I'm working on a db with several files. One file (called "Links.fp7") holds all the links if records from one file are matched with others (like matching a Contact with a Project etc.) and it works just fine. Related data is displayed in protals and also the matching itself happens there.

The problem is matching a project to another project. I set up the relationships in the Links-file (see attachment1) and it seems to work fine there.

But I can't figure out how to draw the right information into the portal that shows the related projects. The table from the Links-file got 2 project-IDs and 2 fields with the respective project description. How can I set this up that only the related ID & description shows up in both projects that are matched with each other? So once matched only the information of the respective other project should show up in the same portal. Hope this makes sense...

Thanks,

Stefan

[Ender]

Hey Stefan,

We discussed some options for creating this type of inverse relationship in this thread:

http://fmforums.com/forum/showtopic.php?tid/171427/

[stevie.at]

Thanks Ender!

So seems to be a bit tricky...unfortunately I don't have Developer or Advanced, so the samples don't work properly.

Anyway...I'll keep on trying...

Stefan

[Ender]

I don't think those methods require Developer or Advanced for the basic relationships, calcs, and scripts. In my demo, I used CFs for something but it would still work in your version, and since they don't really apply to this example, you can ignore them.

[stevie.at]

I think so too. This referred to the ReverseRelation function...

Sorry to bother you but one more thing:

Your solution (RelationshipTree) works just fine, exactly what I was looking for! I do not need the fields RelationshipTo and RelationshioToFromCreator, so I deleted them and rearranged the scripts. Now it's not working properly anymore. Both fields seem to be essential but I don't see why...I use it for project ID and project description (instead of LinkToID and Name as in your example). So now only the projects where the matching starts (record with LinkFromID) shows the related data.

I also rearranged it the way in your example. Same result.

[color:red]OK. [color:black]Problem is my way does actually not create 2 records in the table Relationships but only 1. Now I did this by the script and this way it works fine for me. Probably kind of workaround but at the end it works for me...

cheers,

Stefan

Edited August 29, 2006 by Guest