This question may not be a big one for you, but for me (a newbee) its big.

This is what I'm trying to do.

I have 2 tables. I'll call one Pies and the other Fillings.

You can then go to the Pies table, create a new record.

In the new record is a drop down menu with the names from all the records in the Fillings table.

I've been able to do this so far.

Now I want to look at a record in the Fillings table and see what records in the Pies table selected this filling.

For Example:

I go to the Pies table

Create new record and title it Berry pie

In the drop down menu I select blueberry, rasberry, and huckleberry

Then when I go to the fillings table and select the record blueberry I want to see Berry pie is in a list of available pies that can be made with this filling.

I hope this isn't too confusing. I appreciate any help you can give me.

Create a second relationship table linking Fillings to the recorder filling in Pies. You would end up with the Relationship table 'Pies', 'Filling', and 'Pies2'.

It sounds like what you have is a many-to-many situation. Each pie can have multiple fillings and each filling can be in multiple pies. So you need an additional table to track which fillings are in which pies.

I've attached an example file....let me know if you have more questions.

Dana

pies.fp7.zip

You are seeking a Many-To-Many realtionship. That is several pies may have the same fillings, and several fillings may be in the same pies.

You'll need a "join" table that contains the Pie ID and the Filling ID.

There's an alternative arrangement as well. I've attached another method that uses check boxes as opposed to portals.

Easy_As_Pie.zip

Take a look at my Separation Model for Many to Many. It is the Separation Model section.

OK, so I went with the portal sollution. I also used popup list to select the records from the Filling list.

But for life of me I can't get a sum calculation to work.

Here are links to all the questions I've asked pertaining to this database. Hopefully this will give you a little more background.

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=123927&an=0&page=0#123927

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=119788&an=0&page=12#119788

Any help you can give is extremely appreciated.

Heath