9 Replies Latest reply on Aug 30, 2011 4:44 PM by mgores

    questions on many to many and portals

    mgores

      Title

      questions on many to many and portals

      Post

      I am trying to put together a solution for my sons scout troop.  I have a contact table for the scouts and an event table for different events.  Knowing that there are a bunch of scouts and a bunch of events, I figured I would need a join table to link the two since not all scouts participate in all events.

      I am having trouble setting up a layout where I can associate a scout to an event or vice versa.  I have been trying to set up a portal on the scout layout using the scout_event relationship showing fields from the event table, but an event will only show up when I go to the scout_event table and manually create a record and enter the scoutID and eventID.  I would like to be able to list the events in a second portal and be able to select from the list of events or go to the event layout and select which scouts from the list of scouts.

        • 1. Re: questions on many to many and portals
          philmodjunk

          So you have this relationship:

          Scouts---<Scout_Events>-----Events

          Scouts::ScoutID = Scout_Events::ScoutnID
          Events::EventID = Scout_Events::EventID

          And enable Allow Creation of records via this relationship for Scout_Events.

          You then put a portal to Scout_Events on the Scouts layout.

          You could add a portal to events via a different relationship that lists all events or even all events in a given date range, but you can also set up your Scout_Events::EventID field as a drop down list or pop up menu to do the same.

          To get a portal to act as an events picklist, you add another occurrence of Events such as:

          Scouts::anyfield x AllEvents::anyField

          Then you can set up the fields in a portal to AllEvents as a button such that clicking them performs a script to create a new record in the join table with the correct ID's.

          • 2. Re: questions on many to many and portals
            mgores

            OK thanks,  think my problem may have been that the eventID I had in the scout_event portal was from the events::eventID and not the scou_events::eventID so it wasn't allowing me to create the record.

            Is there a way to have the eventID be a drop down as you suggested, but show the related event name and insert the eventID?

            • 3. Re: questions on many to many and portals
              mgores

              What would be more likely is to create an Event and then select the scouts that participate.  I have a layout based on event with a portal using an "X" relationship to scouts.  What would be the easiest way to select scouts from that list and have it create the records in the scouts_events table.

              Would it be easier to have a selection field in scouts where I can check which scouts are participating, then have a script that does a find on that select field and creates the join table records for each, then clears the select field.

              OR

              set up a button for each name so that clicking it will create the record.

              • 4. Re: questions on many to many and portals
                philmodjunk

                Hmmm, I don't see my last post here...

                I suggested useing a value list with the EventID in field 1 and the event name in field 2. WIth unique event names, you can use a pop up menu for this with the first field hidden and the field can be used to select and display events by name even thought it stores the event ID.

                With your "AllEvents" portal, you can set the event fields in the portal up as a button to run this script:

                Set variable [$EventID ; value: AllEvents::EventID]
                Set Variable [$ScoutID ; Value: Scouts::ScoutID]
                Freeze Window
                Go To layout [Scouts_Events]
                New Record/Request
                Set Field [Scouts_Events::EventID ; $EventID]
                Set Field [Scouts_Events::ScoutID ; $ScoutID]
                Go To layout [original layout]

                You can use a similar method from an events layout to select from a portal of all scouts.

                • 5. Re: questions on many to many and portals
                  mgores

                  Got it. thanks.  Now is there a way to mark choices from the list that have already been selected or to filter them from the portal list? Otherwise there could be multiple duplicate records generated in the scout_event table if you were to lose track of which ones you had selected and kept selecting the same ones.

                  Am trying to look into the conditional formating or a calculation field, but can't figure out a way to check if there is a record in the scout_event table that matches both the scoutID and eventID in order to mark a portal row. 

                  Or possibly have a scout_event portal next to the list portal that would show "participated" (this part works) then have that record filtered out of the list portal.  So when a record is selected from the list portal it "moves" over to the participated portal?

                  • 6. Re: questions on many to many and portals
                    philmodjunk

                    You can set up a unique values validation rule that throws and error when the same combination of Scout and Event IDs are entered in the join table.

                    Define a text field in the join table with this auto-entered calculation: ScoutID & " " & EventID.

                    Specify Unique values as a validation rule for this field.

                    You can also set up dwindling value lists where each time you select an eventID (if selecting from the Scout layout) or ScoutID (If selecting from the event layout) it disappears from the value list. If you are using a portal to all events or all scouts, you can set up conditional formatting to show what events/scounts have been added or a portal filter to remove the selected items from the portal.

                    It will take a lot of typing to spell out all of those methods. Why not let me know which looks most attractive to you and I'll respond back with a description of that method?

                    • 7. Re: questions on many to many and portals
                      mgores

                      Right now I have the events layout with two portals, a portal to "All scouts" and a portal to "participated" (scouts that are selected from the first portal).

                      I set up the field in the All scouts portal to run the script mentioned earlier to create the record in scout_events, which makes that scout show up in the participant portal.  That works great.  I added the calculated text field and the validation criteria, it does keep giving error messages until you let it revert.

                      Have been pulling my hair out trying to figure out a way to filter the All scouts portal to remove them from the list once they are added as participants.

                      • 8. Re: questions on many to many and portals
                        philmodjunk

                        I've been working on a dwindling value lists/ many to many demo file. I brought it up and played around with an "all" portal to see what filter will work.

                        I came up with this expression:

                        Not ValueCount ( FilterValues ( List ( Scout_Events::ScoutID ) ; AllScouts::ScoutID ) )

                        Then update the script you've assigned to the button in the portal that assigns a scoutID to the join table to include this final scirpt step to force the portal to update:

                        Refresh Window [Flush cached join results]

                        • 9. Re: questions on many to many and portals
                          mgores

                          WOW!  that works slick.  I was figuring it would be good for taking roll at meetings or other events and working from an iPad, iPhone or touch with FMgo, so that clicking a name to move them from one portal to another would be a good intuitive way of doing it.  Would never thought of that expression on my own, but it's going in my bag of tricks now.

                          I did find a problem though, probably should have known better, but in the calculation field in the scout_event table I had the "do not replace" option checked, which caused the calculation field to only have the scoutID and the space (no eventID).  So that if a scout was already on one event, it would not let me add him to another event.  I cleared that option and all works fine now.