Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Many to Many Relationships help!

Status
Not open for further replies.

Wishmaker

IS-IT--Management
Feb 29, 2008
3
GB
I need to link a photo/dvd library to individuals in the photo, the event that the photo/s were taken.
I can't see the wood for the trees a the moment..

Many thnaks
John
 
A many to many has to be split up in two one to many.

For this you need a third table that comes 'in between' your two other tables, call it photoDVD_LineItem.

Make sure you have an ID (parent key) in both tables, photoID and DVDID. Those keys need to uniquely identify the records.

In your LineItem table you bring the two IDs together.

In your photo table: photoID
In you DVD table: DVDID

In your LineItem you have two fields: photoID_fk and DVDID_fk.

In the graph screen draw a line from
photoTable:photoID to photoID_fk and from
DVDTable:DVDID to DVDID_fk

When you look at the whole relationshipline you will see that the photos are related to the DVD, through your LineItem table.

Make records in the line item table for all the photos and all the DVD, where you show which photos are on which DVD.
You can make valuelist based on the fields in both tables for easy use.

Now you can put a portal in your DVD table, based on the LineItem relationship to show the related photos.
 
Thank you Jean,

I understand that bit now and since my post we have bought the training manual which shows exactly as you explained it but! We need to do a bit more than than the that, I hope the following has a bit more meat to it.

We would like to able to look up an event and see who attended and see what photo's or a DVD-recording that was taken at the event, at the same time we would also like to search for an individual and see what photo's or a DVD-recordings that they have participated in and events they have attended.

I hope this explains it a bit more.
many thanks
John
 
Same way of working John.

You have 4 entities: event, individual, photo, DVD.
Each in a table with a unique ID.

For 'each link to each link' you need a parentKey and a foreignKey.

>>to look up an event and see who attended and see what photo's or a DVD-recording that was taken at the event

It is clear all your links are many to many, so for each link you need a lineItem table in between with the pk and the fk.

Look in your training manual for the topic about 'records further away in the graph'.

Set up your portals in each table so you can see in Event the individuals at the given event, the photo's and the DVD.
With a button on each row linked to a script with the GTRR (Go to Related Record), you can easely navigate from Events to each individual, to photo's to DVDs.
The GTRR script will sort and isolate your records in each table.

Do the same in all the other tables, Individuals to see the events, Photo's to see the individuals, DVD to see the events etc.

The basic is a really good understanding of relationships and how to make them (from which perspectif) in the relationship graph.

HTH
 
Thank you JeanW,

I can't find any reference to 'records further away in the graph' in the FM9 training manual though.

I'll attempt your suggestions shortly.

Thanks
John
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top