Nothing here sounds impossible, but I'd like a clearer understanding of what you are trying to do here.
From a "big picture" perspective, what are you trying to do with this portion of your database?
Why do you have two item fields in the Relationship table?
I could be very wrong here but it looks like you are trying to pull up different groups of Item records to link to a common Relationship record and I think the same item record might need to appear in the list for more than one relaitonship record.
If so, you'll need a different data model to make that happen as you'll have a many to many relationship here not a one to many.
Thanks for your reply. Yes, I want to have a many to many relationship. Basically, I'd like to have an way of editing data for a kind of travelling salesman problem:
The Item table contains all the cities
The Relationship table allows me to specify if there is a road between two cities and how far it is between them
That's the short version of my problem. My actual problem is that I want to capture cause and effect data, where the effect can again be a cause resulting in another effect.
Thanks for your help,
Then you need three tables rather than two. The third table lies between the related tables and serves as a "join" table. In your "traveling salesman" example, this third table would store the distance between the two linked cities.
Just to make this even more interesting, the "two tables" that are being linked are actually two Tutorial: What are Table Occurrences? that refer to the same data source table. This makes for a "many to many" "self join" relationship.
Cities::__pkCityID = Mileage::_fkCityIDStart
Cities 2::__pkCityID = Mileage::_fkCityIDEnd
With this setup, a portal to Mileage can be placed on a Cities layout to list all the cities to which the salesman traveled. The names of the cities and any other city specific data can be shown in this portal by adding them from Cities 2 to the portal row.
Here's a many to many demo file you may find helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
Thanks for the demo, which I will look into as soon as I get up tomorrow. Hopefully, I will then understand why I need three tables instead of two... :)
Again, thanks and good night!
By the way: Thanks, Phil, for your help. I have downloaded the database and had a closer look at it. While I am fir with DB theory, I do miss one or the other piece of knowledge regarding FM implementation, so I did not manage to implement the frontend to an Ishikawa cause and effect database (with the cause being an effect of an another cause-effect relationship) in a "good looking", easy-to-enter manner. Sorry that the Ishikawa cause and effect term did not pop up earlier. Wishing you a very good weekend!