PLEASE HELP: Utterly Brain-Blocked on Join Tables!

[Guy_Smith]

I thought I had a semi-fair grasp on basic join tables, but....NOPE!  I've spent the last two days scouring my copy of FTS, looking at the starter solutions, digging through this forum, and watching YouTube videos on how to use Join Tables and I'm just not able to grasp the concept.

 

I have three TOs:  People ---< JoinGroup >--- Events 

 

- The JoinGroup Table has it's own primary Key, two foreign Keys (one each from the People and Events tables), some looked-up fields from the people and events tables,  and a few Group Table-specific fields.  

 

So far, so good.  Unfortunately, this is where my brain shuts down.  When I add a new record in the join table, I can't figure out how to select which person from the People TO and which Event from the Events TO go into the new record without resorting to manually looking at the main tables, writing down their primary Keys, and manually entering them into my new Join record.  Yuck!

 

Is there a way to have a list of the people and events show up on the join table layout so I can pick the name and event?  I have list layouts of both other tables but do not how to access them from the join table.  I have tried putting portals on the join table layout to show the people and events, but they are blank until I manually enter the foreign keys which makes just the one person and one event show up (which is a good thing for a specific join record, but not to initially find the person/event).

 

Should the foreign Key fields in the join table be set to (Auto Enter) look up the Primary Key field in the People and Events tables or just left empty?  I've tried both ways and it doesn't seem to do anything either way, but that is probably because I have some fundamental failure in my setup.

 

Any guidance would be much appreciated.

 

Guy

[bcooney]

Typically, on a parent form one would place a portal to the join table. The "other" foreign key might be assigned a value list.

 

Why are you "looking up" fields from the parent? Unless you need  to capture temporal data (prices on an invoice, for example), you do not need to add redundant fields to the join table.

[Guy_Smith]

Thanks, Mr. Cooney - my 'sharp as a marble' brain didn't quite say that correctly:  I actually have some related fields from the other two tables on my Join Table layout and do not use the Lookup section of the Auto-Enter screen.  Good catch.

[LaRetta]

Hi Guy,

You need to be on one side of the join or the other when you decide to enter a new join record so you always know one side and you only need to select the other side as Barbara explained usually selecting from pop-up. Two ways and I will give both examples from People side: (same would work from Events side).

1. On People layout with portal to Join with 'Allow creation' on, add new record by selecting Event in the last empty row. This inserts the PersonID into the join record automatically or 2. Script could go create the join record and return. Script would take the table's primary key with it similar to:

Set Variable [ $personID ; People::PersonID ]

Freeze Window

Go To Layout [ join table ]

New Record/Request

Set Field [ Join::PersonID ; $personID ]

... either stay there to select event and enter other data into the join or return and enter remaining data into portal so return...

Commit Record

Go To Layout [ people ]

[Guy_Smith]

Barbara and LaRetta:  Thank you both very much for your help!

 

Ms. Cooney:  First, please accept my sincere apology for making such a chauvinistic assumption on your name   .  You mentioned that "The "other" foreign key might be assigned a value list."... Is there a way to show that value list with event names instead of the Primary Key (a number)?

 

LaRetta:  Both methods sound good:  In solution #1 with a portal to the join table on the People layout, how do I select the event from the 3rd table?  Do I need an additional portal to the Event table to select it?

In solution #2, same basic question;  After I've set the PersonID field, how do I select the event?  Do I use the value list Barbara discussed?

 

Sorry for the follow-on questions.  Even though it can be frustrating at times, it's a lot of fun learning FM and with your willingness to donate both your time and brain cells to helping, I'm slowly but surely gaining ground  

[LaRetta]

Comment provided the perfect demo - Post #7 from this thread has a join demo which shows how Barbara is suggesting you use a value list for selections.  This shows the proper join structure .

 

http://fmforums.com/forum/topic/50942-portal-grouping-problem/#entry239210

 

After you unzip it, drag it to your 12 icon and it will convert it for you.

[Guy_Smith]

Thanks for the link, LaRetta - I had seen that one a few months ago, but was clueless as to how it worked until now (I thought Michael's really nicely designed layout was using tabs (all linked to the same parent TO) instead of buttons & scripts:  Yep, I'm THAT big of a Noob!.  I guess the simplest looking designs have a lot under the hood to make them so intuitive for the user).

 

I don't have all the bugs worked out yet, but I'm making good headway using your Set Variable/Set Field process.  Hopefully, I'll get some more time tonight to play with it some more.

 

Thanks again to the three of you.  Your kindness and willing assistance is truly remarkable!

 

Best Regards,

 

Guy

[Guy_Smith]

I finally had some time to digest everything and try out your suggestions and everything works BEAUTIFULLY:  i can now go to my new Events TO layout and select/display the people attending each event just as I'd hoped.

 

My new major mind malfunction is not being able to go to the next process which is to charge people for costs incurred while assigned to subgroups at the event:  What I thought was a many - to - many relationship now looks more like a parent - child - grandchild relationship.  Crud and Double Crud!

 

I can't thank you enough for all of the help and I'm slowly but surely learning the ropes!  I will probably start a new thread to get some input on what the proper relationship structure should be.

 

Best Regards,

 

Guy