1 Reply Latest reply on Jan 27, 2011 2:30 PM by philmodjunk

    Updating many-to-many relationship through portals



      Updating many-to-many relationship through portals


      Hi, I'm new to FileMaker and having trouble with my layouts.

      I have two tables, 'Person' and 'Role', that share a many-to-many relationship.  Each record in 'Person' has a 'Name' and a 'PersonID'.  Each record in 'Role' has a 'Title' and a 'RoleID'.

      I created an intermediate table, 'PeoplesRoles'.  Each record in 'PeoplesRoles' has two fields, 'PersonID' and 'RoleID', each used as a relationship to their respective table.  These relationships are configured to allow creation of records in 'PeoplesRoles'.

      In the layout for 'Person' I created a portal to 'PersonsRoles' which shows 'PersonsRoles::RoleID' and 'Role::Title'.

      I can create a new row in the portal by entering a 'PersonsRoles::RoleID' into a blank row, but that requires the user to know an arbitrary value.

      What I can't figure out is how to allow a user to select a value from a value-list containing values from 'Role::Title' to add a row to the portal.

        • 1. Re: Updating many-to-many relationship through portals

          You can set up your value list to have two columns of data. Column 1 is your ID field and column 2 is the name field you need to know which item to choose to assign the correct role.

          Open Manage | Value Lists

          Create a new value list and select the "Use Values from Field option". You'll see two side by side boxes for specifying values in these two columns. In column 1, you can select RoleID from the Role table. In column 2, you can select Title from this same table to show the name of the role.

          That's all you have to do. You can hide the RoleID column if you wish, but don't do this unless you know the value in Title is unique for every Role ID (there's a validation rule you can set to make sure this happens). Otherwise, the duplicates will disappear from your value list and you won't be able to select subsequent duplicated values.