4 Replies Latest reply on Jul 3, 2012 8:21 AM by JosefBalles

    Select a record to relate from a portal?

    JosefBalles

      Title

      Select a record to relate from a portal?

      Post

      Is it possible to select a record from a portal to relate to the record displayed in the table you're viewing? Preferably using a drop-down menu?

      Here's the scenario: I have a table populated with project profile records and a table populated with university personnel. I have a portal to the university personnel table from the project profile table. One or more of the university personnel can be related to each project and this can change through the project's duration. I want to be able to select a record from the portal table to relate to the project being viewed. I do not want to edit or create records in the portal table. Is this even possible?

        • 1. Re: Select a record to relate from a portal?
          philmodjunk

          It's possible, but first you have to have basic tables/relationships in place. If I am understanding your post correctly, you have a "many to many" relationship here. Presumably, a personnel record could be linked to more than one project and you've stated in your post that a project can have more than one personnel assigned to it at the same time.

          This requires a Join table and these relationships:

          Personnel----<Personnel_Project>------Projects

          Personnel::PersonnelID = Personnel_Project::PersonnelID
          Projects::PRojectID = Personnel_Project::ProjectID

          With this set up, you can put a portal to Personnel_Project on your Projects layout and use a drop down list in the portal to select values for the Personnel_Project::PersonnelID field. This can be a drop down list of ID's from Personel with a second field supplying the full name of the people listed in this table. This works for relatively small lists of personnel. With longer lists, it's possible to use more sophisticated designs that make choosing from a long list of names easier to manage.

          To remove a person from a particular project, you'd delete the portal row in this portal--which deletes the record in Personnel_Project. In all of these operations, Personnel, is left unchanged.

          Here's a demo file that illustrates this basic setup as well as two more sophisticated approaches designed to prevent users from making the same assignment twice for the same pair of records:

          https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

          • 2. Re: Select a record to relate from a portal?
            JosefBalles

            In the personnel file, I have first and last names as separate fields, but repeat last names. I can only have two fields in the drop-down menu and one of them has to be the personnel ID. What is a feasible solution to this problem?

            • 3. Re: Select a record to relate from a portal?
              philmodjunk

              Option1: Define a text field with an auto-enter calculation such as: LastName & ", " & FirstName. Use it as your second value in the value list. A calculation field can also be used for this, but using a text field allows you to specify validation rules such as requiring unique first/last name combinations--which can be useful when serving as a data source for a value list.

              Option2: Don't use a value list. The Checkboxes layout in the demo file is an example of how to select records without choosing from a value list.

              • 4. Re: Select a record to relate from a portal?
                JosefBalles

                Awesome, you da man, phil.