Many to Many Relationship - CREATE new records through a join table

[Joe Kaatz]

Hi all,

Thank you in advance for any help.

I'm creating a database for my school's business department.

I have a many to many relationship set up like this:

Students - (Internship) - Internship Sites

Student can have multiple internships, and internship sites can have multiple students.

When a current student starts a new internship it might be with a company not listed in the "Internship Sites" table.

Is it possible to make it so data entry in the "nternship" jointable subsequently creates a record in the "Internship Site" table ?

IE. John Doe has an internship with TARGET. I go to John Doe's record in the "Students" table layout, enter the internship data into the "Internship" portal. TARGET does not exist in the "Internship Site" table yet. Can the data entry into the portal create a new permanent record in the "Internship Site" table?

I cant seem to get this to work out.

Thanks,

Joe

[mr_vodka]

Why not just create a mechanism to quickly create a new internship? How could one sign up for an internship that doesn't exist yet right?

[LaRetta]

If you have several fields which must be completed in Internship Sites then it makes sense to go to a data-entry layout and enter a new Internship Site record where all fields are displayed for easy entry. But if you have only a few fields (or one many times) then you can handle it without script or trigger or leaving the layout at all although I have nothing against leaving the layout. And it uses your existing, already-established relationship.

You can create two records simultaneously in two different tables (both Internship and Internship Sites (please see attached) just by adding a new Internship Site record when on Students. To resolve through the join table (Internships), it will create both records automatically.

It's just another option. :^)

UPDATE: Oh! And welcome to FMForums, Joe!

UPDATE2: Also please note that I have validation unique set on the Internship Sites name to prohibit User from entering a duplicate.

Interns.zip

Edited May 22, 2012 by LaRetta

[isail]

I have pretty much the same problem and was hoping to be able to create a new field in the Internship database through the join portal. I guess that isn't possible :-(. In my case it is: 

 

Event -< GuestList >- Guests

 

And I was hoping to add previously unknown guests directly from the event (and then go and fill out more information about them later. Do I have to populate the Guest database before adding the guest to the GuestList?

[LaRetta]

Welcome to FMForums!  :^)

 

Here is a link where I show further (described in post #3 above) how TwoForOne works.  No script needed at all and using your standard entity relationships.

 

http://fmforums.com/forum/topic/86855-samples-twoforonefp7/

 

The example is Projects -< Crews >- Workers

 

So Projects = Event, Crews = GuestList and Workers = Guests

 

It shows in fp7 format but just drag it to your fmp12 icon and it will convert.  I mean to adjust the demo to make it even easier to understand but I just haven't gotten to it. In fact, this is the example I want to change it to.  This also works well for creating new values in value list tables.

Edited December 5, 2012 by LaRetta