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 gkittelson 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!
- Thread starter Wishmaker
- Start date
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
hotoID to photoID_fk and from
DVDTableVDID 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.
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
hotoID to photoID_fk and fromDVDTable
VDID to DVDID_fkWhen 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.
- Thread starter
- #3
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
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
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
- Thread starter
- #5
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question