Playing with Many-to-Many relationships & Portals

[jscooper]

This is interesting:

I created a many-t0-many relationship using three tables, TableA, JoinTable and TableB. Tables A & B each have ID a serial #. JoinTable has TableA_ID, TableB_ID. I have relationships from TableA to JoinTable and from TableB to JoinTable. All my relationships allow creation of records via the relationship on both sides. (so a portal in TableA can show/create JoinTable records, as can a portal in tableB)

Now, in a layout based on TableA, I create a portal and it gives me the option of showing records from TableB, even though there is no direct connection between the two. "Interesting" I thought whilst munching on my Hot Pocket. I created the portal and it actually let me make new rows! Here's the catch: for the first row I entered, created a record in both JoinTable and TableB. But when I created a second row, it just created another record in TableB, but gave it the SAME ID as the first record. Additional strangeness: if I manually created a record in TableB, it sets the ID to 3, as if the second one had a ID of 2 (and not 1, which is what it did have).

If this is a glitch, I'm wondering if it's in that you shouldn't be able to add portals based on tables not directly related to the base table of the layout? Or, if it's that the IDs are not getting set/join table records not getting created correctly.

If it's by design, the I guess the only way around it is to use scripts/buttons to manually create the needed join table rows.

Jeff

[bcooney]

Hmm..Of course, the first thing I did was a little many-to-many test for myself. I set creation of records only in the Join table. I created a portal in TableA and in TableB to the Join table. Everything worked fine. What was a welcome change, I didn't need to set calculation fields in the join table for the fields that I wished to display in the portal (those beyond the ids). Therefore, my join table remains "pure" if you will, containing only the TableA_ID and TableB_ID fields. Really nice.

[jscooper]

Must be a problem with the Trial Version then ... I got it to work fine (just as you describe) using Developer at a client's. But using the trial version (non-developer), the IDs duplicated in for each portal row I added.

[Thom Brooks]

Hello, this was also my first 'test run' of FMP7 -

Tables: User, Department, User_Department...

where User.ID = User_Department.user_ID,

and Department.ID = User_Department.department_ID.

What I'd like to do is create a 'multi select' (pardon my HTML Form terms) or a checkbox set, in my User Layout, containing all of the Department names. (i.e. a user can belong to one to many departments.)

To add someone to a department, you'd just highlight that line or click that checkbox -- and I'd like the related record (combining userID and departmentID) to be auto created (or deleted, if you unselect) in the User_Department table. Highlight another line (or click another checkbox) to create another record in User_Department, etc.

So far what I've tried doing is inserting a field in my User table, the Department.name, and specified that it's a checkbox set, displaying values from value list 'department', which is composed of values from field Department::name.... So, in my layout, I now have the correct department names, but clicking the checkboxes does nothing (yet).

Am I close to getting it right? Do I need to create a script to do what I'm trying to do, or is there a way to do this? I've attached the file as a .sit.

Thanks,

Thom Brooks

Version: v7.x

Platform: Mac OS X Panther

[jjb]

Hi Thom,

I'm pretty new to FM and am trying to do pretty much the same thing as you described here.

Were you able to get this working? Also, do you display the department/user names or ID's?

Thanks for any help.

/jeremy

[Ender]

We haven't seen Thom around here lately, but I don't think his idea of portal records automatically being created or deleted based on selections in a checkbox would work without a script.

I'd say a simple solution would be to skip the User_Department join file and simply match Department::Department Name to the User::Department (checkbox) field.

If there are other properties of a Department_User that must be tracked, then use a Department popup list in a portal of Department_User records instead of a User::Department checkbox field.

[RalphL]

May I suggest that you take a look at Separation Model many to many relationship. It is in the Separation Model Fourm. Look at the last version. http://www.fmforums.com/threads/showflat.php?Cat=0&Number=117552&page=&fpart=1&vc=1

Look for the post date 16/10/2004.

The methods used can be used without using the Separation Model.

[Ender]

Ralph, It's not clear how your sample applies to this question.

[RalphL]

The question was about creating related records. My sample shows a way to create the records in the join file without creating duplicates. Generally one of the tables is fairly stable. I make the join record in the more dynamic one. But the process can work in either direction.

Instead of a checkbox I used a popup. The value list keeps changing as value are used.