This is a classic many to many relationship. A star can appear in many magazines and a magazine can publish articles on many stars. A third table, called a Join table is required to make this work.
Start with these relationships:
Magazines::__pkMagazineID = Magazine_Star::_fkMagazineID
Stars::__pkStarID = Magazine_Star::_fkStarID
You can place a portal to Magazine_Star on the Magazines layout to list and select a Stars record for each given Magazines record. Fields from Stars can be included in the Portal to show additional info about each selected Stars record and the _fkStarID field can be set up with a value list for selecting Stars records by their ID field.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
PhilModJunk, thanks sofar for your help! Join table is the way to go, as I suspected. Haven't done anything like that yet, but I'll study the link you send and do my best to get it to work.
Thanks so far!
I also have a demo file that implements a many to many relationship and illustrates a number of possible designs for working with such a relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
Thank you very much! I've downloaded and I'm studying it right at this moment! I appreciate your help!
I've been trying all day, finally I decided to start all over again to have a clean database setup. Could you or anybody else help me, please?
This I've done so far:
1. I created a table called "Magazines", which include an Id, Title, issue, Full title, Artists.
2. I created a table called 'Artists', which include a FkArtists, Name, Birthday, Photograph, Biography, Appears in.
3. I created a JoinTable called 'JoinMagazineArtists', which include a FkMagazineArtists.
Nothing much, but to keep it 'easy' I thought I started out with the basics. That should work first, afterwards we'll see...
I've been thinking: I understand how I have to create the relationships between the two tables, but...
somehow the JoinTable is going to be used to store the information (which artist in which magazine, and which magazine covers which artists)... what is the way to go now?
I'm an absolute beginner, but the more I learn, the more I start liking making it, so I hope someone has the time/energy to help me along.
What i've got sofar, I've uploaded to a dropbox, which I've included over here: https://www.dropbox.com/sc/q0zgl61mfarhfd8/Vhj4hXtOD6
Really appreciate any help!
Your join table needs two foreign key fields.
In your join table you will be storing the ID of the magazine and the ID of the artist that are related.
So two number fields.
And two relationships.
Artists::ID ---- is related to ----- JoinMagazineArtists::ArtistIdFk
Magazine::ID---- is related to ----- JoinMagazineArtists::MagazineIdFk
I quickly made a few small additions.
Make sure you look at this carefully and that you understand what's going on.
Because you were making some mistakes.
If you are uncertain, you can always ask.
I'm trying to study it. Thanks for your efforts!
I think I'm starting to see how it's done... I noticed that the magazines that someone is mentioned in gets stored as 'magazineidfk' and the artists likewise in 'Artistidfk' ... somehow I thought there would be a long list of names in the cell for 'magazineidfk', but it gets mentioned only by idnumber...
I'm going to play with this tomorrow (first to the zoo with the kids :-) )... I might get back to you for some other help, but I think that this is the way to go indeed!
Thank you both!!!
I've been playing around with things, and I've managed to get this done: if you click on the listed artist in the magazine menu, you go to the artist page... vice versa, if you click on the magazinename on the artist table, you go to the magazine.
I've managed to do so, by making another button (Show). My goal was to make the information visible while clicking on the artist name, or on the magazine names, but as you have to put in those names at the same time, I couldn't get it working.
For those who are interested:
And as allways, I'm open for any feedback! Thank you all!
The GoToRelated record scriptstep is indeed the proper way to go to the related layout.
But I don't understand completely what you mean with this:
My goal was to make the information visible while clicking on the artist name, or on the magazine names, but as you have to put in those names at the same time, I couldn't get it working.
If you can explain this a little further?
Do you mean you would like to show the data of a certain magazine on the artist layout?
Or do you want the artist details on the magazine layout?
Or do you mean that you wanted the name to be the button that takes you to the other layout?
Because that is indeed not going to work if your field is a dropdown.
But your solution is nicely done. It works.
Now the next thing for you to figure out is how you can create and immediatly relate a new artist from the magazine layout.
And how you can create and imediatly relate a new magazine from the artist layout.
So you don't always first have to go to the other layout to enter a new artist or magazine.
You're right with 'I wanted the name to be the button that takes me to the other layout'. I figured so, that it wasn't going to work this way, so I took a look at your 'delete' option, and figured it out how to use it for a 'Show' button. Thank you! Learned something again!
I'm already thinking about the next step, because it wouldn't be logical to go to the lists one by one, to fill in the items/artists/magazines, and after that combine them. It should be possible to do that from the same page to start with.
Something I was wondering. You made a separte layout-page for each tabel. Is there any specific reason, other than you can have a list-version of everything and a 'fool proof' version to work on (layout one)?
I saw on youtube other people using it, but as you can see both the layouts the same way, using the first option to see the form (don't know how it's called in English, so I try to describe it: you've got the 3 versions of how you can see your layout - ... I'm talking about the first one)...
The three views are:
- Form view (for your fool proof data entry)
- List view
- Table view (looks like an excell spreadsheet)
I always create the table view for every table and then a form view for the user to do data entry.
But when you are using things like dropdowns, radio buttons or checkboxes sometimes you can't see the actual data that's in a field.
And that's why I use the table views, to see the actual data.
And sometimes you can use them to do some data manipulation.
I call them my developer layouts. They are not for a user.
They give you a good look at what's going on under the hood.
For the next step you will be needing some temporary fields and a script that
- Checks to see if that magazine / artist already exists
- if not creates a new record and fills in the data you entered in those temp fields
- Relates that newly created magazine / artist to your current artist / magazine.
And that's done by creating a record in your join table.
Mmm... I was thinking at first to create a button to go to the 'add artist' screen (or 'add magazine'), kinda the way the 'show' button works, but that's probably not the neatest way to go.
I have to dive into this! Thanks for the intell!