Many to Many relationship...script to allow selection of multiple records to be added to a portal

[pacertorganic]

Hello!

 

 

Just migrated from FM6 to 12 and am enjoying all the power and flexibility!

 

I have two tables (Materials and Restrictions) that are "joined" in a many to many relationship through a third table (MatRest_JOIN). The primary table contains information on organic materials, the second table is a listing of all the possible restrictions a material might have. That middle, joined table which allows the many to many relationship can be understood as...
1. A material can have one or many restrictions
2. A restriction could apply to one or many materials at one time

3. MatRest_JOIN table has an index (ID) of it's own records and then for each record has the fields MaterialID and the RestrictionID

At present, I can select one restriction at a time to be added to the joined MatRest_JOIN table via a portal. However what I'd like to give user's the ability to do is see a list of ALL the restrictions possible, choose via a checkbox beside each of the ones they want and then add all those restrictions in one step to the joined table. So back in my main layout for Materials that has the Restrictions portal, they'd see all the restrictions they chose/applied to that organic material.

I do have a couple ideas how to accomplish this, such as a loop in the script that looks at each record in the Restrictions table to see if the user checked it....then as it comes across those, it creates a new record in the joined table tying the ID's of the Materials and Restrictions table records, then it continues through the Restrictions list performing the same task until it hits the last record. 

That seems like the way to do it, however it also seems like a might be a big clunky...going step by step through one table (which at most would only ever have like 30 records/restrictions in there) and checking a flag, and creating a new record in the joined table, on and on.

Any thoughts are greatly appreciated...thanks so much!

Joshua

FM12_Script_MultipleRestrictions.pdf

[bcooney]

Hi Joshua,

How about capturing the IDs of the selected restrictions in a $$var. Then user clicks "Apply" and you loop thru the $$var creating join records off screen. btw, the "checkbox" would look to the $$var to determine if it should be checked. That is, you don't want to edit the Restrictions record at all (that isn't multi-user friendly).

 

hth,

Barbara

[LaRetta]

Hi Joshua, welcome to FMForums!

 

I would take a slightly different approach.  From Materials, you can view list of all Restrictions in portal and just check the ones you want (creating your join records) OR  from Restrictions list layout, just select the Material from the pop-up in the header and then make your Restriction selections.  Change to different Material and make more selections.

 

Attached are fp7 and fmp12 examples for those without 12 - it works same on both.  Also note a trigger on the checkbox which simply commits OnObjectModify so the join portal displays instantly.

Materials.zip

Edited November 27, 2012 by LaRetta

[bcooney]

Joshua,

afaik, you're not saving them any clicks. In fact, you're adding a click. I'd just go with side-by-side portals (select from on left) and a move arrow. I would also diminish the choices on the left, as they are added to the right, and if deleted on the right, add back to the left. I might also give them a Move All button.

-B

[Jauquin Jamile Holmes]

Hello guys,

 

I am kinda new to using filemaker, at our school district we are using iwp to store and access tutoring data for our students and staff. we currently have a verison of the database that has been working for us. I would like for the staff to be able to add multiple students to a caselist over the iwp.

 

so I was thinking the basic set up would be to make a student table and a mentor table. then link these tables with a MentorStudent table (one mentor to many students, visa versa) use the MentorStudent table on a layout and select one mentor and then move the students to the mentor list that would be associated with the mentor list any ideas on how to script this so that it would be compatible with IWP?