Jump to content
Sign in to follow this  
sean o mac

one to many / many to many question

Recommended Posts

OK, just need some clarification here if possible on the variations of what I can and need to do with some relationships:

Problem #1...

My 'main' table (the one I primarily view everything from) is called Events. In one instance I have a relationship between it and another table called Guests. The relationship is pk_EventID (Events) to fk_EventID (Guests). Using a portal I can thus create a guest list for a particular event, then print it, sort by last name etc. That works great assuming each list of names is unique to one event.

Now, what just came to me is this: what if I already have a record of someone's name in the Guests table and wanted to reuse it for a second Event (or a 3rd, 4th etc.)? Correct me if I am wrong, but if I try to choose an existing record of a guest for the new Event, then that name would disappear from the other Event's list wouldn't it? How would I solve this? More importantly, is this solved differently in FM8 vs. older versions of FM (3-6)?

thanks a ton...

Share this post


Link to post
Share on other sites

You should have a third table that is a join table between the events and contacts tables.

Share this post


Link to post
Share on other sites

OK, I know I have heard lots about these Join Tables but have yet to grasp exactly how to implement them. Sorry for my ignorance:

- What goes in the Join Table in this instance? I know enough its primarily ID fields etc. and you don't need to ever see the Join Table in a layout, but some simple specifics would be VERY helpful to me.

- How is the relationship built once the Join Table has what fields it needs?

- Is the way a Join Table functions in FM8 exactly the same as in earlier versions (other than being Tables vs. Files of course)?

- Any ways that FM8 make Join Tables work better/easier?

--- Finally and most importantly, lets say I have half a dozen Tables that need relationships to the 'main' Table that are nearly identical. Is it possible (and smart design) to use a single Join Table for all of these instances so everything is in one place?

Share this post


Link to post
Share on other sites

The join table should have fields for any information that is unique to the join - i.e. not constant to one of the joined records.

You can find a basic demo of a join table here.

Share this post


Link to post
Share on other sites

After looking into it online I found the following link and its the first time I have found a way to understand how join tables work:

http://filemakeradvisor.com/doc/14637

I get most of what this article is going on about and can say I am starting to grasp how Join Tables function. With some effort I will get along even further and solve a bunch of issues myself. More importantly, this seems to address particular differences (and thus advantages) to how FM8 works with Join Tables. So far so good, right?

Unfortunately, I did get completely stuck with the wording of the article in one section and maybe someone could help me. In the section of the article "Create a join with a parameterized script", there is a paragraph (a single sentence actually) about 7 paragraphs down that I am convinced should be expanded from one sentence to be understood properly. Considering how well explained everything up to that point is, its a disappointment I can't get my head around how this works.

Specifically, the paragraph (well, sentence actually) in full is as follows:

"Finally, you have to make the gStudentID/gClassID field a lookup auto-enter value. Using a relationship, look up the name stored in gClassName or gStudentName and set the value of gStudentID or gClassID to the appropriate ID value."

If anyone can decipher this sentence for me and explain a little more I would be greatly appreciative. I tried all kind of permutations of what I THOUGHT it was trying to suggest and got no results at all.

Thank you so much for indulging me.

Share this post


Link to post
Share on other sites

OK, this I get - its what I am now doing in modifying my current database and I understand the theory correctly so far.

What I can't seem to be able to do is add an AFFILIATION via a simlar value list that you have in the portal. The portals are not giving me that option for some reason even though I do have 'allow creation of records' clicked in the join table both directions. Any idea why?

Share this post


Link to post
Share on other sites

Check that the "Allow creation" is indeed for the Join table, and not for the main table. Also check that the ID in the portal belongs to the Join table, and that it is the ID choice for the other table's foreign key (the name will be from the other main table; and should not be enterable).

Share this post


Link to post
Share on other sites

I will check all these things again, but as far as I know I have done exactly as Comment's example. I just can't get a new portal row to appear at all when I want to add an affiliation using a value list.

On that note, assuming I find I am missing a step here and I can fix this to add an existing affilitaion, does the simplicity of adding a record in a portal row get lost? Would I now have to use a script to make it look/funtion the same when adding a record as when there was no Join Table between the two files? I know there are times when it might be preferable to use a different type of interface, but considering how many records I need to add in quick succession in this case, the usefulness of hitting tab, tab, tab then going to the next row is really the most useful way of working.

thanks

Share this post


Link to post
Share on other sites

It's hard to troubleshoot blind. It does sound, though, as if the portal's relationship is not allowed to create new records. Double check the identity of the portal and of the fields in the portals.

The demo is very basic, and its purpose is to show the principle of using a join table in a many-to-many relationship. The user interface is intentionally the simplest it can be. As is, it is easy to add join records that join the current person to an EXISTING organization, or vice versa. Adding a NEW organization during this process is not provided for. But there are endless possibilities of modifying the UI to accommodate any preferred workflow.

Share this post


Link to post
Share on other sites

Sorry its taken a while to get back onto this, but I just took another good look and I still don't know what I am doing wrong. Here is the current setup and what the behaviour is, all of which is identical to your example as far as I can tell:

event table related to join table by pk_eventID::fk_eventID fields (allow creation of records ON in join table)

press table related to join table by pk_pressID::fk_pressID fields (allow creation of records ON in join table)

I have set up a value list in the event table exactly like your example, namely displaying pk_pressID in addition to press_name via the relationship called Press Contact, which is on the other side of the Join table (just like your value list for Contact).

As in your example, the field using the value list above is set to display the fk_pressID from the Join table. I get the value list to showing up as expected, yet when I choose a record from the list it displays BOTH pressID and Contact Name, which isn't like the behaviour of your example. In your example, only the record ID displays, clearly placing that value into the fk_contactID field in a new record in the Join table, thus allowing the relationship to connect across to the record with a similar pk_pressID.

I just don't get what I am doing wrong... did I miss something?

Share this post


Link to post
Share on other sites

It's a bit hard to follow - why not attach a file?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.